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

On Mon, Sep 24, 2007 at 12:50:22PM +0530, Anoo Sivadasan Pillai wrote:
> 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;

One way to do this is with Postgres's UPDATE ... FROM construct:

CREATE TABLE foo(i INTEGER PRIMARY KEY);
INSERT INTO foo(i) VALUES (1), (2), (3), (4), (5);
UPDATE foo SET i=foo.i+1
FROM (SELECT i FROM foo ORDER BY i DESC) f
WHERE f.i = foo.i;

While specific to Postgres, this technique avoids a lot of messing
around with boundary conditions :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate