我在我的Postgres 9.0数据库中有一个小表(约30行),其中包含一个整数ID字段(主键),该字段目前包含从1开始的唯一连续整数,但未使用“serial”关键字创建。
我该如何更改此表,以使得对该表的插入操作会使这个字段的行为就像它已经被创建为“serial”类型一样?
我在我的Postgres 9.0数据库中有一个小表(约30行),其中包含一个整数ID字段(主键),该字段目前包含从1开始的唯一连续整数,但未使用“serial”关键字创建。
我该如何更改此表,以使得对该表的插入操作会使这个字段的行为就像它已经被创建为“serial”类型一样?
请看下面的命令(特别是被注释掉的块)。
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;
ALTER TABLE foo ADD PRIMARY KEY (a)
命令。 - Skippy le Grand GourouALTER TABLE foo OWNER TO current_user;
。 - Dima KorobskiyMAX(a)+1
设置为 setval
的值呢?
SELECT MAX(a)+1 FROM foo; SELECT setval('foo_a_seq', 6);
- SunnyProSTART 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');
这是一个版本,您不需要人工读取值并自己输入。
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
coalesce(max(a), 0))
大多数时候不起作用,因为ID通常从1开始。更正确的方法应该是 coalesce(max(a), 1))
。 - Amikosetval
函数实际上只为序列设置当前“最新使用值”。下一个可用值(实际使用的第一个值)将是更多!在空列上使用setval(...,coalesce(max(a),1))
将使其设置为以2
(下一个可用值)“开始”,如文档所示。 - ccjmnecurrval
也不应该为0
。使用setval
的三个参数形式会更合适:setval(..., coalesce(max(a), 0) + 1, false)
。回答已相应更新! - ccjmne不要使用 serial
。感谢上述评论。
ALTER TABLE mysmalltable
ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY;
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));