From: | Barry Kimelman <blkimelman(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: problem with on conflict / do update using psql 14.4 |
Date: | 2022-09-24 15:29:11 |
Message-ID: | CAMPa0rX0bLAJGh14X=g+LL5VoX1_16-pbFPeVFmKJKyv=Kz3bA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Sep 24, 2022 at 9:47 AM Christophe Pettus <xof(at)thebuild(dot)com> wrote:
>
>
> > On Sep 24, 2022, at 07:29, Barry Kimelman <blkimelman(at)gmail(dot)com> 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"
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2022-09-24 15:43:22 | Re: problem with on conflict / do update using psql 14.4 |
Previous Message | Christophe Pettus | 2022-09-24 14:46:24 | Re: problem with on conflict / do update using psql 14.4 |