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


Tue, 09 Dec 2003 22:55:16 -0700
David Sigeti <sigeti@lanl.gov>
Cannot add an column of type serial or bigserial
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

Wed, 10 Dec 2003 14:11:19 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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
> 

Wed, 10 Dec 2003 09:58:56 +0200
Ian Freislich <ianf@za.uu.net>
Re: Cannot add an column of type serial or bigserial 
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



Wed, 10 Dec 2003 12:21:15 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Cannot add an column of type serial or bigserial
> 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

Wed, 10 Dec 2003 19:40:48 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Cannot add an column of type serial or bigserial
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



Wed, 10 Dec 2003 14:34:48 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Cannot add an column of type serial or bigserial
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


Wed, 10 Dec 2003 08:54:46 -0700
David Sigeti <sigeti@lanl.gov>
Re: Cannot add an column of type serial or
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



Wed, 10 Dec 2003 17:13:35 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Cannot add an column of type serial