Re: adding SERIAL to a table

On Mon, 1 Sep 2003, Tom Lane wrote:

> "Claudio Lapidus" <clapidus@hotmail.com> writes:
> > So? Is there a way to add the sequence to an existing table?
>
> Sure.  You have to break the SERIAL down to its component parts though.
> Something like
>
>     CREATE SEQUENCE seq;
>     ALTER TABLE tab ADD COLUMN ser INTEGER;
>     UPDATE tab SET ser = nextval('seq');    -- this will take awhile
>     ALTER TABLE tab ALTER COLUMN ser SET DEFAULT nextval('seq');
>     ALTER TABLE tab ALTER COLUMN ser SET NOT NULL;
>     -- possibly also add a UNIQUE constraint

For folks just starting out, you can also do it this way:

=>begin;
=>create table a (info text, date date);
CREATE TABLE
=> insert into a values ('abc','2003-04-03');
INSERT 1127459 1
=> create table b (info text, date date, id serial);
NOTICE:  CREATE TABLE will create implicit sequence "b_id_seq" for SERIAL
column "b.id"
CREATE TABLE
=> insert into b (select * from a);
INSERT 1127468 1
=> select * from b;
 info |    date    | id
------+------------+----
 abc  | 2003-04-03 |  1

=>drop table a;
DROP TABLE
=> alter table b rename to a;
ALTER TABLE
=>commit;