将'serial'添加到Postgres中的现有列 [英] Adding 'serial' to existing column in Postgres

问题描述

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

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.

如何更改此表，以便从现在开始对该表进行插入将导致该字段的行为就像是使用"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?

推荐答案

查看以下命令(尤其是注释的块).

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;

这篇关于将'serial'添加到Postgres中的现有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！