如何在upsert中獲取衝突行的ID？

我有一個tag包含 2 列的表：id（uuid）和name（文本）。我現在想在表中插入一個新標籤，但如果標籤已經存在，我想簡單地獲取id現有記錄的。

我以為我可以ON CONFLICT DO NOTHING結合使用RETURNING "id"：

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

但是，如果名稱為“foo”的標籤已經存在，這將返回一個空的結果集。

然後我將查詢更改為使用 noopDO UPDATE子句：

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

這按預期工作，但有點令人困惑，因為我只是將名稱設置為已經存在的值。

這是解決這個問題的方法還是我缺少一種更簡單的方法？

這將在所有 3 種情況下都有效（據我測試），如果要插入的值都是新的或都已經在表中或混合：

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 ;

可能還有其他一些方法可以做到這一點，也許不使用新ON CONFLICT語法。

引用自：https://dba.stackexchange.com/questions/129522