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"?
CodePudding user response:
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
.