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

On Fri, Sep 23, 2022 at 4:42 PM Steve Baldwin <steve(dot)baldwin(at)gmail(dot)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 <blkimelman(at)gmail(dot)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