PostgreSQL 11.6 ON CONFLICT ON CONSTRAINT not triggering update, but constraint should be activated

Question

I have a table created like so:

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
);

I upsert data to it, like so:

        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
        ;

For some reason when I write new rows with the same account_id, no updates happen. I'm not sure exactly what is happening, 1) i get no error 2) i know the data is changing because i insert every thing into a history table so i see data/timestamps change 3) no new rows are written

essentially, nothing changes in change.payer even as i attempt to upsert new data, but new rows are written to my history table.

`no updates happen` - to be precise: updates *do* happen, but columns are set to their current values. You still accrue most of the costs, triggers are still fired, etc. See https://stackoverflow.com/a/42217872/939860 (You found the right fix using `EXCLUDED`.) — Erwin Brandstetter, Apr 22 '20 at 23:00

score 2 · Answer 1 · answered Apr 22 '20 at 22:40

It turns out the right side of the set clause is not the table, you're inserting into, but the data you want to insert. also, you must use a keyword EXCLUDED

so the correct upsert query for my use case would be:

        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
        ;

PostgreSQL 11.6 ON CONFLICT ON CONSTRAINT not triggering update, but constraint should be activated

1 Answers1