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 <t...@sss.pgh.pa.us> wrote: > Peter Geoghegan <p...@bowt.ie> writes: > > On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn <tim.dawb...@gmail.com> > 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 <t...@sss.pgh.pa.us> > 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 >
