Re: adding SERIAL to a table

On Mon, 1 Sep 2003, Tom Lane wrote:

> "Claudio Lapidus" <clapidus(at)hotmail(dot)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;