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

Ups, I used the wrong database. I was experimenting with it a bit and didn’t noticed I was copying from the wrong one. Where is the correct version (and the one that is giving me those “random” errors):

Column | Type | Modifiers | Storage | Stats target | Description
----------------------------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('accounts_id_seq'::regclass) | plain | |
type | character varying | | extended | |
identifier | character varying | | extended | |
person_id | integer | | plain | |
business_id | integer | | plain | |
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 (type, person_id) WHERE type::text = 'PersonAccount'::text
"index_accounts_on_business_id" btree (business_id)
"index_accounts_on_person_id" btree (person_id)
Foreign-key constraints:
"fk_rails_156241f05a" FOREIGN KEY (business_id) REFERENCES businesses(id)
"fk_rails_777d10a224" FOREIGN KEY (person_id) REFERENCES persons(id)

> On 7 Feb 2017, at 18:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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