Thread: Cannot add an column of type serial or bigserial
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
You cannot add a serial column after table creation time in PostgreSQL. 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
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
>
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). > serial isn't a true datatype but a keyword that can be used only at creation time. Still, pgAdmin3 should create the commands necessary for this to make life easier; I added this as a TODO item. Regards, Andreas
> serial isn't a true datatype but a keyword that can be used only at > creation time. Still, pgAdmin3 should create the commands necessary for > this to make life easier; I added this as a TODO item. The problem will be recording the dependency properly in pg_depend... Chris
Christopher Kings-Lynne wrote: >> serial isn't a true datatype but a keyword that can be used only at >> creation time. Still, pgAdmin3 should create the commands necessary >> for this to make life easier; I added this as a TODO item. > > > The problem will be recording the dependency properly in pg_depend... That's no real problem, just an issue that should be thought of; thanks for mentioning. Regards, Andreas
At 12:21 PM 12/10/2003 +0100, Andreas Pflug wrote: >David Sigeti wrote: > >>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). >serial isn't a true datatype but a keyword that can be used only at >creation time. Still, pgAdmin3 should create the commands necessary for >this to make life easier; I added this as a TODO item. Adding this functionality would be great but I suggest that the error message be changed right away as it is seriously misleading. Thanks to everyone who took the time to help me out. 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
David Sigeti wrote:
> At 12:21 PM 12/10/2003 +0100, Andreas Pflug wrote:
>
>> David Sigeti wrote:
>>
>>> 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.
>>
pgAdmin breaks this into two steps for you; have a look at the SQL page.
>>> The problem does not arise if the serial (or bigserial) column is
>>> created when the table is created (as a part of CREATE TABLE).
>>
>> serial isn't a true datatype but a keyword that can be used only at
>> creation time. Still, pgAdmin3 should create the commands necessary
>> for this to make life easier; I added this as a TODO item.
>
>
> Adding this functionality would be great but I suggest that the error
> message be changed right away as it is seriously misleading.
>
The error message originates from the backend. I agree that this might
be confusing to newbies, who don't know that serial actually is int4
default nextval('...') (currently pgAdmin3 doesn't either :-)
I'm forwarding this to pgsql-hackers.
Regards,
Andreas