Re: set-level update fails with unique constraint violation

[David Fetter <david(at)fetter(dot)org>]

On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz wrote:
> Hello,
> 
> this fails with "duplicate key value":
> 
>     CREATE TABLE x (
>       i INT NOT NULL UNIQUE
>     );
>     INSERT INTO x (i) VALUES (1), (2), (3);
>     UPDATE x SET i = i + 1;
> 
> are there any plans to make this work?

This will work in 8.5:

CREATE TABLE x (
    i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO x (i) VALUES (1), (2), (3);
UPDATE x SET i = i + 1;

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
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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