Search Postgresql Archives

Re: Upsert with a partial unique index constraint violation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Peter Geoghegan <pg@xxxxxxx> writes:
> On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn <tim.dawborn@xxxxxxxxx> wrote:
>> tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true)
>> tmp-# ON CONFLICT (a, b) WHERE d = true
>> tmp-# DO UPDATE SET c = 'four' WHERE foo.a = 1 AND foo.b = 2 AND foo.d =
>> true;
>> ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT
>> specification
>> 
>> If anyone knows what I'm doing wrong and how to get this to work, or knows
>> that this is not possible to achieve, I'm all ears.

> That should work. Are you sure you haven't spelled it "... WHERE d IS TRUE"?

It does work for me, but I think it probably only started working after
this as-yet-unreleased patch:


Author: Tom Lane <tgl@xxxxxxxxxxxxx>
Branch: master [26e66184d] 2016-05-11 16:20:23 -0400
Branch: REL9_5_STABLE [58d802410] 2016-05-11 16:20:03 -0400

    Fix assorted missing infrastructure for ON CONFLICT.
    
    subquery_planner() failed to apply expression preprocessing to the
    arbiterElems and arbiterWhere fields of an OnConflictExpr.  No doubt the
    theory was that this wasn't necessary because we don't actually try to
    execute those expressions; but that's wrong, because it results in failure
    to match to index expressions or index predicates that are changed at all
    by preprocessing.  Per bug #14132 from Reynold Smith.


The key point here being that "WHERE boolvar = true" will be simplified
to "WHERE boolvar" by preprocessing, and you don't get a match unless
that happened on both expressions.  Tim could work around this in
unpatched releases by spelling the predicate as just "d".

			regards, tom lane


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux