From: | "gao iqiang" <iqianggao(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | unique constraint when updating tables |
Date: | 2006-08-22 00:39:00 |
Message-ID: | 5323452d0608211739l6ff0145btfc1a052b3073f207@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 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".
In my experience with using Oracle, SQL Server and Mysql, such SQL
should be executed succussfully. After i met this problem, i turned back to
some textbook about database transaction and i got information like this "it
is ok to have some inconsistent state during one transaction, only if it is
consistent after the transaction is finished succesfully."
And now, i'm wondering what's the problem with this SQL query? How
'update' is executed in the backend?
I'm using PostgreSQL 8.1.4.
Thanks
Shawn Gao
2006-08-22
From | Date | Subject | |
---|---|---|---|
Next Message | Bernard Miville | 2006-08-22 02:05:21 | Postgresql, Perl and DBI connect problem |
Previous Message | Tom Lane | 2006-08-22 00:09:38 | Re: Queries joining views |