Thread: BUG #3910: Incorrect behavior of UPDATE statement on tables with constraints UNIQUE/PRIMARY KEY

The following bug has been logged online:

Bug reference:      3910
Logged by:          Dmitry Afanasiev
Email address:      KOT@MATPOCKuH.Ru
PostgreSQL version: 7.4.19
Operating system:   FreeBSD 7.0 / 5.5
Description:        Incorrect behavior of UPDATE statement on tables with
constraints UNIQUE/PRIMARY KEY
Details:

Constraints must be checked AFTER updating ALL of rows, but really after
every row.
For illustrate try this simple sql script:
CREATE TABLE n(n INTEGER PRIMARY KEY);
INSERT INTO n VALUES(1);
INSERT INTO n VALUES(2);
INSERT INTO n VALUES(3);
UPDATE n SET n = n + 1;
SELECT * FROM n;
DROP TABLE n;

On UPDATE psql says about violation UNIQUE constraint and does't update
anything.
For example, in Oracle all works propertly, and result of SELECT is:
SQL> select * from n;

         N
----------
         2
         3
         4

Dmitry Afanasiev wrote:

> Constraints must be checked AFTER updating ALL of rows, but really after
> every row.
> For illustrate try this simple sql script:
> CREATE TABLE n(n INTEGER PRIMARY KEY);
> INSERT INTO n VALUES(1);
> INSERT INTO n VALUES(2);
> INSERT INTO n VALUES(3);
> UPDATE n SET n = n + 1;

Yes.  This is a known problem.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, Jan 29, 2008 at 09:45:28AM +0000, Dmitry Afanasiev wrote:
>
> The following bug has been logged online:
>
> Bug reference:      3910
> Logged by:          Dmitry Afanasiev
> Email address:      KOT@MATPOCKuH.Ru
> PostgreSQL version: 7.4.19
> Operating system:   FreeBSD 7.0 / 5.5
> Description:        Incorrect behavior of UPDATE statement on tables with
> constraints UNIQUE/PRIMARY KEY
> Details:
>
> Constraints must be checked AFTER updating ALL of rows, but really after
> every row.
> For illustrate try this simple sql script:
> CREATE TABLE n(n INTEGER PRIMARY KEY);
> INSERT INTO n VALUES(1);
> INSERT INTO n VALUES(2);
> INSERT INTO n VALUES(3);
> UPDATE n SET n = n + 1;
> SELECT * FROM n;
> DROP TABLE n;
>
> On UPDATE psql says about violation UNIQUE constraint and does't update
> anything.
> For example, in Oracle all works propertly, and result of SELECT is:
> SQL> select * from n;
>
>          N
> ----------
>          2
>          3
>          4

You can get around this by doing:

UPDATE t
SET n=t.n+1
FROM (
    SELECT n FROM t ORDER BY n DESC
) t1
WHERE t1.n=t.n;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate