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

Gregory Arenius · Accepted Answer · 2019-01-19 03:48:04Z

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.

Collectives™ on Stack Overflow

Appending array during Postgresql upsert & getting ambiguous column error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related