Re: Upsert with a partial unique index constraint violation

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Peter Geoghegan <pg(at)bowt(dot)ie> writes:
> > On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn <tim(dot)dawborn(at)gmail(dot)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 <tgl(at)sss(dot)pgh(dot)pa(dot)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
>