Thread: Serial Data Type

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,
- Naz.

> 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?

You'll want to do this:

ALTER SEQUENCE table_col_id_seq OWNED BY table.col_id;

http://www.postgresql.org/docs/8.3/interactive/sql-altersequence.html


> 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?

Yes

On Wed, Apr 2, 2008 at 10:12 PM, Naz Gassiep <naz@mira.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;

"Adam Rich" <adam.r@sbcglobal.net> writes:
>> 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?

> You'll want to do this:
> ALTER SEQUENCE table_col_id_seq OWNED BY table.col_id;
> http://www.postgresql.org/docs/8.3/interactive/sql-altersequence.html

That's correct as far as it goes, but the OP might benefit more from
reading the description of what a "serial" column really is:

http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL

            regards, tom lane