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?))

Check [here](https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql) — Ergest Basha, Mar 09 '22 at 19:09
@ErgestBasha was wondering if there was a more graceful way to do it, but if that's truly the best answer then I guess i'll just have to wrestle with pg-format until something works lol — Vincent Lauffer, Mar 09 '22 at 19:14

score 1 · Accepted Answer · answered Mar 09 '22 at 19:08

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?

I'm basically sql illiterate lol definitely need to read up more — Vincent Lauffer, Mar 09 '22 at 20:31

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

1 Answers1