Probably you are using a sequence, and if so you need to update the value of
sequence prior to update:
SELECT setval('master_m1_seq',((SELECT id_m1 FROM master ORDER BY 1 DESC LIMIT
1)+1));
----- Original Message -----
From: Anoo Sivadasan Pillai
To: pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Sent: Monday, September 24, 2007 3:20 AM
Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint
"master_pkey" is raised? - Is this a Bug?
I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)" on Windows 2003 server
While I am trying to update a prmary key It is failing with the following
message "ERROR: duplicate key violates unique constraint "master_pkey" "
Can anybody explain why this happens so? Sending the script that I tried.
CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate key violates unique
constraint "master_pkey"
If I insert data in the reverse order it is making no problem. Is this a Bug
?
I tried ,
TRUNCATE TABLE master;
INSERT INTO master VALUES ( 3, 'm3' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
It works perfectly.
Anoo S
Visit our Website at www.rmesi.co.in
This message is confidential. You should not copy it or disclose its contents
to anyone. You may use and apply the information for the intended purpose only.
Internet communications are not secure; therefore, RMESI does not accept legal
responsibility for the contents of this message. Any views or opinions
presented are those of the author only and not of RMESI. If this email has come
to you in error, please delete it, along with any attachments. Please note that
RMESI may intercept incoming and outgoing email communications.
Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging
to RMESI. Where the email and any attachments do contain information of a
confidential nature, including without limitation information relating to trade
secrets, special terms or prices these shall be deemed for the purpose of the
Freedom of Information Act 2000 as information provided in confidence by RMESI
and the disclosure of which would be prejudicial to RMESI's commercial
interests.
This email has been scanned for viruses by Trend ScanMail.
------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.30/1025 - Release Date: 23/09/2007
13:53
