PostgreSQL中基于复合主键实现用户专属自动递增记录ID

我有一张存储用户记录的notes表，需要为每个用户创建的记录生成该用户专属的唯一ID（整个表中ID可重复）。示例如下：

即每个用户的特定ID仅对应一条记录。

当前表结构如下：

CREATE TABLE "notes" (
  "id" int,
  "user" int,
  "text" varchar,
  "created" timestamp NOT NULL
);

CREATE UNIQUE INDEX ON "notes" ("id", "user");

我希望用户插入记录时，数据库自动为其生成该用户专属的递增且唯一的ID。请问有实现方法吗？

更新：我尝试执行以下查询：

INSERT INTO "notes" VALUES (
    5, 1, 'test1', NULL, ('2023-06-27 13:12:29.611415')::TIMESTAMP
) 
ON CONFLICT (id, "user") DO 
UPDATE SET id = "notes".id + 1;

但有时会报错duplicate key value violates unique constraint "notes_id_user_idx"（因用户可能删除部分记录）。我希望数据库能生成用户未使用的唯一ID，且不修改已保存的记录，仅在插入时生成ID。

方法1：触发器自动生成（推荐） #

通过触发器函数在插入前自动计算当前用户的专属ID，无需手动指定，适配绝大多数场景。

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION generate_user_note_id()
RETURNS TRIGGER AS $$
BEGIN
    -- 获取当前用户已有记录的最大ID，无记录则从1开始
    NEW.id := COALESCE((SELECT MAX(id) FROM notes WHERE "user" = NEW."user"), 0) + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到表 #

CREATE TRIGGER trigger_auto_note_id
BEFORE INSERT ON notes
FOR EACH ROW
EXECUTE FUNCTION generate_user_note_id();

使用示例 #

插入时无需指定id字段，数据库自动生成：

INSERT INTO notes ("user", text, created) VALUES (1, '新笔记内容', NOW());

高并发优化 #

如果存在大量并发插入场景，为避免多个事务同时计算MAX(id)导致重复ID，可修改触发器函数添加锁：

CREATE OR REPLACE FUNCTION generate_user_note_id()
RETURNS TRIGGER AS $$
BEGIN
    -- 锁定当前用户的所有记录，确保并发时ID生成唯一
    PERFORM 1 FROM notes WHERE "user" = NEW."user" FOR UPDATE;
    NEW.id := COALESCE((SELECT MAX(id) FROM notes WHERE "user" = NEW."user"), 0) + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

方法2：手动计算ID（适合单次插入） #

如果不想用触发器，每次插入前先查询当前用户的最大ID，再执行插入：

INSERT INTO notes (id, "user", text, created)
SELECT COALESCE((SELECT MAX(id) FROM notes WHERE "user" = 1), 0) + 1, 1, '手动插入笔记', NOW();

注意：这种方式需要保证插入操作的原子性，高并发场景下建议优先用触发器方案。

关键说明 #

• 上述方案生成的ID会基于当前用户的最大ID递增，不会复用已删除记录的空缺ID（如果需要复用空缺ID，需调整逻辑为查找最小未使用的ID，但这种场景较少见且性能较低）。
• 你的原方案用ON CONFLICT更新ID不可行，因为更新后的ID可能依然冲突，且会修改已有记录，不符合你的需求。

内容的提问来源于stack exchange，提问作者pizhlo