Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 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)


David J.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux