Awesome. Thanks, Tom. Glad to see this issue has been patched upstream.
I'll use the alternative syntax in the meantime.
Cheers,
Tim
On 13 July 2016 at 01:03, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
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
