Re: BUG #14526: no unique or exclusion constraint matching the ON CONFLICT

Tiago Babo <tiago(dot)babo(at)gmail(dot)com> writes:
> Indexes:
> "accounts_pkey" PRIMARY KEY, btree (id)
> "index_accounts_on_type_and_identifier" UNIQUE, btree (type, identifier)
> "uniq_bank_accounts" UNIQUE, btree (type, identifier) WHERE type::text = 'BankAccount'::text
> "uniq_business_accounts" UNIQUE, btree (type, business_id) WHERE type::text = 'BusinessAccount'::text
> "uniq_person_accounts" UNIQUE, btree (person_id) WHERE type::text = 'PersonAccount'::text
> "index_accounts_on_business_id" btree (business_id)
> "index_accounts_on_person_id" btree (person_id)

So according to that, you *don't* have a unique index over (type, person_id).
(A sufficiently clever person might realize that the partial index on
person_id would serve in this instance, but I do not expect that Postgres
would figure that out.)

That makes the question less about why it fails and more about why it
seems to sometimes work. It shouldn't, at least not with this set of
indexes and this query.

regards, tom lane