> 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.
