Re: unique constraint when updating tables

am Tue, dem 22.08.2006, um 8:39:00 +0800 mailte gao iqiang folgendes:
> Hello,
> I met the following problem when i am using PostgreSQL as a backend
> database:
> I have a table "tabrel(a int primary key, b int)", and now there are for
> example 100 records with column 'a' be from 1 to 100. When i'm going to add one

Why do you want to do this?

> to each 'a' and intended to get 'a' varing from 2 to 101, i use SQL query as
> "update tabrel set a=a+1" , but i got " ERROR: duplicate key violates unique
> constraint tabrel_a_key".

You can do something like this:

test=# create table foo (id int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
test=# insert into foo values(1);
INSERT 0 1
test=# insert into foo values(2);
INSERT 0 1
test=# insert into foo values(3);
INSERT 0 1
test=# update foo set id = id+1;
ERROR: duplicate key violates unique constraint "foo_pkey"
test=# begin;
BEGIN
test=# update foo set id = id+100;
UPDATE 3
test=# update foo set id = id-99;
UPDATE 3
test=# commit;
COMMIT
test=# select * from foo;
id
----
2
3
4
(3 rows)

Other solution:

test=# begin;
BEGIN
test=# alter table foo drop constraint foo_pkey;
ALTER TABLE
test=# update foo set id = id+1;
UPDATE 3
test=# alter table foo add primary key (id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
ALTER TABLE
test=# commit;
COMMIT

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net