On Fri, Sep 23, 2022 at 4:43 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman <blkimelman@xxxxxxxxx> 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 messageWhich 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)David J.
Thanks for the reply. but could you be more specific about the where clause ? I am new to postgresql and ON CONFLICT
--
====================
Barry Kimelman
Winnipeg, MB, Canada
