Thread: BUG #13675: Problem statement “INSERT ON CONFLICT WHERE"

The following bug has been logged on the website:

Bug reference:      13675
Logged by:          nmgjinan
Email address:      nmgjinan@126.com
PostgreSQL version: 9.5beta1
Operating system:   CentOS release 5.6  32bit
Description:

Hi,I'm testing pg9.5 beta1, encounter a problem, detailed description:

postgres=# create table distributors(did int primary key,dname
varchar,is_active boolean);
CREATE TABLE
postgres=# insert into distributors values(1,'hyl',false),(2,'aaa',true);
INSERT 0 2
postgres=# select * from distributors ;
 did | dname | is_active
-----+-------+-----------
   1 | hyl   | f
   2 | aaa   | t
(2 rows)

postgres=# insert into distributors values (2,'bbb')
postgres-# on conflict (did) where is_active do update set
dname=EXCLUDED.dname;
INSERT 0 1
postgres=# select * from distributors ;
 did | dname | is_active
-----+-------+-----------
   1 | hyl   | f
   2 | bbb   | t
(2 rows)

postgres=# insert into distributors values (1,'hgdb')
postgres-# on conflict (did) where is_active do update set
dname=EXCLUDED.dname;
INSERT 0 1
postgres=# select * from distributors ;
 did | dname | is_active
-----+-------+-----------
   2 | bbb   | t
   1 | hgdb  | f
(2 rows)

postgres=#

Why, when the where condition is true or false, the fields are change?

On 2015-10-13 01:42:09 +0000, nmgjinan@126.com wrote:
> postgres=# insert into distributors values (1,'hgdb')
> postgres-# on conflict (did) where is_active do update set
> dname=EXCLUDED.dname;
> INSERT 0 1
> postgres=# select * from distributors ;
>  did | dname | is_active
> -----+-------+-----------
>    2 | bbb   | t
>    1 | hgdb  | f
> (2 rows)
>
> postgres=#
>
> Why, when the where condition is true or false, the fields are change?

The WHERE above is parsed as parsed as the ON CONFLICT clause - where
it's just for specifying partial indexes and such. Put the WHERE after
the DO UPDATE and you'll be good.

Greetings,

Andres Freund