Appending array during Postgresql upsert & getting ambiguous column error

Question

When trying to do a query like below:

INSERT INTO employee_channels (employee_id, channels)
        VALUES ('46356699-bed1-4ec4-9ac1-76f124b32184', '{a159d680-2f2e-4ba7-9498-484271ad0834}')
        ON CONFLICT (employee_id)
        DO UPDATE SET channels = array_append(channels, 'a159d680-2f2e-4ba7-9498-484271ad0834')
        WHERE employee_id = '46356699-bed1-4ec4-9ac1-76f124b32184'
        AND NOT lower(channels::text)::text[] @>  ARRAY['a159d680-2f2e-4ba7-9498-484271ad0834'];

I get the following error

[42702] ERROR: column reference "channels" is ambiguous Position: 245

The specific reference to channels it's referring to is the 'channels' inside array_append.

channels is a CITEXT[] data type

Answer 1

You may need to specify the EXCLUDED table in your set statement.

SET channels = array_append(EXCLUDED.channels, 'a159d680-2f2e-4ba7-9498-484271ad0834')

When using the ON CONFLICT DO UPDATE clause the values that aren't inserted because of the conflict are stored in the EXCLUDED table. Its an ephemeral table you don't have to actually make, the way NEW and OLD are in triggers.

From the PostgreSQL Manual:

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.

Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, since those effects may have contributed to the row being excluded from insertion.