在Postgres中向现有列添加“serial”

Question

我在我的Postgres 9.0数据库中有一个小表（约30行），其中包含一个整数ID字段（主键），该字段目前包含从1开始的唯一连续整数，但未使用“serial”关键字创建。

我该如何更改此表，以使得对该表的插入操作会使这个字段的行为就像它已经被创建为“serial”类型一样？

- nicolaskruchten

---

对于现代的Postgres版本（>= 10），请参见此问题：https://dev59.com/iHA85IYBdhLWcg3wF_i0 - user330315

Answer 1

请看下面的命令（特别是被注释掉的块）。

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;

- Euler Taveira

---

既然你在原帖中提到了主键，你可能还想使用 ALTER TABLE foo ADD PRIMARY KEY (a) 命令。 - Skippy le Grand Gourou

SERIAL 是语法糖，不存储在数据库元数据中，因此上述代码完全等效。 - Dima Korobskiy

如果目标表可能是由不同的用户创建的，您需要首先执行 ALTER TABLE foo OWNER TO current_user;。 - Dima Korobskiy

你是否应该将MAX(a)+1设置为 setval 的值呢？ SELECT MAX(a)+1 FROM foo; SELECT setval('foo_a_seq', 6); - SunnyPro

Answer 2

你还可以使用 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');

- John Powell

Answer 3

简而言之

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

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]子句期望一个value而不是一个子查询，所以需要注意：这个规则适用于所有非CRUD（即除了INSERT，SELECT，UPDATE，DELETE之外的任何语句）在pgSQL中。

注：据我所知，这条经验法则适用于所有非CRUD（即除了INSERT，SELECT，UPDATE，DELETE之外的任何语句）在pgSQL中。

然而，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开始。

—来自文档中的此条目

另外提一下，您还可以直接使用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

或者，如果您计划对多列执行此操作，可以选择使用实际的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

- ccjmne

---

很棒的答案，但请记住 coalesce(max(a), 0)) 大多数时候不起作用，因为ID通常从1开始。更正确的方法应该是 coalesce(max(a), 1))。 - Amiko

感谢@Amiko的评论！setval函数实际上只为序列设置当前“最新使用值”。下一个可用值（实际使用的第一个值）将是更多！在空列上使用setval（...，coalesce（max（a），1））将使其设置为以2（下一个可用值）“开始”，如文档所示。 - ccjmne

@Amiko，你说我的代码有问题是对的，不过：即使在实际数据集中没有反映出来，currval也不应该为0。使用setval的三个参数形式会更合适：setval(..., coalesce(max(a), 0) + 1, false)。回答已相应更新！ - ccjmne

同意，我完全忽略了那个。 感谢你的答案，节省了我的时间。 - Amiko

Answer 4

PostgreSQL 10及更高版本

不要使用 serial。感谢上述评论。

ALTER TABLE mysmalltable
ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY;

- M Imam Pratama

Answer 5

在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));

- M. Hamza Rajput

---

这会导致无休止的执行。 - Qohelet

Answer 6

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

它可能在高级选项下。

- Chubi Best