From: | Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com> |
---|---|
To: | Barry Kimelman <blkimelman(at)gmail(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: problem with on conflict / do update using psql 14.4 |
Date: | 2022-09-23 21:42:09 |
Message-ID: | CAKE1AiaMhqwdpHuUCv+vx+1j7WYGne=kDyCFvLPMRATSNCN2vg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 ?
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-09-23 21:43:04 | Re: problem with on conflict / do update using psql 14.4 |
Previous Message | Barry Kimelman | 2022-09-23 21:28:15 | problem with on conflict / do update using psql 14.4 |