How to get the ID of the conflicting row in upsert?
This will work (as far as I tested) in all 3 cases, if the to-be-inserted values are all new or all already in the table or a mix:
WITH
val (name) AS
( VALUES -- rows to be inserted
('foo'),
('bar'),
('zzz')
),
ins AS
( INSERT INTO
tag (name)
SELECT name FROM val
ON CONFLICT (name) DO NOTHING
RETURNING id, name -- only the inserted ones
)
SELECT COALESCE(ins.id, tag.id) AS id,
val.name
FROM val
LEFT JOIN ins ON ins.name = val.name
LEFT JOIN tag ON tag.name = val.name ;
There's probably some other ways to do this, perhaps without using the new ON CONFLICT
syntax.
No idea if how this will perform but just as another option to try, here is doing the same an old-school way (without ON CONFLICT
):
WITH items (name) AS (VALUES ('foo'), ('bar'), ('zzz')),
added AS
(
INSERT INTO tag (name)
SELECT name FROM items
EXCEPT
SELECT name FROM tag
RETURNING id
)
SELECT id FROM added
UNION ALL
SELECT id FROM tag
WHERE name IN (SELECT name FROM items)
;
That is, insert only the [unique] names not found in the tag
table and return the IDs; combine that with the IDs of the names that do exist in tag
, for the final output. You can also throw name
into the output, as suggested by ypercubeᵀᴹ, so that you know which ID matches which name.