On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman <blkimel...@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.
