c# - 获取信息在 Postgres UPSERT 查询期间发生了哪些变化

Question

在 PostgreSQL 上的 UPSERT 查询期间，表的哪些条目已更改（插入或更新）并不明显。

询问：

首先为businessKey添加一个唯一约束：

alter table public."test_table" add constraint "bk_constraint" unique ("businessKey");

插入 ... 冲突中 ... 更新

INSERT INTO public."test_table" ("first_name","last_name","role", "businessKey") 
SELECT 
UNNEST(ARRAY['Jon','Anna','Bernhard']),
UNNEST(ARRAY['Doe','Miller','Hill']),
UNNEST(ARRAY['team member','team member','project lead']),
UNNEST(ARRAY['Jon JD','Anna AM','Bernhard AH'])
ON CONFLICT ("businessKey") DO UPDATE 
SET "first_name"=EXCLUDED."first_name","last_name"=EXCLUDED."last_name""role"=EXCLUDED."role","businessKey"=EXCLUDED."businessKey";

现在我想知道插入或更新了哪些条目以供进一步处理。我怎样才能得到这些信息？

标签： c#.netpostgresqlupsert

Answer 1

只需添加一条更新语句来记录此信息：

...
ON CONFLICT ("businessKey") DO UPDATE 
SET is_updated = true,  
 "first_name"=EXCLUDED."first_name",
 "last_name"=EXCLUDED."last_name"...