PostgreSQL实现不同用户独立递增ID的最优方案

我有一个数据库，其中包含存储用户信息的members表，结构如下：

另有一个存储帖子信息的posts表，当前结构如下：

我希望实现每个用户拥有独立的ID计数器，效果如下（每个用户的帖子ID从1开始递增）：

或者采用新增全局唯一main_id的结构：

我知道可以将id和member_id组合成唯一键，但这样无法使用自动递增计数器，需要在后端通过逻辑计算新ID。我想知道是否可以让PostgreSQL自动处理：传入member_id时，自动为该用户分配下一个可用的递增ID，就像普通计数器一样，是否可行？

完全可以实现，推荐使用触发器+用户计数器表的方案，让PostgreSQL自动维护每个用户的独立ID递增逻辑，无需后端额外计算：

步骤1：创建用户帖子计数器表 #

专门记录每个用户的下一个可用帖子ID，确保原子性：

CREATE TABLE member_post_counters (
    member_id INT PRIMARY KEY REFERENCES members(id),
    next_post_id INT DEFAULT 1
);

步骤2：初始化现有用户的计数器 #

为已存在的用户初始化起始ID：

INSERT INTO member_post_counters (member_id)
SELECT id FROM members;

步骤3：编写生成下一个帖子ID的函数 #

实现原子性的ID递增逻辑，同时兼容新用户的自动初始化：

CREATE OR REPLACE FUNCTION get_next_post_id(p_member_id INT)
RETURNS INT AS $$
DECLARE
    next_id INT;
BEGIN
    -- 原子更新并获取当前可用ID
    UPDATE member_post_counters
    SET next_post_id = next_post_id + 1
    WHERE member_id = p_member_id
    RETURNING next_post_id - 1 INTO next_id;
    
    -- 若用户不存在（新用户），自动初始化计数器并返回1
    IF next_id IS NULL THEN
        INSERT INTO member_post_counters (member_id) VALUES (p_member_id)
        RETURNING next_post_id - 1 INTO next_id;
    END IF;
    
    RETURN next_id;
END;
$$ LANGUAGE plpgsql VOLATILE;

步骤4：修改posts表结构 #

根据你选择的结构二选一：

结构一（无全局main_id） #

ALTER TABLE posts
DROP COLUMN id,
ADD COLUMN id INT,
ADD CONSTRAINT unique_member_post_id UNIQUE (member_id, id);

结构二（带全局唯一main_id） #

ALTER TABLE posts
DROP COLUMN id,
ADD COLUMN id INT,
ADD COLUMN main_id SERIAL PRIMARY KEY,
ADD CONSTRAINT unique_member_post_id UNIQUE (member_id, id);

步骤5：创建触发自动赋值的触发器 #

让插入操作自动调用函数生成用户专属ID：

CREATE OR REPLACE FUNCTION set_post_id()
RETURNS TRIGGER AS $$
BEGIN
    NEW.id = get_next_post_id(NEW.member_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_post_id
BEFORE INSERT ON posts
FOR EACH ROW EXECUTE FUNCTION set_post_id();

使用示例 #

插入帖子时只需传入member_id和content，PostgreSQL会自动分配用户专属的递增ID：

INSERT INTO posts (member_id, content) VALUES (1, 'Hello New Post');
INSERT INTO posts (member_id, content) VALUES (2, 'Hello New Post');

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