代码之家 › 专栏 › 技术社区 › metersk

PostgreSQL 11.6关于约束冲突未触发更新,但应激活约束

upsert insert postgresql

0

metersk · 技术社区 · 6 年前

我有一个这样创建的表:

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 2) 我知道数据在变化,因为我把每件事都插入到历史表中,所以我看到数据/时间戳在变化 3) 不写入新行

change.payer 即使我尝试向上插入新数据,但新行也会写入历史记录表。

1 回复 | 直到 6 年前

1

2

metersk 6 年前

结果发现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
        ;