Re: adding columns with defaults is not implemented

"Marcelo" <marcelo(at)humano2(dot)com> writes:

> Hello,
> Using Postgres 7.4, I am trying to perform an "alter table temptable add
> column "myCol" serial"
>
> It gives the following msg
> ERROR: adding columns with defaults is not implemented
>
> You cannot add a column that is serial in a table which already has data
> in postgres 7.
>
> Is there a way I can create a serial column on a table which already has
> data? Or is the only solution upgrading to postgres 8 ?

I think you can do it as a three-step process:

1) Add the column as an "int" (or "int8") with no default
2) Create the sequence for the column by hand
3) Do ALTER TABLE foo ALTER COLUMN "myCol" DEFAULT nextval('myseq');

This definitely works in 8.0.X and I think it should work in 7.4.X as
well.

A serial column is basically just syntactic sugar for the above, so
you're not losing anything.

-Doug