postgresql 将“串行”添加到 Postgres 中的现有列

Adding 'serial' to existing column in Postgres

提问by nicolaskruchten

I have a small table (~30 rows) in my Postgres 9.0 database with an integer ID field (the primary key) which currently contains unique sequential integers starting at 1, but which was not created using the 'serial' keyword.

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

How can I alter this table such that from now on inserts to this table will cause this field to behave as if it had been created with 'serial' as a type?

我怎样才能改变这个表，从现在开始插入到这个表会导致这个字段的行为就像它是用“串行”作为类型创建的一样？

回答by Euler Taveira

Look at the following commands (especially the commented block).

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

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;

回答by John Powell

You can also use START WITHto start a sequence from a particular point, although setval accomplishes the same thing, as in Euler's answer, eg,

您也可以使用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');

回答by ccjmne

A non-interactive solution

非交互式解决方案

Just adding to the other two answers, for those of us who need to have these Sequences created by a non-interactive script, while patching a live-ish DB for instance.

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

That is, when you don't wanna SELECTthe value manually and type it yourself into a subsequent CREATEstatement.

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

In short, you can notdo:

总之，你不能这样做：

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

... since the START [WITH]clause in CREATE SEQUENCEexpects a value, not a subquery.

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

Note: As a rule of thumb, that applies to all non-CRUD (i.e.: anything other than INSERT, SELECT, UPDATE, DELETE) statements in pgSQLAFAIK.

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

However, setval()does! Thus, the following is absolutely fine:

然而，setval()确实！因此，以下绝对没问题：

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

If there's no data and you don't (want to) know about it, use coalesce()to set the default value:

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

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

However, having the current sequence value set to 0is clumsy, if not illegal.
Using the three-parameter form of setvalwould be more appropriate:

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

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

Setting the optional third parameter of setvalto falsewill prevent the next nextvalfrom advancing the sequence before returning a value, and thus:

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

the next nextvalwill return exactly the specified value, and sequence advancement commences with the following nextval.

nextnextval将准确返回指定的值，序列推进从以下开始nextval。

— from this entry in the documentation

— 来自文档中的这个条目

On an unrelated note, you also can specify the column owning the Sequencedirectly with CREATE, you don't have to alter it later:

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

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;

In summary:

总之：

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

---

---

Using a Function

用一个 Function

Alternatively, if you're planning on doing this for multiple columns, you could opt for using an actual 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;

Use it like so:

像这样使用它：

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