How to get the ID of the conflicting row in upsert?
32
votes
3
answers
27194
views
I have a table
tag
with 2 columns: id
(uuid) and name
(text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id
of the existing record.
I assumed I could just use ON CONFLICT DO NOTHING
in combination with RETURNING "id"
:
INSERT INTO
"tag" ("name")
VALUES( 'foo' )
ON CONFLICT DO NOTHING
RETURNING "id";
But this returns an empty result set, if the tag with the name "foo" already exists.
I then changed the query to use a noop DO UPDATE
clause:
INSERT INTO
"tag" ("name")
VALUES( 'foo' )
ON CONFLICT ("name") DO UPDATE SET "name" = 'foo'
RETURNING "id";
This works as intended, but it is somewhat confusing, because I'm just setting the name to the already existing value.
Is this the way to go about this problem or is there a simpler approach I'm missing?
Asked by Oliver Salzburg
(422 rep)
Feb 16, 2016, 03:59 PM
Last activity: May 27, 2025, 03:34 PM
Last activity: May 27, 2025, 03:34 PM