如何在 PostgreSQL 中向现有表添加自动递增主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2944499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
提问by xRobot
I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?
我有一个包含现有数据的表。有没有办法在不删除和重新创建表的情况下添加主键？
回答by leonbloy
(Updated - Thanks to the people who commented)
（更新 - 感谢评论的人）
Modern Versions of PostgreSQL
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:
假设您有一个名为 的表test1，您想向其中添加一个自动递增的主键id（代理）列。在最新版本的 PostgreSQL 中，以下命令应该足够了：
ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;
Older Versions of PostgreSQL
较旧版本的 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:
在旧版本的 PostgreSQL（8.x 之前？）中，您必须完成所有繁重的工作。以下命令序列应该可以解决问题：
ALTER TABLE test1 ADD COLUMN id INTEGER;
CREATE SEQUENCE test_id_seq OWNED BY test1.id;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
UPDATE test1 SET id = nextval('test_id_seq');
Again, in recent versions of Postgres this is roughly equivalent to the single command above.
同样，在最新版本的 Postgres 中，这大致相当于上面的单个命令。
回答by Synesso
ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;
This is all you need to:
这是您需要做的一切：
- Add the
idcolumn - Populate it with a sequence from 1 to count(*).
- Set it as primary key / not null.
- 添加
id列 - 用从 1 到 count(*) 的序列填充它。
- 将其设置为主键/非空。
Credit is given to @resnyanskiy who gave this answer in a comment.
感谢@resnyanskiy 在评论中给出了这个答案。
回答by jhoanna
To use an identity column in v10,
要在 v10 中使用标识列，
ALTER TABLE test
ADD COLUMN id { int | bigint | smallint}
GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY PRIMARY KEY;
For an explanation of identity columns, see https://blog.2ndquadrant.com/postgresql-10-identity-columns/.
有关标识列的说明，请参阅https://blog.2ndquadrant.com/postgresql-10-identity-columns/。
For the difference between GENERATED BY DEFAULT and GENERATED ALWAYS, see https://www.cybertec-postgresql.com/en/sequences-gains-and-pitfalls/.
有关 GENERATED BY DEFAULT 和 GENERATED ALWAYS 之间的区别，请参阅https://www.cybertec-postgresql.com/en/sequences-gains-and-pitfalls/。
For altering the sequence, see https://popsql.io/learn-sql/postgresql/how-to-alter-sequence-in-postgresql/.
要更改序列，请参阅https://popsql.io/learn-sql/postgresql/how-to-alter-sequence-in-postgresql/。
回答by Dean Sha
I landed here because I was looking for something like that too. In my case, I was copying the data from a set of staging tables with many columns into one table while also assigning row ids to the target table. Here is a variant of the above approaches that I used. I added the serial column at the end of my target table. That way I don't have to have a placeholder for it in the Insert statement. Then a simple select * into the target table auto populated this column. Here are the two SQL statements that I used on PostgreSQL 9.6.4.
我来到这里是因为我也在寻找类似的东西。就我而言，我将数据从一组具有许多列的临时表复制到一个表中，同时还将行 ID 分配给目标表。这是我使用的上述方法的一个变体。我在目标表的末尾添加了串行列。这样我就不必在 Insert 语句中为它设置占位符。然后一个简单的 select * 进入目标表自动填充此列。这是我在 PostgreSQL 9.6.4 上使用的两个 SQL 语句。
ALTER TABLE target ADD COLUMN some_column SERIAL;
INSERT INTO target SELECT * from source;
