Updating a unique constrant

From: "Peter Rahm-Coffey" <RahmCoff(at)radio1190(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Updating a unique constrant
Date: 2004-06-04 19:09:40
Message-ID: 49296.128.138.162.187.1086376180.squirrel@radio1190.colorado.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've having trouble with updating a unique index. Briefly:

CREATE TABLE playlist (
[...]
playorder numeric UNIQUE NOT NULL,
[...]
};

CREATE TABLE globals (
[...]
current_play numeric NOT NULL REFERENCES playlist(playorder),
[...]
);

INSERT INTO playlist(playorder) VALUES (1);
INSERT INTO playlist(playorder) VALUES (2);
INSERT INTO playlist(playorder) VALUES (3);
[...]
INSERT INTO playlist(playorder) VALUES (217);

UPDATE playlist SET playorder = playorder + 123 FROM globals WHERE
playorder > current_play;

If current_play is less than 94, I get uniqueness error. Since the update
is most likely to be done when playorder = 1, this is a problem. I
switched to the slower numeric to try doing insert values (1+1/123), but
then I can't do SELECT ... WHERE playorder = 1.00813008130081. (It returns
false.) Is there a way out of my problem without removing the unique index
and the foreign keys that use it?

--
Peter Rahm-Coffey

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rick Gigger 2004-06-04 19:28:07 Re: [GENERAL] [HACKERS] Slony-I goes BETA
Previous Message Joseph Shraibman 2004-06-04 18:50:29 Re: VACUUM Question