PostgreSQL 11.6 ON CONFLICT ON CONSTRAINT未触发更新，但应激活约束

Question

我创建了一个这样的表：

create table change.payer
(
account_id text not null
constraint payer_account_id_pk
primary key,
entity_identifier text,
entity_type text,
name text,
contact_information jsonb,
etin text,
document_fetched_at timestamp,
created_at timestamp default CURRENT_TIMESTAMP not null
);

我向它追加数据，就像这样：

INSERT INTO change.payer (
"account_id", "entity_identifier", "entity_type", "name",
"contact_information", "etin", "document_fetched_at"
)
VALUES (
%(account_id)s, %(entity_identifier)s, %(entity_type)s, %(name)s,
%(contact_information)s, %(etin)s, %(document_fetched_at)s
)
ON CONFLICT ON CONSTRAINT payer_account_id_pk
DO UPDATE SET
entity_identifier = change.payer.entity_identifier,
entity_type = change.payer.entity_type,
name = change.payer.name,
contact_information = change.payer.contact_information,
etin = change.payer.etin,
document_fetched_at = change.payer.document_fetched_at
;

出于某种原因，当我用相同的account_id写入新行时，不会发生更新。我不确定到底发生了什么，1( 我没有错2( 我知道数据在变化，因为我把所有的东西都插入到历史表中，这样我就能看到数据/时间戳的变化3( 没有写入新行

本质上，即使我尝试追加新数据，change.payer中也没有任何变化，但新行会写入到我的历史表中。

Answer 1

原来set子句的右侧不是要插入的表，而是要插入的数据。此外，您必须使用关键字EXCLUDED

因此，对于我的用例，正确的追加销售查询应该是：

INSERT INTO change.payer (
"account_id", "entity_identifier", "entity_type", "name",
"contact_information", "etin", "document_fetched_at"
)
VALUES (
%(account_id)s, %(entity_identifier)s, %(entity_type)s, %(name)s,
%(contact_information)s, %(etin)s, %(document_fetched_at)s
)
ON CONFLICT ON CONSTRAINT payer_account_id_pk
DO UPDATE SET
entity_identifier = EXCLUDED.entity_identifier,
entity_type = EXCLUDED.entity_type,
name = EXCLUDED.name,
contact_information = EXCLUDED.contact_information,
etin = EXCLUDED.etin,
document_fetched_at = EXCLUDED.document_fetched_at
;