postgresql - Use INSERT ... ON CONFLICT DO NOTHING RETURNING failed rows -

suppose have following table:

create table tags (     id int pk,     name varchar(255),     constraint name_unique unique(name) ) 

i need query insert tags not exists , return ids requested tags. consider following:

insert tags (name) values ('tag10'), ('tag6'), ('tag11') on conflict nothing returning id, name 

the output of query is:

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

what need have tag6 in output.

a bit verbose, can't think of else:

with all_tags (name) (   values ('tag10'), ('tag6'), ('tag11') ), inserted (id, name) (    insert tags (name)    select name     all_tags    on conflict nothing     returning id, name ) select t.id, t.name, 'already there' tags t   join all_tags @ on at.name = t.name union select id, name, 'inserted' inserted; 

the outer select tags sees snapshot of table before new tags inserted. third column constant there test query 1 can identify rows inserted , not.