From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Dmitry Afanasiev <KOT(at)MATPOCKuH(dot)Ru> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3910: Incorrect behavior of UPDATE statement on tables with constraints UNIQUE/PRIMARY KEY |
Date: | 2008-01-29 15:59:20 |
Message-ID: | 20080129155920.GJ21175@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)MATPOCKuH(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2008-01-29 16:06:38 | Re: BUG #3897: plJava dll still doesn't load for 8.3RC2 |
Previous Message | David JL Gradwell | 2008-01-29 15:33:54 | BUG #3897: plJava dll still doesn't load for 8.3RC2 |