From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgres unique index checking and atomic transactions |
Date: | 2003-07-24 16:36:43 |
Message-ID: | Pine.LNX.4.33.0307241036120.25680-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 24 Jul 2003, Greg Stark wrote:
>
> So I have to adjust a primary key by adding one to every existing record.
> Obviously this isn't a routine operation, my data model isn't that messed up.
> It's a one-time manual operation.
>
> However when I tried to do the equivalent of:
>
> update tab set pk = pk + 1
>
> I got
>
> ERROR: Cannot insert a duplicate key into unique index tab_pkey
>
> Is that right? Obviously after completing the query there would be no
> duplicate keys. Is this a case where I would need deferred constraints to
> allow this? Even for immediate constraints shouldn't a single sql update be
> able to go ahead as long as it leaves things in a consistent state?
I've run into this before. If your pks are in the range of say 1 to 1000,
then just add 50000 to them, then subtract 49999...
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Ayers | 2003-07-24 16:43:23 | Re: 0/1 vs true/false |
Previous Message | Andrew Ayers | 2003-07-24 16:31:15 | Re: Limited varchar, unlimited varchar, or text? |