adding columns with defaults is not implemented

On Thu, 2005-06-02 at 15:29, Marcelo wrote:

Hello,
Using Postgres 7.4, I am trying to perform an "alter table
temptable add column "myCol" serial"

It gives the following msg
ERROR: adding columns with defaults is not implemented

You cannot add a column that is serial in a table which already has
data in postgres 7.

Is there a way I can create a serial column on a table which already
has data? Or is the only solution upgrading to postgres 8 ?

You can add a default after you add the column with a separate alter
table statement...

"Marcelo" <marcelo@humano2.com> writes:

Hello,
Using Postgres 7.4, I am trying to perform an "alter table temptable add
column "myCol" serial"

It gives the following msg
ERROR: adding columns with defaults is not implemented

You cannot add a column that is serial in a table which already has data
in postgres 7.

Is there a way I can create a serial column on a table which already has
data? Or is the only solution upgrading to postgres 8 ?

I think you can do it as a three-step process:

1) Add the column as an "int" (or "int8") with no default
2) Create the sequence for the column by hand
3) Do ALTER TABLE foo ALTER COLUMN "myCol" DEFAULT nextval('myseq');

This definitely works in 8.0.X and I think it should work in 7.4.X as
well.

A serial column is basically just syntactic sugar for the above, so
you're not losing anything.

-Doug

Hi,
Thanks for your reply, but I have some doubts.

Are yoy sugesting I create the column as an Integer then change it to
Serial? in Pgsql 7 you cant change a column type.

If I create the column as an int then add a default value, how can I make
this default value increment with each insert?

Thanks again for your help.
Marcelo

----- Original Message -----
From: "Scott Marlowe" <smarlowe@g2switchworks.com>
To: "Marcelo" <marcelo@humano2.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, June 02, 2005 4:43 PM
Subject: Re: [GENERAL] adding columns with defaults is not implemented

On Thu, 2005-06-02 at 17:47 -0400, Marcelo wrote:

Hi,
Thanks for your reply, but I have some doubts.

Are yoy sugesting I create the column as an Integer then change it to
Serial? in Pgsql 7 you cant change a column type.

If I create the column as an int then add a default value, how can I make
this default value increment with each insert?

Thanks again for your help.
Marcelo

----- Original Message -----
From: "Scott Marlowe" <smarlowe@g2switchworks.com>
To: "Marcelo" <marcelo@humano2.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, June 02, 2005 4:43 PM
Subject: Re: [GENERAL] adding columns with defaults is not implemented

On Thu, 2005-06-02 at 15:29, Marcelo wrote:

Hello,
Using Postgres 7.4, I am trying to perform an "alter table
temptable add column "myCol" serial"

It gives the following msg
ERROR: adding columns with defaults is not implemented

You cannot add a column that is serial in a table which already has
data in postgres 7.

Is there a way I can create a serial column on a table which already
has data? Or is the only solution upgrading to postgres 8 ?

You can add a default after you add the column with a separate alter
table statement...

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

[Bottom posting to the top-posted reply] ....

You would have to do this in steps: Assuming that "mytable" exists and
"mycol" is currently of type int and currently has as its max value 100:

create sequence mytable_mycol_seq start with 101;
alter table mytable alter mycol set default
nextval('mytable_mycol_seq'::text);

At this point any new inserts will start autoincrementing the mycol
field starting with value 101.

Sven

Marcelo wrote:

Hi,
Thanks for your reply, but I have some doubts.

Are yoy sugesting I create the column as an Integer then change it to
Serial? in Pgsql 7 you cant change a column type.

Serial is not a real data type. Do this.

create table foo (bar integer not null);
create sequence foo_bar_seq;
alter table foo alter column bar set default nextval('foo_bar_seq');

Sincerely,

Joshua D. Drake

If I create the column as an int then add a default value, how can I make
this default value increment with each insert?

Thanks again for your help.
Marcelo

----- Original Message -----
From: "Scott Marlowe" <smarlowe@g2switchworks.com>
To: "Marcelo" <marcelo@humano2.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, June 02, 2005 4:43 PM
Subject: Re: [GENERAL] adding columns with defaults is not implemented

On Thu, 2005-06-02 at 15:29, Marcelo wrote:

Hello,
Using Postgres 7.4, I am trying to perform an "alter table
temptable add column "myCol" serial"

It gives the following msg
ERROR: adding columns with defaults is not implemented

You cannot add a column that is serial in a table which already has
data in postgres 7.

Is there a way I can create a serial column on a table which already
has data? Or is the only solution upgrading to postgres 8 ?

You can add a default after you add the column with a separate alter
table statement...

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Thu, Jun 02, 2005 at 16:57:14 -0400,
Douglas McNaught <doug@mcnaught.org> wrote:

A serial column is basically just syntactic sugar for the above, so
you're not losing anything.

In recent versions of postgres, I do think you lose something. I don't
believe the dependency will get tracked as it would for a serial
column (though this is really a bug that should eventually get fixed).
pg_get_serial_sequence will probably not work as well.