From: Franklin Haut <franklin.haut@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Update PK Violation
Date: Tue, 15 Jan 2008 18:03:49 -0300
Message-ID: <478D1FB5.2070003@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franklin.haut@gmail.com
Subject: Re: Update PK Violation
In-Reply-To: <478D1FB5.2070003@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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