将'serial'添加到Postgres中的现有列

Question

我的Postgres 9.0数据库中有一个小表（约30行），带有一个整数ID字段（主键），该字段当前包含从1开始的唯一连续整数，但不是使用'serial'关键字创建的。如何更改此表，以便从现在开始对该表进行插入将导致此字段的行为就像它是使用“ serial”作为类型创建的一样？

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;

Answer 2

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

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

Answer 3

非交互式解决方案

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

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

简而言之，您不能执行以下操作：

CREATE SEQUENCE foo_a_seq

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

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

注：作为一个经验法则，适用于所有非CRUD（即：比其他任何东西INSERT，SELECT，UPDATE，DELETE在报表）pgSQL的 AFAIK。

但是，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

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

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

—从文档中的此项开始

在不相关的注释上，您还可以Sequence直接通过来指定拥有的列CREATE，而不必稍后进行更改：

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

另外，如果您打算对多个列执行此操作，则可以选择使用real 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