Re: [GENERAL] 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 wrote: > Peter Geoghegan writes: > > On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn > 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 > 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 >
Re: [GENERAL] 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 wrote: > On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn > 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 >
Re: [GENERAL] Upsert with a partial unique index constraint violation
Peter Geoghegan writes: > On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upsert with a partial unique index constraint violation
On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upsert with a partial unique index constraint violation
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