在兩個表中插入行，保留與第三個表的連接

作為最近的數據庫開發項目的一部分，我遇到了一個似乎找不到優雅解決方案的查詢（即沒有循環）。

架構的相關位：

CREATE TABLE sites (
 site_id SERIAL PRIMARY KEY,
 ...
);

CREATE TABLE properties (
 property_id SERIAL PRIMARY KEY,
 property_def_id INT NOT NULL,
 FOREIGN KEY (property_def_id) REFERENCES property_def(property_def_id)
);

CREATE TABLE site_properties (
 property_id INT NOT NULL,
 site_id INT NOT NULL,
 PRIMARY KEY (property_id, site_id),
 FOREIGN KEY (property_id) REFERENCES properties(property_id),
 FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

任務：為表中的每一行在表中

插入一個新的、不同的屬性，並在其中插入一行以儲存兩者之間的關係。properties``sites``site_properties

期望的行為：

for each site_id (as my_site_id) in sites:  
* INSERT INTO properties (property_def_id) VALUES (1) RETURNING property_id INTO my_prop_id
* INSERT INTO site_properties (property_id, site_id) VALUES (my_prop_id, my_site_id)

最後，我會在 site_properties 表中將每個“新”屬性僅分配給一個站點（即沒有交叉連接）

我覺得應該可以在一個查詢中做到這一點，但我無法弄清楚如何為我的愛。我已經嘗試過 CTE，但我最終被一個 property_id 列表、一個站點列表困住了，並且無法在兩者之間加入。

我錯過了什麼？

問題

RETURNINGan 子句中的表達式INSERT只能涉及插入行中的列，但不能涉及加入的其他表中的列。這使得很難將表中每個新插入的行與不同於propertiestable的行相關聯。site_id``sites

手冊INSERT：

可選RETURNING子句導致基於實際插入（或更新，如果使用 子句INSERT）的每一行計算和返回值。這主要用於獲取預設提供的值，例如序列號。但是，任何使用表列的表達式都是允許的。ON CONFLICT DO UPDATE

大膽強調我的。

解決方案

使用**nextval()**序列函式係列可以廉價而優雅地解決這個問題。曾經是Postgres 8.2RETURNING引入之前的選擇方式，現在仍然有效。INSERT

使用pg_get_serial_sequence()您也不需要知道serial列的基礎序列的名稱：

WITH site_prop AS (
  SELECT site_id, nextval(pg_get_serial_sequence('properties', 'property_id')) AS prop_id
  FROM   sites
  )
, props AS (
  INSERT INTO properties (property_id, property_def_id)
  SELECT prop_id, 1 FROM site_prop
  )
INSERT INTO site_properties (site_id, property_id)
SELECT site_id, prop_id FROM site_prop;

這樣，您只需要一次掃描表sites，既不需要視窗函式也不需要連接。便宜得多。

更好的是

在 key 中不存在的情況下插入帶有 new property_idinto的行似乎是違反 FK的。但是，如果您將鍵插入到同一命令中，情況並非如此。CTE 被視為同一命令的一部分。手冊：site_properties``properties``properties

在每個命令之後將立即檢查不可延遲的約束。

所以我們可以再簡化一些，使用andnext_val() 子句RETURNING：

WITH site_prop AS (
  INSERT INTO site_properties (site_id, property_id)
  SELECT site_id, nextval(pg_get_serial_sequence('properties', 'property_id'))
  FROM   sites
  RETURNING site_id, property_id
  )
INSERT INTO properties (property_id, property_def_id)
SELECT property_id, 1 FROM site_prop;

有關的：

• PostgreSQL 序列的下一個值？
• 插入相關表

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