Re: [BUGS] BUG #14526: no unique or exclusion constraint matching theON CONFLICT
| От | Tiago Babo |
|---|---|
| Тема | Re: [BUGS] BUG #14526: no unique or exclusion constraint matching theON CONFLICT |
| Дата | |
| Msg-id | CAPsQ5r-2W=cJmJsQfd1qAY=kFuXDnR668iQvL8kTQSCkmoU76w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [BUGS] BUG #14526: no unique or exclusion constraint matching theON CONFLICT (Peter Geoghegan <pg@bowt.ie>) |
| Список | pgsql-bugs |
Thanks for your replies.
Peter Geoghegan <pg@bowt.ie> escreveu no dia terça, 7/02/2017 às 21:27:
On Tue, Feb 7, 2017 at 12:44 PM, Tom Lane <tgl@sss.pgh.pa.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
В списке pgsql-bugs по дате отправления: