PostgreSQL中如何结合两个标识实现按house_id分组自增room_id

PostgreSQL没有内置按分组维度独立递增的序列功能，但完全不需要硬写无锁的max(room_id)+1逻辑——这种裸写方案并发场景下会生成重复ID，有更可靠的实现方式。

方案1：触发器+行级锁（最匹配需求，编号连续无空洞） #

这是最贴合你业务要求的实现，可保证同一house_id下room_id严格从1开始连续递增，且并发安全。
首先定义房间表结构，用(house_id, room_id)作为复合主键，天然限制同一房屋下房间编号唯一：

CREATE TABLE rooms (
  house_id bigint REFERENCES houses(house_id) ON DELETE CASCADE,
  room_id int NOT NULL,
  -- 其余房间业务字段，例如房间名称、面积、朝向
  room_name text,
  area numeric,
  PRIMARY KEY (house_id, room_id)
);

创建触发器函数，插入数据时自动生成对应room_id：

CREATE OR REPLACE FUNCTION generate_room_seq()
RETURNS TRIGGER AS $$
BEGIN
  -- 锁定houses表中当前house_id对应的行，阻塞同房屋的并发插入，避免编号重复
  -- 不同house_id的插入操作互不阻塞，不会影响跨房屋的插入性能
  PERFORM 1 FROM houses WHERE house_id = NEW.house_id FOR SHARE;
  -- 计算当前房屋下的下一个可用room_id，无存量房间时从1开始计数
  NEW.room_id := COALESCE(
    (SELECT MAX(room_id) + 1 FROM rooms WHERE house_id = NEW.house_id),
    1
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- 绑定触发器，插入数据前自动执行
CREATE TRIGGER trg_generate_room_id
BEFORE INSERT ON rooms
FOR EACH ROW
EXECUTE FUNCTION generate_room_seq();

注意：这个方案不会自动回填删除房间产生的编号空缺，如果要求删除房间后编号仍然保持连续，不要选这个方案。

方案2：视图动态生成编号（永远连续，零维护） #

如果你不需要room_id作为永久固定的关联字段，只是查询时需要展示同房屋下的顺序编号，完全不需要在表中存储room_id字段，直接用窗口函数在查询时动态生成即可：

-- 直接查询时生成编号
SELECT
  house_id,
  ROW_NUMBER() OVER (PARTITION BY house_id ORDER BY created_at) AS room_id,
  room_name,
  area
FROM rooms;

-- 也可以封装成视图方便调用
CREATE VIEW v_rooms_seq AS
SELECT
  house_id,
  ROW_NUMBER() OVER (PARTITION BY house_id ORDER BY created_at) AS room_id,
  *
FROM rooms;

这个方案的优点是无论怎么增删数据，编号永远是连续的1、2…n，没有任何维护成本；缺点是room_id不是固定值，删除中间的房间后，后续房间的编号会自动前移，不适合需要用room_id和其他业务表做关联的场景。

避坑提醒 #

• 不要直接在业务代码里裸写SELECT MAX(room_id)+1 FROM rooms WHERE house_id = ?生成编号：这种写法没有加锁保护，两个并发请求同时给同一个房屋新增房间时，会查到相同的max值，最终生成重复room_id引发主键冲突。
• 不要尝试为每个house_id单独创建独立数据库序列：当房屋数据量增长到成千上万级别时，海量的序列对象会带来极高的维护成本，完全没有必要。

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