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
