PostgreSQL 12：插入值时自动创建关联用户（原子操作）

可以通过PostgreSQL的WITH公共表表达式（CTE）结合ON CONFLICT语法实现原子操作，确保要么创建新用户并插入对应数据，要么直接引用已有用户插入数据，整个过程在单个事务内完成，避免并发冲突。

完整SQL语句如下（使用参数占位符适配你的输入参数）：

WITH user_upsert AS (
    INSERT INTO users (tenant, name)
    VALUES (:tenant, :name)
    ON CONFLICT (tenant, name) DO NOTHING
    RETURNING id
)
INSERT INTO values (user_id, value)
SELECT id, :value
FROM user_upsert
UNION ALL
SELECT id, :value
FROM users
WHERE (tenant, name) = (:tenant, :name)
ON CONFLICT (user_id, value) DO NOTHING;

各部分说明： #

• user_upsert CTE：尝试插入指定tenant和name的用户。由于users表有(tenant, name)的唯一约束，若用户已存在则触发冲突，执行DO NOTHING，仅返回新创建用户的id。
• 插入values表：
  • 先从user_upsert中获取新创建用户的id，插入对应value；
  • 若user_upsert无返回结果（说明用户已存在），则从users表查询该用户的id并插入数据；
  • 最后通过ON CONFLICT (user_id, value) DO NOTHING避免重复插入同一用户的相同value，符合values表的唯一约束。

原子性保证： #

PostgreSQL会将整个WITH子句与后续INSERT视为单个事务执行，即便并发场景下多个请求同时尝试创建同一用户，ON CONFLICT也会确保仅一个请求成功创建用户，其余请求会直接引用已存在的用户ID，不会出现数据不一致或外键冲突问题。

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