Re: adding SERIAL to a table

"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

We haven't yet got round to supporting ADD COLUMN ... DEFAULT because
according to the SQL spec that implies doing the UPDATE to fill the
column values immediately, and that's just a chunk of code no one's
written yet.

> Or, alternatively, is there a way to issue a \copy command while letting th=
> e sequence fill in the serial field?

Yes, you have to list in the COPY command just the columns that are
actually being supplied in the input data. Columns not mentioned (like
the serial column) get filled from their defaults. I think this is
new in 7.3 ... it's pretty recent anyway.

regards, tom lane