Jak uzyskać identyfikator konfliktu wiersza w upsert?

Mam tabelę tagz 2 kolumnami: id(uuid) i name(tekst). Chcę teraz wstawić nowy znacznik do tabeli, ale jeśli znacznik już istnieje, chcę po prostu pobrać idistniejący rekord.

Zakładałem, że mogę po prostu użyć ON CONFLICT DO NOTHINGw połączeniu z RETURNING "id":

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

Zwraca to jednak pusty zestaw wyników, jeśli znacznik o nazwie „foo” już istnieje.

Następnie zmieniłem zapytanie, aby użyć DO UPDATEklauzuli noop :

INSERT INTO
    "tag" ("name")
VALUES( 'foo' )
ON CONFLICT ("name") DO UPDATE SET "name" = 'foo'
RETURNING "id";

Działa to zgodnie z przeznaczeniem, ale jest nieco mylące, ponieważ po prostu ustawiam nazwę na już istniejącą wartość.

Czy to jest sposób na rozwiązanie tego problemu, czy brakuje mi prostszego podejścia?

Będzie to działać (o ile testowałem) we wszystkich 3 przypadkach, jeśli wartości do wstawienia są nowe lub wszystkie już znajdują się w tabeli lub mieszance:

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 ;

Prawdopodobnie istnieją inne sposoby, aby to zrobić, być może bez użycia nowej ON CONFLICTskładni.

Nie mam pojęcia, jak to będzie działać, ale jako kolejna opcja do wypróbowania, tutaj robi to samo w oldschoolowy sposób (bez 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)
;

Oznacza to, że wstaw tylko [unikalne] nazwy nie znalezione w tagtabeli i zwróć identyfikatory; połącz to z identyfikatorami nazw, które istnieją w tagcelu uzyskania ostatecznego wyniku. Możesz także wrzucić namedane wyjściowe, zgodnie z sugestią ypercubeᵀᴹ , abyś wiedział, który identyfikator pasuje do której nazwy.