如何在PostgreSQL中向现有表中添加自动递增主键?
我有一个包含现有数据的表。是否有添加主键而不删除和重新创建表格的方法?
I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?
)
(Updated - Thanks to the people who commented)
假设您有一个名为 test1
,要向其中添加自动递增的主键 id
(代理)列。以下命令在PostgreSQL的最新版本中应该是足够的:
Suppose you have a table named test1
, to which you want to add an auto-incrementing, primary-key id
(surrogate) column. The following command should be sufficient in recent versions of PostgreSQL:
ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;
PostgreSQL的旧版本
版本的PostgreSQL(8.x之前?)你不得不做所有的肮脏的工作。下面的命令序列应该可以做到:
Older Versions of PostgreSQL
In old versions of PostgreSQL (prior to 8.x?) you had to do all the dirty work. The following sequence of commands should do the trick:
CREATE SEQUENCE test_id_seq;
ALTER TABLE test1 ADD COLUMN id INTEGER;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
UPDATE test1 SET id = nextval('test_id_seq');
同样,在最近的Postgres版本中,这大致相当于上面的单个命令。
Again, in recent versions of Postgres this is roughly equivalent to the single command above.