On Fri, Sep 23, 2022 at 4:42 PM Steve Baldwin <steve.bald...@gmail.com> wrote:
> You need to prefix the rhs column(s) with 'excluded.'. For example: > > on conflict (company_name) do update set company_name = > concat(excluded.company_name,'++',excluded.string_company_id); > > On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelman <blkimel...@gmail.com> > wrote: > >> >> I have not been able to get the "ON CONFLICT" clause to work >> I am using psql 14.4 on ubuntu >> >> Given the following table definition >> >> CREATE TABLE my_companies ( >> company_id SERIAL NOT NULL PRIMARY KEY, >> second_id INTEGER NOT NULL REFERENCES big_list(second_id), >> string_company_id TEXT NOT NULL, >> company_name TEXT NOT NULL, >> person_name TEXT NOT NULL >> ) >> INHERITS ( _audit_table, _delete_table ); >> >> and the following constraints >> >> 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); >> >> In this example a record with a company name of 'widgets' already exists >> and will >> result in an constraint violation >> >> 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 >> >> What would be the proper format for the "on conflict" clause ? >> > Thanks for the response. But when I "prefixed" the columns with "excluded" I got a different error message ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
