I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set.
I've used info from this question: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null, it's quite related to what I have.
I tried to use this query, but when I run it, it errors with
Column reference 'affiliate_code' is ambiguous:
INSERT INTO accounts (id, token, affiliate_code) VALUES (value1, value2, value3) ON CONFLICT (id) DO UPDATE SET token = value2, affiliate_code = COALESCE(affiliate_code, value3);
(the real values are substituted, of course).
If I replace
affiliate_code = COALESCE(affiliate_code, value3) with
affiliate_code = value3, everything works, but not in the way I want it to work.
How can I make this work?
Here is how my table is defined:
CREATE TABLE accounts ( id VARCHAR NOT NULL UNIQUE, token VARCHAR NOT NULL, affiliate_code VARCHAR );