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