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

Thanks for your replies.

Peter Geoghegan <pg(at)bowt(dot)ie> escreveu no dia terça, 7/02/2017 às 21:27:

> On Tue, Feb 7, 2017 at 12:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Hm. I looked at infer_arbiter_indexes, which is the place where this
> > particular error is thrown, and realized that my previous assertion
> > was wrong: it *does* try to prove applicability of partial indexes
> > based on the ON CONFLICT WHERE clause.
>
> I must have misunderstood. ON CONFLICT accepts a WHERE clause as part
> of the inference specification itself entirely because that is
> sometimes useful.
>
> > So actually it should be
> > deciding that uniq_person_accounts is a usable unique index --- at
> > least, if you always have "WHERE type = 'PersonAccount'" in the
> > ON CONFLICT clause. Maybe you're sometimes leaving that out?
>
> That's the simplest answer. Tiago?
>

There is only one place where I create accounts, so the query is always the
same.

Here is an example where it gives the error (I excluded the created_at and
updated_at columns in the last e-mail, but they do exist in the table):

INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 69559,
'2017-02-03 12:09:27.259', '2017-02-03 12:09:27.259') ON CONFLICT (type,
person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at =
EXCLUDED.updated_at RETURNING *
SQL execution failed (Reason: ERROR: there is no unique or exclusion
constraint matching the ON CONFLICT specification)

>
> --
> Peter Geoghegan
>