I am trying to run an UPSERT
query string on a postgres db –
x
insert into business_data_test
(select * from business_data_tmp)
on conflict(phone_number)
do update set
average_expense=(average_expense*expense_count + excluded.average_expense*excluded.expense_count)/(expense_count + excluded.expense_count), expense_count=(expense_count + excluded.expense_count);
I am basically trying to update the column average_expense
if there is conflicting data, but I think there is something wrong with the query as I am running into the following error –
ERROR: column reference "average_expense" is ambiguous
LINE 1: lict(phone_number) do update set average_expense=(average_ex
^
SQL state: 42702
Character: 123
I believe we have to add some table name alias somewhere but I am not sure how to fix this.
Advertisement
Answer
You need to full qualify the reference to the old values (i.e. the “non-exluded” ones). This is a bit easier if you use an alias for the target table
insert into business_data_test as tst
select *
from business_data_tmp
on conflict(phone_number)
do update
set average_expense = (tst.average_expense * tst.expense_count + excluded.average_expense * excluded.expense_count)/(tst.expense_count + excluded.expense_count),
expense_count = tst.expense_count + excluded.expense_count;