12

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', 'test@test.test')
    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', 'test@test.test')
    ON CONFLICT(email) DO UPDATE SET status='upserted')
    ON CONFLICT DO NOTHING;
jhhayashi
  • 361
  • 4
  • 14
  • Possible duplicate of [Use multiple conflict\_target in ON CONFLICT clause](http://stackoverflow.com/questions/35888012/use-multiple-conflict-target-in-on-conflict-clause) – e4c5 Nov 13 '16 at 10:07
  • @e4c5 This question is about conditionally processing multiple possible conflicts. The question you refer to is simply about multiple constraints. – Patrick Nov 13 '16 at 10:18

1 Answers1

7

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', 'test@test.test')
    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;
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • 2
    Doesn't adding UNIQUE to two columns create one single unique index on the pair, so it would only be caught if the pair were unique and not just one or the other? – swade Aug 02 '19 at 21:54
  • I've tested this and it doesn't seem to work. I'm getting a UniqueViolationError unless the columns specified are an *exact* match for the index columns (not merely a superset). – Hans Brende Oct 08 '21 at 17:18