Re: Creating a new column with SERIAL as data type - Mailing list pgsql-novice

Kumar S <ps_postgres@yahoo.com> writes:
> friends=> alter table friend  add column fr_id serial;
> NOTICE:  ALTER TABLE will create implicit sequence "friend_fr_id_seq" for "serial" column "friend.fr_id"
> ERROR:  adding columns with defaults is not implemented
> HINT:  Add the column, then use ALTER TABLE SET DEFAULT.

> Can any one suggest what is wrong here.

Just what it says: adding columns with defaults is not implemented.
(It is implemented in 8.0, but that won't help you today.)  You can
do it by hand:

create sequence friend_fr_id_seq;
alter table friend add column fr_id int;
update friend set fr_id = nextval('friend_fr_id_seq');
alter table friend alter column fr_id set default nextval('friend_fr_id_seq');

This isn't an exact substitute since the sequence will appear as
a standalone object and not be hidden behind the "serial" column,
but it's functionally equivalent.

            regards, tom lane