Re: [BUGS] BUG #14526: no unique or exclusion constraint matching theON 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.
Whereis 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@sss.pgh.pa.us> wrote:
>
> Tiago Babo <tiago.babo@gmail.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



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs