How do I avoid inserting duplicates and return ONLY conflicting rows?

Question

I have a table that is essentially this:

CREATE TABLE stuff (
  row_id VARCHAR(64) NOT NULL UNIQUE,
  content TEXT
)

When I insert into this table, I want to avoid inserting duplicates and return only the potentially duplicated values.

I can get around the first part of the issue by using this query:

INSERT INTO stuff (row_id, content) VALUES ('1', 'Here's the content') ON CONFLICT (message_id) DO NOTHING;

But if there IS a conflict, I need to return just the values that were causing the conflict. For example, if my table looks like this:

| row_id | content    |
|:------ | ----------:| 
| "1"    | "TEXT here"|
| "2"    | "more text"|
| "3"    | "text+text"|

and I attempt to insert duplicate values:

INSERT INTO stuff (row_id, content) VALUES ('1', 'Here's the content'), ('2', 'more content') ON CONFLICT (message_id) DO NOTHING;

the duplicates should not insert, and the query should return the row_id(s) where this conflict occurred ["1", "2"]. Any help would be appreciated! (Also, I'm running these queries in a node.js environment, formatting queries w/ pg-format (maybe this is relevant?))

Answer 1

I feel like you could do this with common table expressions, using something like:

with joined as (
  select v.row_id,
         v.content,
         s.stuff_row_id
  from   (
           values 
          ('1', 'Here's the content'), 
          ('2', 'more content') 
         ) as v (row_id, content) 
  left join stuff s on v.row_id = s.row_id
),
add_them as (
  insert into stuff
  select row_id, 
         content
  from   joined 
  where  stuff_row_id is null
),
select row_id, content
from   joined 
where  stuff_row_id is not null
;

There may be all sorts of syntax errors in there, mind!

Does that method make sense?