pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddeferrable on unique
2+ messages / 2 participants
[nested] [flat]
* deferrable on unique
@ 2005-11-22 16:15 chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: chester c young @ 2005-11-22 16:15 UTC (permalink / raw)
To: pgsql-sql
table t1:
id integer primary key,
seq integer not null unique
the seq is for ordering the rows as the user likes. however, if the
rows are moved around, eg
begin
update t1 set seq=4 where id=5
update t1 set seq=5 where id=4
end
will bomb because the first update has two rows of seq=4 (although
correct after the transaction).
I thought "deferrable initally deferred" would fix this, but the phrase
is not allowed on unique.
is this correct? any ideas?
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: deferrable on unique
@ 2005-11-22 16:27 John McCawley <nospam@hardgeus.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: John McCawley @ 2005-11-22 16:27 UTC (permalink / raw)
To: pgsql-sql
It's a low-tech solution but you could:
begin
update t1 set seq=-1 where id=5
update t1 set seq=5 where id=4
update t1 set seq=4 where id=-1
end
This is assuming that you don't naturally have -1 as a valid value of
that column.
chester c young wrote:
>table t1:
> id integer primary key,
> seq integer not null unique
>
>the seq is for ordering the rows as the user likes. however, if the
>rows are moved around, eg
> begin
> update t1 set seq=4 where id=5
> update t1 set seq=5 where id=4
> end
>will bomb because the first update has two rows of seq=4 (although
>correct after the transaction).
>
>I thought "deferrable initally deferred" would fix this, but the phrase
>is not allowed on unique.
>
>is this correct? any ideas?
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-11-22 16:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-22 16:15 deferrable on unique chester c young <chestercyoung@yahoo.com>
2005-11-22 16:27 ` John McCawley <nospam@hardgeus.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox