PostgreSQL:如果不存在，请插入，然后选择

Question

问题

想象一下有以下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，如果特定用户的行不存在，则具有默认值的行将看起来像这样：

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

(其中%s是占位符，我将在其中提供用户ID(

我也知道要获得用户的设置(也称为SELECT(，我可以做以下操作：

SELECT * FROM setting WHERE user_id = %s

但是，我正在尝试将两者结合起来，在这里我可以检索用户的设置，并且如果特定用户的设置还不存在，则INSERT默认值并返回这些值。

示例

所以它看起来像这样：

假设Alice的设置已经保存在数据库中，但Bob是新用户，没有。

当我们使用Alice的用户ID执行神奇的SQL查询时，它将返回存储在数据库中的Alice的设置。如果我们对Bob的用户ID执行相同的神奇SQL查询，它将检测到Bob在数据库中没有保存任何设置，因此它将使用所有默认值INSERT的记录设置，然后返回Bob新创建的设置。

Answer 1

正如Frank Heikens所说，假设user_id上存在UNIQUE或PK约束，则尝试插入，如果它违反了约束，则不执行任何操作，并在tCTE中返回插入的行(如果有的话(，将其与"正确"的select并用，只选择第一行。如果插入返回一行，优化器会注意不要进行额外的选择。

with t as
(
insert into setting (user_id) values (%s)
on conflict do nothing
returning *
)
select * from t
union all
select * from setting where user_id = %s
limit 1;

Answer 2

不需要魔法。使用returning和union all:

with inparms as ( -- Put your input parameters in CTE so you bind only once
select %s::bigint as user_id
), cond_insert as ( -- Insert the record if not exists, returning *
insert into settings (user_id)
select i.user_id
from inparms i
where not exists (select 1 from settings where user_id = i.user_id)
returning *
)
select *              -- If a record was inserted, get it
from cond_insert
union all
select s.*            -- If not, then get the pre-existing record
from inparms i
join settings s on s.user_id = i.user_id;