DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding;
	b=oMvuQfrfJ6Dev5UxlKpgq5Muo5NcgQNWx1hN6daU7fU/Car0/+8ltCxdDrEqzHPlW00zlT2XkPMo7sgfI1fg3he/kufWuUaxNSmzzLYyUHwS/hOC+U8SFyVYvIZbwzYXlFulkt+i/dYSqDOxOvxnKPTNj3sMnWKZV/ssNb2SoM4=
Message-ID: <478D1FB5.2070003@gmail.com>
Date: Tue, 15 Jan 2008 18:03:49 -0300
From: Franklin Haut <franklin.haut@gmail.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Update PK Violation
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi all,

i have a problem with one update sentence sql.

example to produce:

create table temp (num integer primary key, name varchar(20));

insert into temp values (1, 'THE');
insert into temp values (2, 'BOOK');
insert into temp values (3, 'IS');
insert into temp values (4, 'ON');
insert into temp values (5, 'THE');
insert into temp values (6, 'RED');
insert into temp values (7, 'TABLE');

-- now i need insert new row at position 4, for this i need increase the 
field 'num' 4 to 5, 5 to 6, 6 to 7 and 7 to 8
update temp set num = num + 1  where num > 5;
--  but this give an key violation error, because the postgresql try 
change row 4 to 5 and the 5 exist.

-- to contornate the situation i have make
update temp set num = 8 where num = 7;
update temp set num = 7 where num = 6;
update temp set num = 6 where num = 5;
update temp set num = 5 where num = 4;

-- so then i can insert the row...
insert into temp values (5, 'NOT');

-- and obtain the result what i need.
select num, name from temp
---result ------
   1, 'THE'
   2, 'BOOK'
   3, 'IS'
   4, 'NOT'
   5, 'ON'
   6, 'THE'
   7, 'RED'
   8, 'TABLE'


--- the big question is...  have one way to the command  (update temp 
set num = num + 1  where num > 5;) works ?
-- consideration, i can´t delete the primary key
-- using PG 8.2 / Windows
 

Thanks for all


Franklin