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@xxxxxxx> wrote:
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"?
--
Peter Geoghegan
