Re: Cannot add an column of type serial or bigserial

Christopher Kings-Lynne wrote:
> You cannot add a serial column after table creation time in PostgreSQL.

But you can do the following from the query editor:

ALTER TABLE table ADD column INTEGER;
CREATE SEQUENCE table_column_seq;
ALTER TABLE table ALTER column SET DEFAULT nextval('"table_column_seq"'::text);

If you want the column to be NOT NULL as well, you will have to
populate that column for all the rows and then do:

ALTER TABLE table ALTER column SET NOT NULL;

Ian

> Chris
> 
> David Sigeti wrote:
> 
> > Dear Pgadmin Folks,
> > 
> > I am using pgadminIII 1.0.2 with PostgreSQL 7.4 under W2K SP4 and Cygwin 
> > (current as of 2 or 3 weeks ago).  The server is running locally.
> > 
> > If I attempt to add an column of type serial or bigserial to a table, 
> > the operation fails with a message telling me that adding an column with 
> > a default value is not implemented.  What is really weird is that one 
> > can, in fact, add a column with a default value, like an int4 with a 
> > default value or 0.  The problem does not arise if the serial (or 
> > bigserial) column is created when the table is created (as a part of 
> > CREATE TABLE).
> > 
> > Yours,
> > David
> > 
> > ---
> > Dr. David E. Sigeti
> > Phone: 505-667-9239
> > E-mail: sigeti@lanl.gov
> > Surface mail: X-8, MS-F645, Los Alamos National Laboratory,
> >               Los Alamos, NM 87545 USA
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> >               http://archives.postgresql.org
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>