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

Hi Ashish,

Do you mean to say that UPDATE command will work depending on the order
in which data is inserted?

I mean,

The following command will not work

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;

But the following command will work

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

UPDATE master SET m1 = m1 + 1;

Even though both are inserting same data ?

Anoo S

From: Ashish Karalkar [mailto:ashish(dot)karalkar(at)info-spectrum(dot)com]
Sent: 24 September 2007 13:20
To: Anoo Sivadasan Pillai; pgsql-general(at)postgresql(dot)org
Cc: Anoo Sivadasan Pillai
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique
constraint "master_pkey" is raised? - Is this a Bug?

As per as I think this is not bug,

in first case

when u update the first row

In first case:

M1= 1+1 =2 conflict with the second row.

in second case:

M1=3+1=4 no conflict with second row

I hope this will help.

With reagrds

Ashish

----- Original Message -----

From: Anoo Sivadasan Pillai <mailto:aspillai(at)in(dot)rm(dot)com>

To: pgsql-general(at)postgresql(dot)org

Cc: Anoo Sivadasan Pillai <mailto:aspillai(at)in(dot)rm(dot)com>

Sent: Monday, September 24, 2007 12:50 PM

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.
Visit our Website at http://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.