Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Hi,

I am not using any sequences, The following batch can reproduce the behaviour.

CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;

INSERT  INTO master VALUES  ( 1, 1 ) ;

INSERT  INTO master VALUES  ( 2, 2) ;

UPDATE  master SET m2 = m2 + 1;

With Cheers,

----- 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