帶有嵌套 CTE 的條件插入？

我試圖弄清楚是否有一種方法可以使嵌套 CTE 適用於這種特殊情況。

考慮以下基於實際應用程序的（高度人為的）場景：有一個員工 ID 的單列表。然後是一個包含所有詳細資訊的員工屬性表。（單個 col 表背後的主要原因通常是理所當然地需要在知道實際員工的任何細節之前批量創建和分配新員工 ID。）

現在到手頭的任務，我們正在插入新員工的詳細資訊（即姓名），但首先我們需要檢查是否已經存在具有該姓名的員工。如果是，我們將簡單地返回 id，如果不是，我們將創建一個新的員工記錄，然後插入詳細資訊，最後返回新創建的 id。

要重新創建此測試場景：

CREATE TABLE public.employee (
   id text DEFAULT gen_random_uuid(),
   PRIMARY KEY (id)
);

CREATE TABLE public.employee_details (
   employee_id text,
   name text,
   PRIMARY KEY (employee_id),
   FOREIGN KEY (employee_id) REFERENCES public.employee(id)
);

我試圖敲定的查詢如下所示。

with 
e as 
    (select name, employee_id from employee_details where name = 'jack bauer'), 

i as (insert into employee_details (name, employee_id) 
    select 'jack bauer', 
       (with a as (insert into employee values(default) RETURNING id) select a.id from a)
   where not exists (select 1 from e) returning name, employee_id) 

select employee_id, name from e
union all 
select employee_id, name from i; 

如果我用已經創建的 id 替換嵌套的 CTE（單獨執行嵌套的 CTE），它可以工作（但可能導致創建多餘的 id）。with e as (..), i as (..), a as (..) select .. where not exists...也可以簡單地將嵌套的CTE移動到頂層（所以整個事情看起來像做到這一點“內聯” - 所以只有在not exists子句返回 true時才會創建新的 id 。

我不斷收到錯誤：

包含數據修改語句的 WITH 子句必須位於頂層。

我想問題在於嵌套的 CTE 返回一個“列”，而如果它獲得一個“值”，則整個查詢將起作用（當一個簡單地複製文本值而不是 CTE 時，它會起作用）。我確實在這個問題上遇到了一些相關的討論，提到了一個自 9.3 以來已修復的明顯錯誤。我不知道這是否與我在這裡的麻煩有關。引用連結的討論：

解析分析程式碼似乎認為 WITH 只能附加到集合操作樹內的頂層或葉級 SELECT；但語法遵循 SQL 標準，沒有這樣的說法

我正在使用 Postgres 10.3。

出於這個問題的目的，我假設employee_details.name被定義為UNIQUE. 否則，整個操作將毫無意義。

您不能像您嘗試的那樣嵌套數據修改 CTE（因為您已經發現了困難的方法） - 而且您不需要。此查詢將實現您的目標：

WITH e AS (
  SELECT name, employee_id
  FROM   employee_details
  WHERE  name = 'jack bauer'
  )
, i1 AS (
  INSERT INTO employee             -- no target columns!
  SELECT                           -- empty SELECT list!
  WHERE NOT EXISTS (SELECT FROM e)
  RETURNING id
  )
, i2 AS (
  INSERT INTO employee_details (name, employee_id) 
  SELECT 'jack bauer', id
  FROM   i1
  RETURNING name, employee_id
  )
SELECT employee_id, name FROM e
UNION ALL 
SELECT employee_id, name FROM i2;

核心功能是INSERT沒有目標列和空的SELECT. SELECTPostgres用預設值填充所有未列出的列。這樣我們就可以用有條件的替換VALUES (default)無條件的INSERT。i1如果未找到給定名稱，CTE僅插入一行。

手冊：

如果沒有清單

$$ target $$列名完全給出，預設是表中所有列的聲明順序；$$ … $$ 顯式或隱式列列表中不存在的每一列都將填充一個預設值，要麼是其聲明的預設值，要麼是 null 如果沒有。

這是標準的 Postgres 特定擴展：

此外，標準不允許列名列表被省略，但並非所有列都從VALUES子句或*query*, 中填充的情況。

如果返回一行，則最終 CTEi2僅插入一行。i1瞧。

這取決於對同一表的***並發寫入負載下的競爭條件。***如果你需要排除這種情況，你需要做更多的事情。有關的：

• 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT？

如果沒有第二個表中條件 INSERT 的複雜性，這將歸結為SELECT 或 INSERT的常見情況：

• 函式中的 SELECT 或 INSERT 是否容易出現競爭條件？

在旁邊

"id" text DEFAULT gen_random_uuid()

我強烈建議使用數據類型uuid來儲存 UUID。

• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎
• MD5 欄位的最佳數據類型是什麼？

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