如何在现有的 PostgreSQL 表中添加自增主键？

Question

我有一个包含现有数据的表格。是否有一种方法可以在不删除和重新创建表格的情况下添加主键？

- xRobot

Answer 1

(更新 - 感谢发表评论的人)

现代版 PostgreSQL

假设您有一个名为test1的表，您想要添加一个自增的主键id（替代）列。在较新版本的PostgreSQL中，以下命令应该足够：

   ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

较旧版本的PostgreSQL

在旧版本的PostgreSQL（8.x之前？）中，您必须自己处理所有脏活累活。以下命令序列应该能解决问题：

  ALTER TABLE test1 ADD COLUMN id INTEGER;
  CREATE SEQUENCE test_id_seq OWNED BY test1.id;
  ALTER TABLE test1 ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
  UPDATE test1 SET id = nextval('test_id_seq');

再次强调，对于最近版本的Postgres来说，这与上面的单个命令大致等效。

- leonbloy

---

我正在使用ORACLE，因此分享它可能对ORACLE的人有用。在ORACLE中：ALTER TABLE TEST1 ADD ID NUMBER; UPDATE TEST1 SET ID = TEST1_SEQ.NEXTVAL; ALTER TABLE TEST1 ADD PRIMARY KEY(ID); 在执行UPDATE语句之前创建一个名为TEST1_SEQ的序列。 - msbyuva

根据@resnyanskiy的评论，即使表中有数据，这也可以工作。ID已填充并设置了非空约束。整个答案可以被该评论中的一行代码替换。 - Synesso

@EricWang 谢谢，Eric，你说得对 - 我相信这在一些版本（年份）之前是不起作用的，但我不确定。将答案转换为社区维基。 - leonbloy

在使用“ALTER TABLE <table> ADD COLUMN id SERIAL PRIMARY KEY”时，如何指定id为bigint？ - p.matsinopoulos

@p.matsinopoulos 只需将 SERIAL 替换为 BIGSERIAL。 - Israel Lins Albuquerque

Answer 2

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

以下是您需要进行的全部操作：

1. 添加 id 列。
2. 用从1到 count(*) 的序列填充它。
3. 将其设置为主键/非空。

@resnyanskiy 在评论中给出了此答案。

- Synesso

---

这个应该标记为答案，并且答案应该归属于@resnyanskiy。 - Eric

我必须先删除主键，然后运行这个命令。ALTER TABLE <table> DROP CONSTRAINT <pkey_name>; - Josh Robertson

Answer 3

在v10中使用自增列，

ALTER TABLE test 
ADD COLUMN id { int | bigint | smallint}
GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY PRIMARY KEY;

关于identity列的解释，请参见https://blog.2ndquadrant.com/postgresql-10-identity-columns/。

有关GENERATED BY DEFAULT和GENERATED ALWAYS之间的区别，请参见https://www.cybertec-postgresql.com/en/sequences-gains-and-pitfalls/。

如需更改序列，请参见https://popsql.io/learn-sql/postgresql/how-to-alter-sequence-in-postgresql/。

- jhoanna

---

这个解决方案的问题在于，如果表中已经包含行，则会出现错误：SQL Error [23502]: ERROR: column "id" contains null values。 - isapir

早期版本（pg 10和10.1）存在一个错误，导致出现这个错误。该问题在pg 10.2中得到修复。详细信息请参见：https://dba.stackexchange.com/q/200143/3684 - Erwin Brandstetter

Answer 4

我来到这里是因为我也在寻找类似的内容。在我的情况下，我需要将多个列的数据从一组分段表复制到一个表中，并给目标表分配行ID。这是我使用的一种上述方法的变体。我在目标表的末尾添加了序列列。这样我就不必在插入语句中为它设置占位符了。然后，简单的“select * into”语句就可以自动填充目标表的这一列。以下是我在PostgreSQL 9.6.4上使用的两个SQL语句。

ALTER TABLE target ADD COLUMN some_column SERIAL;
INSERT INTO target SELECT * from source;

- Dean Sha

Answer 5

ALTER TABLE test1 ADD id int8 NOT NULL GENERATED ALWAYS AS IDENTITY;

将test1表添加id int8列，非空并且作为标识列自动生成。

- yaseer

---

你的回答可以通过提供更多支持信息来改进。请编辑以添加进一步的细节，例如引用或文档，以便他人可以确认你的答案是正确的。您可以在帮助中心中找到有关如何编写良好答案的更多信息。 - Community

这并没有回答问题。一旦您拥有足够的声望，您将能够评论任何帖子；相反，提供不需要询问者澄清的答案。- 来自审核 - Smit Gajera

@yaseer 谢谢你的回答，它非常有效。 - undefined

Answer 6

我知道已经过了很多年，但这可能对其他用户有所帮助。 尝试使用第一个查询：

ALTER TABLE dbo."Users"
ALTER COLUMN "UserID" 
ADD GENERATED BY DEFAULT AS IDENTITY (MINVALUE 1 START WITH 1 INCREMENT BY 1) 

更新当前值的第二个查询：

SELECT setval('"Users_UserID_seq"', (SELECT max("UserID")+1 FROM dbo."Users"), false)

其中"Users"是一个表，"UserID"是主键。

- Павел К.