Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Sat, Sep 24, 2022 at 9:47 AM Christophe Pettus <xof@xxxxxxxxxxxx> wrote:


> On Sep 24, 2022, at 07:29, Barry Kimelman <blkimelman@xxxxxxxxx> wrote:
>
> CREATE UNIQUE INDEX my_companies_company_name_unique ON my_companies(company_name) WHERE delete_timestamp IS NULL;

The issue here is that the unique index is partial (it has a WHERE clause).  In order to use that as an arbiter, you need include a WHERE clause in the ON CONFLICT clause that matches the one on the index.

I believe that something like:

insert into my_companies (second_id,string_company_id,company_name,person_name)
values (1,'66','widgets','seller-toto')
on conflict (company_name) where delete_timestamp IS NULL do update set company_name = concat(company_name,'++',string_company_id)

... will work.  Note that if you do an insert with a duplicate "company_name", but "delete_timestamp" not null, it *won't* treat that as a conflict and won't run the ON CONFLICT action; it'll just insert the row.


Thanks for the response. When I ran the INSERT with your suggested change I got an error message telling me
"column reference 'company_name' is ambiguous"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux