Search Postgresql Archives

Re: unique constraint when updating tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux