Re: Upsert with a partial unique index constraint violation

Correct, there was no typo there. All of the psql examples I included were
copy-pasted out of a clean psql 9.5 session on a clean psql 9.5 database
(64 bit linux).

$ createdb tmp
$ psql --quiet tmp
tmp=# select version();
version
-------------------------------------------------------------------------------------------------
PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)
tmp=# CREATE TABLE foo (a INT NOT NULL, b INT NOT NULL, c TEXT, d BOOLEAN
DEFAULT false);
tmp=# CREATE UNIQUE INDEX foo_unique_true ON foo (a, b) WHERE d = true;
tmp=# \d+ foo
Table "public.foo"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+---------------+----------+--------------+-------------
a | integer | not null | plain | |
b | integer | not null | plain | |
c | text | | extended | |
d | boolean | default false | plain | |
Indexes:
"foo_unique_true" UNIQUE, btree (a, b) WHERE d = true

tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'one', false);
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'two', false);
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'three', true);
tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true) ON CONFLICT
(a, b) WHERE d = true 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

On 12 July 2016 at 13:43, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> 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"?
>
> --
> Peter Geoghegan
>