在 Postgres 的现有列中添加“串行”

查看以下命令（尤其是注释块）。

DROP TABLE foo;
DROP TABLE bar;

CREATE TABLE foo (a int, b text);
CREATE TABLE bar (a serial, b text);

INSERT INTO foo (a, b) SELECT i, 'foo ' || i::text FROM generate_series(1, 5) i;
INSERT INTO bar (b) SELECT 'bar ' || i::text FROM generate_series(1, 5) i;

-- blocks of commands to turn foo into bar
CREATE SEQUENCE foo_a_seq;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
ALTER TABLE foo ALTER COLUMN a SET NOT NULL;
ALTER SEQUENCE foo_a_seq OWNED BY foo.a;    -- 8.2 or later

SELECT MAX(a) FROM foo;
SELECT setval('foo_a_seq', 5);  -- replace 5 by SELECT MAX result

INSERT INTO foo (b) VALUES('teste');
INSERT INTO bar (b) VALUES('teste');

SELECT * FROM foo;
SELECT * FROM bar;

---

您也可以使用 START WITH 从特定点开始序列，尽管 setval 完成相同的事情，如欧拉的答案，例如，

SELECT MAX(a) + 1 FROM foo;
CREATE SEQUENCE foo_a_seq START WITH 12345; -- replace 12345 with max above
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');

---

TL;博士

这是一个版本，您不需要人工读取值并自己输入。

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq'); 

另一种选择是使用在此答案末尾共享的可重复使用的 Function。

非交互式解决方案

只需添加其他两个答案，对于我们这些需要通过非交互式脚本创建这些Sequence，同时修补实时数据库的人。

也就是说，当您不想手动 SELECT 值并自己将其键入到后续的 CREATE 语句中时。

简而言之，你不能这样做：

CREATE SEQUENCE foo_a_seq
    START WITH ( SELECT max(a) + 1 FROM foo );

... 因为 CREATE SEQUENCE 中的 START [WITH] 子句需要一个值，而不是子查询。

注意：根据经验，这适用于 pgSQL AFAIK 中的所有非 CRUD（即：除 INSERT、SELECT、UPDATE、DELETE 以外的任何内容）语句。

但是，setval() 可以！因此，以下内容绝对没问题：

SELECT setval('foo_a_seq', max(a)) FROM foo;

如果没有数据并且您不（想）知道它，请使用 coalesce() 设置默认值：

SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo;
--                         ^      ^         ^
--                       defaults to:       0

但是，将当前序列值设置为 0 是很笨拙的，如果不是非法的话。
使用 setval 的三参数形式会更合适：

--                                             vvv
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
--                                                  ^   ^
--                                                is_called

将 setval 的可选第三个参数设置为 false 将阻止下一个 nextval 在返回值之前推进序列，因此：

下一个 nextval 将准确返回指定的值，并且序列推进从下一个 nextval 开始。

— 来自 this entry in the documentation

在不相关的注释中，您还可以直接使用 CREATE 指定拥有 Sequence 的列，以后不必更改它：

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;

总之：

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0) + 1, false) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq'); 

使用函数

或者，如果您计划对多个列执行此操作，您可以选择使用实际的 Function。

CREATE OR REPLACE FUNCTION make_into_serial(table_name TEXT, column_name TEXT) RETURNS INTEGER AS $$
DECLARE
    start_with INTEGER;
    sequence_name TEXT;
BEGIN
    sequence_name := table_name || '_' || column_name || '_seq';
    EXECUTE 'SELECT coalesce(max(' || column_name || '), 0) + 1 FROM ' || table_name
            INTO start_with;
    EXECUTE 'CREATE SEQUENCE ' || sequence_name ||
            ' START WITH ' || start_with ||
            ' OWNED BY ' || table_name || '.' || column_name;
    EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name ||
            ' SET DEFAULT nextVal(''' || sequence_name || ''')';
    RETURN start_with;
END;
$$ LANGUAGE plpgsql VOLATILE;

像这样使用它：

INSERT INTO foo (data) VALUES ('asdf');
-- ERROR: null value in column "a" violates not-null constraint

SELECT make_into_serial('foo', 'a');
INSERT INTO foo (data) VALUES ('asdf');
-- OK: 1 row(s) affected

---

在 PostgreSQL 12 中为我工作。

它将我现有的 int 列转换为串行列。

CREATE SEQUENCE table_name_id_seq;
ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('table_name_id_seq');
ALTER TABLE table_name ALTER COLUMN id SET NOT NULL;
ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id;  
SELECT setval('table_name_id_seq', (SELECT max(id) FROM table_name));

---

如果您使用 DBMS，您可以将列上的默认值设置为序列。

https://i.stack.imgur.com/1eEDP.png

它可能在高级选项下。

https://i.stack.imgur.com/P9nKc.png

---