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