猿问

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

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

如何更改此表,以便从现在开始对该表进行插入将导致此字段的行为就像它是使用“ serial”作为类型创建的一样?


牛魔王的故事
浏览 749回答 3
微课
3回答

莫回无

查看以下命令(尤其是注释的块)。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 barCREATE 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 laterSELECT MAX(a) FROM foo;SELECT setval('foo_a_seq', 5);  -- replace 5 by SELECT MAX resultINSERT INTO foo (b) VALUES('teste');INSERT INTO bar (b) VALUES('teste');SELECT * FROM foo;SELECT * FROM bar;

千巷猫影

您也可以使用START WITH从特定点开始序列,尽管setval可以完成与Euler的回答相同的事情,例如,SELECT MAX(a) + 1 FROM foo;CREATE SEQUENCE foo_a_seq START WITH 12345; -- replace 12345 with max aboveALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');

婷婷同学_

非交互式解决方案只需添加其他两个答案,对于那些需要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会更合适:--                                             vvvSELECT 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 constraintSELECT make_into_serial('foo', 'a');INSERT INTO foo (data) VALUES ('asdf');-- OK: 1 row(s) affected
随时随地看视频慕课网APP
我要回答