[SOLVED] Use INSERT ... ON CONFLICT DO NOTHING RETURNING failed rows

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

Suppose I have the following table:

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

I need a query that will insert tags that do not exists and return ids for all requested tags. Consider the following:

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

The output of this query is:

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

What I need is to have tag6 in the output.

Solution

A bit verbose, but I can't think of anything else:

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;

The outer select from tags sees the snapshot of the table as it was before the new tags were inserted. The third column with the constant is only there to test the query so that one can identify which rows were inserted and which not.