Column name is ambiguous on UPSERT action

I am trying to run an UPSERT query string on a postgres db –

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.

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;