Upsert with a partial unique index constraint violation

From: Tim Dawborn <tim(dot)dawborn(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Upsert with a partial unique index constraint violation
Date: 2016-07-11 07:06:56
Message-ID: CAN9Kr4C6An92CpWg=yLOjat9pcFM0AMF1e+y5TszpRyx4cH2ew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I'm struggling to work out the correct way to use a partial unique index in
an upsert context.

Here's the context: I'm creating a partial unique index on a table:

tmp=# CREATE TABLE foo (a INT NOT NULL, b int NOT NULL, c TEXT, d BOOLEAN
DEFAULT false);
CREATE TABLE
tmp=# CREATE UNIQUE INDEX foo_unique_true ON foo (a, b) WHERE d = true;
CREATE INDEX
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'one', false);
INSERT 0 1
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'two', false);
INSERT 0 1
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'three', true);
INSERT 0 1
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true);
ERROR: duplicate key value violates unique constraint "foo_unique_true"

This is all working as expected.

What I want to do is alter my INSERT to be an upsert for the violation of
the index constraint foo_unique_true. However, I cannot work out the syntax
for doing this as the partial index is not a constraint, and my
interpretation of *index_expression* and *index_predicate* in the
grammar[1] don't seem to be working:

First, trying to upsert as if foo_unique_true was a constraint:

tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true)
tmp-# ON CONFLICT ON CONSTRAINT "foo_unique_true"
tmp-# DO UPDATE SET c = 'four' WHERE foo.a = 1 AND foo.b = 2 AND foo.d =
true;
ERROR: constraint "foo_unique_true" for table "foo" does not exist

Second, trying with *index_expression* and *index_predicate*:

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.

Cheers,
Tim

[1] https://www.postgresql.org/docs/9.5/static/sql-insert.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message arnaud gaboury 2016-07-11 08:00:50 Re: error when upgrading 9.4 to 9.5 manually
Previous Message Christian Castelli 2016-07-11 06:51:40 Re: RHEL 7