Skip site navigation (1) Skip section navigation (2)

Re: Update PK Violation

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Achilleas Mantzios" <achill(at)matrix(dot)gatewaynet(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, "Franklin Haut" <franklin(dot)haut(at)gmail(dot)com>
Subject: Re: Update PK Violation
Date: 2008-01-16 16:15:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
On Jan 16, 2008 8:30 AM, Achilleas Mantzios
<achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> Στις Tuesday 15 January 2008 23:03:49 ο/η Franklin Haut έγραψε:
> > Hi all,
> >
> > i have a problem with one update sentence sql.
> >
> A simple way i use:
> foodb=# update temp set num = num*1000  where num >= 5;
> foodb=# insert into temp values (5, 'NOT');
> foodb=# update temp set num = 1 + num/1000  where num >= 6;

That's still open to possible collisions.  Another method that avoids
them is to use negative numbers.  i.e.

update temp set num = -1*num where num >=5;
insert into temp values (5,'NOT';);
update temp set num = (-1*num) + 1 where num < 0;

Assuming you don't use negative numbers in your setup, works a charm.

However, this kind of activity screams "bad design"...  Not that I've
never found myself right smack dab in the middle of such a thing

In response to

pgsql-sql by date

Next:From: Bryan EmrysDate: 2008-01-16 17:34:53
Subject: Re: SQL dealing with subquery
Previous:From: Bryan EmrysDate: 2008-01-16 15:54:41
Subject: Re: SQL dealing with subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group