就在这里returning
INSERT INTO tag ("key", "value")
SELECT 'key1', 'value1'
WHERE NOT EXISTS (
SELECT id, "key", "value"
FROM node_tag
WHERE key = 'key1' AND value = 'value1'
)
returning id, "key", "value"
如果该行已存在则返回该行
with s as (
select id, "key", "value"
from tag
where key = 'key1' and value = 'value1'
), i as (
insert into tag ("key", "value")
select 'key1', 'value1'
where not exists (select 1 from s)
returning id, "key", "value"
)
select id, "key", "value"
from i
union all
select id, "key", "value"
from s
如果该行不存在,它将返回插入的行,否则返回现有的行。
顺便说一句,如果“键”/“值”对使其唯一,那么它就是主键,并且不需要 id 列。除非“键”/“值”对中的一个或两个可以为空。