On Fri, Sep 23, 2022 at 4:43 PM David G. Johnston < david.g.johns...@gmail.com> wrote:
> 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. > > 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
