PostgreSQL实现：不存在则插入默认值并返回数据

需求：查询指定用户的设置记录，若该用户不存在，则自动插入带默认值的记录并返回这条新创建的记录；若用户已存在，则直接返回其已有记录。

原表结构 #

CREATE TABLE setting (
  user_id bigint PRIMARY KEY NOT NULL,
  language lang NOT NULL DEFAULT 'english',
  foo bool NOT NULL DEFAULT true,
  bar bool NOT NULL DEFAULT true
);

原有单独语句 #

• 仅当用户不存在时插入默认值的语句：

INSERT INTO setting  (user_id)
    SELECT %s
    WHERE NOT EXISTS (SELECT 1 FROM setting WHERE user_id = %s)

• 查询用户设置的语句：

SELECT * FROM setting WHERE user_id = %s

合并后的实现语句 #

可以通过WITH子句将插入与查询逻辑合并，确保无论用户是否存在，都能在单条语句内返回对应的设置记录：

WITH inserted AS (
  INSERT INTO setting (user_id)
  SELECT %s
  WHERE NOT EXISTS (SELECT 1 FROM setting WHERE user_id = %s)
  RETURNING *
)
SELECT * FROM inserted
UNION ALL
SELECT * FROM setting WHERE user_id = %s AND NOT EXISTS (SELECT 1 FROM inserted);

逻辑说明 #

1. 先通过inserted公共表表达式执行插入操作：仅当目标user_id不存在时，插入带默认值的记录，并返回插入的内容。
2. 若插入成功（inserted有数据），则返回插入的记录；若插入未执行（用户已存在），则执行第二个SELECT返回已有记录。

也可以利用PostgreSQL的ON CONFLICT特性简化实现，但需要注意如果用户已存在时该语句不会返回结果，因此需要分两次执行：

-- 先尝试插入，存在则不操作并返回空，不存在则插入并返回新记录
INSERT INTO setting (user_id)
VALUES (%s)
ON CONFLICT (user_id) DO NOTHING
RETURNING *;

-- 若上述语句无返回结果，执行查询返回已有记录
SELECT * FROM setting WHERE user_id = %s;

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