我的 PostgreSQL 12 資料庫中有 2 個具有一對多關系 ( thing, 和thing_identifier) 的永久表。第二個 -- thing_identifier-- 有一個列參考thing，這樣thing_identifier可以保存給定的多個外部識別符號thing：
CREATE TABLE IF NOT EXISTS thing
(
thing_id SERIAL PRIMARY KEY,
thing_name TEXT, --this is not necessarily unique
thing_attribute TEXT --also not unique
);
CREATE TABLE IF NOT EXISTS thing_identifier
(
id SERIAL PRIMARY KEY,
thing_id integer references thing (thing_id),
identifier text
);
我需要插入一些新資料thing和thing_identifier，這兩者都來自我創建通過使用一個表COPY來一個大的CSV檔案的內容拉入資料庫，是這樣的：
CREATE TABLE IF NOT EXISTS things_to_add
(
id SERIAL PRIMARY KEY,
guid TEXT, --a unique identifier used by the supplier
thing_name TEXT, --not unique
thing_attribute TEXT --also not unique
);
樣本資料：
INSERT INTO things_to_add (guid, thing_name) VALUES
('[111-22-ABC]','Thing-a-ma-jig','pretty thing'),
('[999-88-XYZ]','Herk-a-ma-fob','blue thing');
目標是讓每一行things_to_add結果在一個新行中，每一行都在thing和 中thing_identifier，如下所示：
thing：
| thing_id | thing_name | thing attribute |
|----------|---------------------|-------------------|
| 1 | thing-a-ma-jig | pretty thing
| 2 | herk-a-ma-fob | blue thing
thing_identifier：
| id | thing_id | identifier |
|----|----------|------------------|
| 8 | 1 | '[111-22-ABC]' |
| 9 | 2 | '[999-88-XYZ]' |
I could use a CTE INSERTstatement (with RETURNING thing_id) to get the thing_id that results from the INSERT on thing, but I can't figure out how to get both that thing_id from the INSERT on thing and the original guid from things_to_add, which needs to go into thing_identifier.identifier.
Just to be clear, the only guaranteed unique column in thing is thing_id, and the only guaranteed unique column in things_to_add is id (which we don't want to store) and guid (which is what we want in thing_identifier.identifier), so there isn't any way to join thing and things_to_add after the INSERT on thing.
uj5u.com熱心網友回復：
您可以從以下檔案中檢索 thing_to_add.guid JOIN：
WITH list AS
(
INSERT INTO thing (thing_name)
SELECT thing_name
FROM things_to_add
RETURNING thing_id, thing_name
)
INSERT INTO thing_identifier (thing_id, identifier)
SELECT l.thing_id, t.guid
FROM list AS l
INNER JOIN thing_to_add AS t
ON l.thing_name = t.thing_name
那么，如果thing.thing_name不是唯一的，問題就更棘手了。更新兩個表thing并thing_identifier從同一個觸發器上更新thing_to_add可能會解決問題：
CREATE OR REPLACE FUNCTION after_insert_thing_to_add ()
RETURNS TRIGGER LANGUAGE sql AS
$$
WITH list AS
(
INSERT INTO thing (thing_name)
SELECT NEW.thing_name
RETURNING thing_id
)
INSERT INTO thing_identifier (thing_id, identifier)
SELECT l.thing_id, NEW.guid
FROM list AS l ;
$$
DROP TRIGGER IF EXISTS after_insert ON thing_to_add ;
CREATE TRIGGER after_insert
AFTER INSERT
ON thing_to_add
FOR EACH ROW
EXECUTE PROCEDURE after_insert_thing_to_add ();
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/350384.html
標籤：postgresql sql-insert common-table-expression sql-returning