How to get the ID of the conflicting row in upsert?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

With postgres 18+:

    INSERT INTO
        "tag" ("name")
    VALUES( 'foo' )
    ON CONFLICT DO NOTHING
    RETURNING old."id";