CODEWITHSUNDEEP
postgresqlsql-insertupsert
jhamm
jhamm

Reputation: 25032

Why am I getting an ambiguous column in my PG on conflict insert?

Here is my query: 

insert into zoning_algorithm_value (algorithm_value_id, value, zoning_id)
values (
           61,
           21,
           7321
       )
on conflict(algorithm_value_id)
    DO
        update set value = 21 where zoning_id = 7321 and algorithm_value_id = 61;

I am only referencing one table. Here is the error I am getting.

[42702] ERROR: column reference "zoning_id" is ambiguous

How can it be ambiguous when there is only one table and one column with that name? How do I make this upsert work?

Upvotes: 0

Views: 61

Answers (1)

Nick
Nick

Reputation: 7451

You either need to specify the table name or EXCLUDED to precede the fields in the WHERE clause.

For example, if you only want to update value when the "new" zoning_id and algorithm_value_id are 7321 and 61, respectively:

insert into zoning_algorithm_value (algorithm_value_id, value, zoning_id)
values (61, 21, 7321)
on conflict(algorithm_value_id)
    DO
        update set value = 21 where EXCLUDED.zoning_id = 7321 and EXCLUDED.algorithm_value_id = 61;

If you instead want the WHERE to reference the "existing" record values, use the table name.

Upvotes: 1

Related Questions