PostgreSQL 11.6 ON CONSTRAINT ON constraint未触发更新，但应激活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
        ;