On Sat, Sep 24, 2022 at 9:47 AM Christophe Pettus <x...@thebuild.com> wrote:
> > > > On Sep 24, 2022, at 07:29, Barry Kimelman <blkimel...@gmail.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"
