Re: problem with on conflict / do update using psql 14.4

On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman <blkimelman@gmail.com> wrote:

CREATE UNIQUE INDEX my_companies_id_unique ON my_companies(string_company_id) WHERE delete_timestamp IS NULL;
CREATE UNIQUE INDEX my_companies_company_name_unique ON my_companies(company_name) WHERE delete_timestamp IS NULL;

I issued the following sql insert in an attempt to use "on conflict" to update the
duplicate column name

insert into my_companies (second_id,string_company_id,company_name,person_name)
values (1,'66','widgets','seller-toto')
on conflict (company_name) do update set company_name = concat(company_name,'++',string_company_id);


when I ran my sql statement I received the following error message

bkimelman=# \i insert-companies-1c.sql
psql:insert-companies-1c.sql:3: ERROR:  column reference "company_name" is ambiguous
LINE 3: ...company_name) do update set company_name = concat(company_na...

I tried fully qualifying the column names in the concat() function call,
but all that did was get me a different error message

Which is the more interesting one since the ambiguous column name error you did show has been resolved...

What would be the proper format for the "on conflict" clause ?

I think that since your index is partial you need to modify your insert command's on conflict clause to include a matching where clause.  (WHERE index_predicate)

https://www.postgresql.org/docs/current/sql-insert.html

David J.