BUG #13073: Uniqueness constraint incorrectly reports constraint violations - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      13073
Logged by:          David Portas
Email address:      [email protected]
PostgreSQL version: 9.1.13
Operating system:   Debian Linux
Description:

Repro script:
CREATE TABLE tbl1 (x INT NOT NULL PRIMARY KEY);
INSERT INTO tbl1 VALUES (1),(2);
UPDATE tbl1 SET x = x +1;

Result:

ERROR:  duplicate key value violates unique constraint "tbl1_pkey"
DETAIL:  Key (x)=(2) already exists.

Expected result: UPDATE should succeed because the constraint is not
violated. The constraint should be evaluated against the complete resulting
table as per documentation: "unique with respect to all the rows in the
table"[1].

The expected result can be seen if the insertion order of the INSERTs is
reversed:
CREATE TABLE tbl1 (x INT NOT NULL PRIMARY KEY);
INSERT INTO tbl1 VALUES (2),(1);
UPDATE tbl1 SET x = x +1;

Result: UPDATE succeeds. This is expected but is inconsistent with the
previous result even though the two UPDATEs are logically equivalent.

The same effect is seen if UNIQUE is specified instead of PRIMARY KEY.

[1]http://www.postgresql.org/docs/9.1/static/ddl-constraints.html