Search code examples
postgresqlupsert

Errors with INSERT ... ON CONFLICT

I have this code below

INSERT INTO table1 (code, value)
VALUES ('code1', 'value1')
ON CONFLICT (code, value)
WHERE code = 'code1'
  DO
    UPDATE SET value = 'value1';

When I try running this code I get

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

So I tried adding a "where" clause in the "update" part, but then I get the error

ERROR: column reference "code" is ambiguous

How to correctly create an "insert" with "do update"?

Solution

  • The error in the WHERE condition is thrown because it is not clear if you mean table1.code (the old version) or EXCLUDED.code (the new version). You have to be explicit there.

    But that WHERE condition won't do anything to prevent the original problem, namely that there is no unique constraint or index on (code, value). You have to create such a constraint or index if you want to use INSERT ... ON CONFLICT.