problem with on conflict / do update using psql 14.4

From: Barry Kimelman <blkimelman(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: problem with on conflict / do update using psql 14.4
Date: 2022-09-23 21:28:15
Message-ID: CAMPa0rUQUqiVPQLdYSg7g8mrxyRzKf6cVjt2KXvJMnhvLcHbig@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Baldwin 2022-09-23 21:42:09 Re: problem with on conflict / do update using psql 14.4
Previous Message Mohammed falih 2022-09-23 20:34:18 Re: i added Arabic Dictionary but how I know i'm using it