From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Naz Gassiep" <naz(at)mira(dot)net> |
Cc: | "pgSQL - General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Serial Data Type |
Date: | 2008-04-03 04:18:53 |
Message-ID: | b42b73150804022118t596b475i7cdbc0cade2e9408@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Apr 2, 2008 at 10:12 PM, Naz Gassiep <naz(at)mira(dot)net> wrote:
> I have just created a table using SELECT INTO however the PK was
> supposed to be a serial. It is now an integer. To make it a serial I
> just create the seq and set the default to be the nextval() of that
> sequence right? is there anything else I need to do? It'll maintain the
> transactional safety of a serial created default, right? I.e., it'll not
> rollback seq values on a transaction abortion will it?
> Thanks,
not quite. you also have to set the sequence to a higher number than
the highest currently inserted key of the table. you do this with
setval...watch out for the is_called property. also you should lock
the table first...otherwise you would get a race if someone inserts a
value into the table between the time when you calculate the value for
setval and you assign it to the sequence.
so (pseudo code here):
begin;
lock table foo;
setval('the_sequence, (select max(foo_id) from foo), true);
alter table foo alter foo_id default nextval('the_sequence');
alter sequence the_sequence owned by foo.foo_id; -- h/t to adam rich
commit;
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-04-03 04:37:59 | Re: PG 8.3.x doesn't get build |
Previous Message | Adam Rich | 2008-04-03 03:22:11 | Re: Serial Data Type |