469,090 Members | 1,100 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Updating a unique constrant

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
4 1456
On Fri, Jun 04, 2004 at 13:09:40 -0600,
Peter Rahm-Coffey <Ra******@radio1190.org> wrote:

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?


Uniqueness constraints can't be deferred. One common strategy is to change
all of the values to values in a set disjoint from what they are now union
with what they will be. (Commonly people make the negative of their
current value.) And then change them to the correct new values.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2
On Fri, Jun 04, 2004 at 13:09:40 -0600,
Peter Rahm-Coffey <Ra******@radio1190.org> wrote:

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?


Uniqueness constraints can't be deferred. One common strategy is to change
all of the values to values in a set disjoint from what they are now union
with what they will be. (Commonly people make the negative of their
current value.) And then change them to the correct new values.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3
On Sun, 6 Jun 2004, Bruno Wolff III wrote:
Uniqueness constraints can't be deferred. One common strategy is to change
all of the values to values in a set disjoint from what they are now union
with what they will be. (Commonly people make the negative of their
current value.) And then change them to the correct new values.


Thanks for the advice. I already screwed up my test database when tring to
make it work without the unique index.
--
Peter of the Norse
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4
On Sun, 6 Jun 2004, Bruno Wolff III wrote:
Uniqueness constraints can't be deferred. One common strategy is to change
all of the values to values in a set disjoint from what they are now union
with what they will be. (Commonly people make the negative of their
current value.) And then change them to the correct new values.


Thanks for the advice. I already screwed up my test database when tring to
make it work without the unique index.
--
Peter of the Norse
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Eric Kiernan | last post: by
1 post views Thread by Marius Kaizerman | last post: by
reply views Thread by Peter Rahm-Coffey | last post: by
1 post views Thread by Robert Fitzpatrick | last post: by
33 posts views Thread by bill | last post: by
2 posts views Thread by UKuser | last post: by
10 posts views Thread by chimambo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.