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?))
CodePudding user response:
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?