alter table alter column: from integer to serial?

[Thomas Guettler]

Hi,

how to update a primary key column from type integer to type serial?

foo=# alter table footable alter column id type serial;
FEHLER: Typ »serial« existiert nicht
(ERROR: Typ serial does not exist)

Thomas

[HansH]

"Thomas Guettler" <h...@tbz-pariv.de> schreef in bericht
news:61b6ldF...@mid.individual.net...

> how to update a primary key column from type integer to type serial?
>
> foo=# alter table footable alter column id type serial;

> (ERROR: Typ serial does not exist)
>

http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
" The data types serial and bigserial are not true types, but merely a
notational convenience for setting up unique identifier columns..."

To convert a table difined like
CREATE TABLE test_table
( test_column integer NOT NULL,
CONSTRAINT pk_test PRIMARY KEY (test_column) )
into
CREATE TABLE test_table
( test_column serial NOT NULL,
CONSTRAINT pk_test PRIMARY KEY (test_column) )
execute CREATE SEQUENCE test_table_test_column_seq
INCREMENT 1MINVALUE 1MAXVALUE 2147483648 START 1CACHE 1;
ALTER TABLE test_tableALTER COLUMN test_column
SET DEFAULT nextval('test_table_test_column_seq'::regclass);

Change MAXVALUE to the type -smallint, int or bigint-
Change START to the current max value of test_column.

HansH