Message-ID: <438346EB.2000809@hardgeus.com>
Date: Tue, 22 Nov 2005 10:27:23 -0600
From: John McCawley <nospam@hardgeus.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716)
MIME-Version: 1.0
To: "Pgsql-sql@postgresql.org" <Pgsql-sql@postgresql.org>
Subject: Re: deferrable on unique
References: <20051122161524.10090.qmail@web54309.mail.yahoo.com>
In-Reply-To: <20051122161524.10090.qmail@web54309.mail.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>  
>