使用插入。。。在冲突时不执行任何操作返回失败的行

Question

假设我有下表：

CREATE TABLE tags (
    id int PK,
    name varchar(255),
    CONSTRAINT name_unique UNIQUE(name)
)

我需要一个查询，将插入不存在的标签，并返回所有请求的标签ID。考虑以下事项：

INSERT INTO tags (name) values ('tag10'), ('tag6'), ('tag11') ON CONFLICT DO NOTHING returning id, name

此查询的输出是：

+---------------+
|  id   |  name |
|---------------|
|  208  | tag10 |
|---------------|
|  209  | tag11 |
+---------------+

我需要的是在输出中有tag6。

Answer 1

与此表：

CREATE TABLE tags (
    id serial PRIMARY KEY,
    name text UNIQUE
);

只要此查询是唯一的，解决方案中的值就很长：

INSERT INTO tags (name) 
VALUES ('tag10'), ('tag6'), ('tag11') 
ON CONFLICT DO UPDATE name = EXCLUDED.name RETURNING id, name;

Answer 2

有点罗嗦，但我想不出其他任何东西：

with all_tags (name) as (
  values ('tag10'), ('tag6'), ('tag11')
), inserted (id, name) as (
   INSERT INTO tags (name)
   select name 
   from all_tags
   ON CONFLICT DO NOTHING 
   returning id, name
)
select t.id, t.name, 'already there'
from tags t
  join all_tags at on at.name = t.name
union all
select id, name, 'inserted'
from inserted;

从标签中进行外部选择可以看到插入新标签之前表格的快照。带有常量的第三列仅用于测试查询，以便识别插入了哪些行和未插入哪些行。