UPSERT 與 ON CONFLICT 使用 UPDATE 部分中源表中的值

鑑於：

CREATE TABLE A (
PK_A INT8 NOT NULL,
A INT8,
PRIMARY KEY (PK_A)
);

CREATE TABLE B (
PK_B INT8 NOT NULL,
B INT8,
PRIMARY KEY (PK_B)
);

這個查詢：

insert into table_b (pk_b, b) 
select pk_a,a from table_a 
on conflict (b) do update set b=a;

導致以下錯誤：

ERROR:  column "a" does not exist
LINE 1: ...elect pk_a,a from table_a on conflict (b) do update set b=a;
                                                                 ^
HINT:  There is a column named "a" in table "*SELECT*", but it cannot be referenced from this part of the query.

如何在參考內容的同時進行更新table_a？

多個問題。

您的設置，擴展：

CREATE TABLE a (
 pk_a int PRIMARY KEY 
, a int
, comment text  -- added column to make effect clear
);

CREATE TABLE b (
 pk_b int PRIMARY KEY
, b int 
, comment text
);

INSERT INTO a VALUES (1, 11, 'comment from a')
                  , (2, 22, 'comment from a');

INSERT INTO b VALUES (1, 77, 'comment from b');

這有效：

INSERT INTO b (pk_b, b, comment) 
SELECT pk_a, a, comment
FROM   a 
**ON CONFLICT (pk_b) DO UPDATE**  -- conflict is on the unique column
**SET b = excluded.b;**            -- key word "excluded", refer to target column

結果：

TABLE b;

pk_b | b  |    comment
------+----+----------------
   1 | 11 | comment from b   -- updated
   2 | 22 | comment from a   -- inserted

問題

0. 您在展示中感到困惑（如table_a@Abelisto評論）。A

使用合法的、小寫的、不帶引號的標識符有助於避免混淆。

1. 就像提到的@Ziggy一樣，ON CONFLICT僅適用於實際的唯一性或排除約束違規。手冊：

可選ON CONFLICT子句指定引發唯一違反或排除約束違反錯誤的替代操作。

因此，ON CONFLICT (b)不能工作，那裡沒有限制。ON CONFLICT (pk_b)作品。 2. 就像@Ziggy 還提到的那樣，源表名稱在該UPDATE部分中不可見。手冊：

中的SETandWHERE子句ON CONFLICT DO UPDATE可以使用表名（或別名）訪問現有行，並 使用特殊表訪問****建議插入的行excluded。

大膽強調我的。 3. 您也不能在元件中使用源表的列名UPDATE。它必須是目標行****的列名。所以你真的想要：

SET    b = excluded.b

手冊再次：

請注意，所有每行BEFORE INSERT觸發器的影響都反映在排除值中，因為這些影響可能導致該行被排除在插入之外。

在 PostgreSQL 9.5+ 中執行 upserts 時，您必須通過 alias 引用排除的數據（插入失敗的數據）excluded。此外，該on conflict選項必須引用 key:(pk_b)而不是(b). 例如。

insert into table_b (pk_b, b) 
select pk_a,a from table_a 
on conflict (pk_b) do update set b=excluded.b;

有關更多資訊，請參閱官方文件或此簡單介紹 upsert。

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