如果名稱不存在則插入，如果存在則返回 id

假設我有一個字元串數組“a”、“b”、“c”、“d”、“e”。

CREATE TABLE foo (
 id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

傳遞字元串數組並插入每個字元串（如果name不存在）並返回給定數組的所有 ID 的查詢是什麼？在此範例中，它應該1,2,3,4,5作為 SQL 執行的輸出返回。

***INSERT這是orSELECT***的反復出現的問題，與常見的 UPSERT 相關，但不一樣。INSERT ... ON CONFLICT ... DO ...（通常稱為 UPSERT），在 Postgres 9.5 中引入，在任何情況下都是有用的。

假設表上沒有並發寫入負載的簡單情況：

CREATE TABLE foo (
  id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

WITH input(name) AS (SELECT unnest('{a,b,c,d,e}'::text[]))  -- input array once
, ins AS (
   INSERT INTO foo(name) 
   TABLE  input
   ON CONFLICT (name) DO NOTHING
   RETURNING id
   )
SELECT f.id
FROM   input i
JOIN   foo   f USING (name)
UNION  ALL
TABLE  ins;

| 編號 |
| -: |
| 1 |
| 2 |
| 4 |
| 5 |
| 7 |

db<>在這裡擺弄

請注意缺少的序列號3和6. UPSERT 的一個副作用是衝突的行會刻錄一個序列號，因為在檢查衝突*之前會獲取預設值，並且永遠不會設置序列號。*這應該是無關緊要的，因為在任何時候都可以預料到序列號的差距。這意味著，您不能依賴1,2,3,4,5給定範例（和假設設置）的結果。

詳細解釋和替代解決方案 - 特別是對於並發寫入負載：

• 函式中的 SELECT 或 INSERT 是否容易出現競爭條件？
• 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT？

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