将“串行"添加到 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.

如何更改此表，以便从现在开始插入此表将导致此字段的行为就像它是使用串行"作为类型创建的一样?

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;

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