-1

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.

Anurag-Sharma
  • 4,278
  • 5
  • 27
  • 42
  • 1
    I removed the `postgresql-9.3` tag as that version did not support `on conflict()` –  Mar 15 '21 at 15:08
  • Does this answer your question? [SQL column reference "id" is ambiguous](https://stackoverflow.com/questions/9821121/sql-column-reference-id-is-ambiguous) – TylerH Oct 20 '22 at 13:49

2 Answers2

2

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;
Anurag-Sharma
  • 4,278
  • 5
  • 27
  • 42
0

your query has "excluded." butno reference to what is that "excluded" is?

My suggestion, do the update with one single column value instead of the long computational logic. Make sure you get it working then add to that update logic....

Easier to debug that way.

tenet testuser1
  • 89
  • 7
  • The `excluded` part is correct, it's the other columns that are not. –  Mar 15 '21 at 15:11