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

Question

我有一个包含现有数据的 PostgreSQL 表。

如何在不删除并重新创建表的情况下添加自增主键？

Answer 1

PostgreSQL v10+ 的版本

假设您有一个表 table_name，您想要向其中添加一个自动递增的主键 id（代理）列。 推荐的方法是使用形式 GENERATED { ALWAYS |默认} AS IDENTITY [(sequence_options)]。

例如（ref）

ALTER TABLE table_name 
ADD COLUMN id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY;

或

ALTER TABLE table_name 
ADD COLUMN id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY;

旧版本的 PostgreSQL

不建议这样做但自 PostgreSQL v16 起继续受支持。

注意：旧的 SERIAL 形式已替换为 GENERATED ... AS IDENTITY 在 PostgreSQL v10 中，因为 SERIAL 可能会导致问题，例如允许意外覆盖该值以及需要更多授权才能允许插入（PostgreSQL：串行与身份）。

使用以下形式：

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

在内部，此 SERIAL 未保留，它在解析时扩展为 SEQUENCE 和 SET DEFAULT nextval({sequence_name})< /code> （更详细的讨论），让您免于显式地键入旧版本中所需的内容，如下所述：

甚至旧版本的 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');

Answer 2

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

您需要做的就是：

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

感谢 @resnyanskiy，他在评论中给出了这个答案。

Answer 3

要在 v10 中使用标识列，

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

有关标识列的说明，请参阅 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/。

Answer 4

我来到这里是因为我也在寻找类似的东西。就我而言，我将数据从一组具有许多列的临时表复制到一个表中，同时还将行 ID 分配给目标表。这是我使用的上述方法的变体。
我在目标表的末尾添加了序列列。这样我就不必在 Insert 语句中为其添加占位符。然后简单的 select * into 目标表自动填充此列。这是我在 PostgreSQL 9.6.4 上使用的两条 SQL 语句。

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

Answer 5

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

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” - 主键

Answer 6

我有 Postgres 15.4

例如

• 表：'post'。
• column: 'id' 主列整数，不自动递增。

---

1 - 添加 GENERATED IDENTITY

ALTER TABLE post ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY

2 - 设置 MAX(id) 的序列值，如果没有记录，则设置 1

SELECT SETVAL(pg_get_serial_sequence(post, 'id'), COALESCE((SELECT MAX(id) FROM post) + 1, 1))

读取：

1. 参数 GENERATED BY DEFAULT 和 ALWAYS序列
2. Postgres 中的

Answer 7

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