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 <p...@bowt.ie> wrote: > 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"? > > -- > Peter Geoghegan >