'Column reference is ambiguous' when upserting element into table

Question

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
);

Answer 1

From the docs,

conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table. SELECT privilege is required on any column in the target table where corresponding excluded columns are read.

So instead, try this per ypercubeᵀᴹ

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(accounts.affiliate_code, excluded.affiliate_code);

Answer 2

This answer helped me solve a slightly different ambiguous column problem. I have a table where we do daily roll-ups into the same table multiple times per day. We need to re-calculate the daily roll-up on an hourly basis, which means we're updating the same row 24 times per day.

Paraphrasing the above:

INSERT INTO accounts as act 
    (affiliate_code, datum)
SELECT  affiliate_code, datum
FROM
    -- complex multi-table join with "datum" in more than one table.
ON CONFLICT (affiliate_code) DO
    UPDATE SET 
        -- Update knows "datum" is the target row
        datum = excluded.datum
    WHERE
        -- Here update needs to be told which of the multiple tables
        -- with datum to use. 
        act.datum != excluded.datum;