Possible to upsert in Postgres on conflict on exactly one of 2 columns?

Question

I have a table that has two columns that are unique, and would like to upsert if the first column (or both columns) has a conflict, but do nothing if only the second column has a conflict. Is this possible?

CREATE TABLE test (
    username    VARCHAR(255) NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    status      VARCHAR(127)
);

The following works to check for conflicts on email:

INSERT INTO test(username, email)
    VALUES ('test', '[email protected]')
    ON CONFLICT(email) DO UPDATE SET status='upserted';

But I'd like to do something like this (invalid syntax below):

(INSERT INTO test(username, email)
    VALUES ('test', '[email protected]')
    ON CONFLICT(email) DO UPDATE SET status='upserted')
    ON CONFLICT DO NOTHING;

Answer 1

Yes, you can do this, but it requires some conditional trickery.

First of all, you can have only one ON CONFLICT clause, but this clause can specify multiple columns with constraints defined on them. In your case that would be ON CONFLICT (username, email). When either or both of the columns trigger a conflict, the conflict_action kicks in.

Secondly, the conflict_action clause should compare values from the candidate row for insertion (referenced by EXCLUDED) against current values and take appropriate action. DO NOTHING will in practice not be possible, but you can assign the old value to the new row so the effect is the same (but the update will happen). Not pretty, but it will look somewhat like this:

INSERT INTO test(username, email)
    VALUES ('test', '[email protected]')
    ON CONFLICT(username, email) DO UPDATE 
        SET status = CASE WHEN username != EXCLUDED.username -- only email offending
                          THEN status                        -- so "do nothing"
                          ELSE 'upserted'                    -- some other action
                     END;