Lists: | pgsql-general |
---|
From: | "Marcelo" <marcelo(at)humano2(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | adding columns with defaults is not implemented |
Date: | 2005-06-02 20:29:04 |
Message-ID: | 014601c567b1$b81a2280$500aa8c0@humano2.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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 ?
Thanks
From: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
---|---|
To: | Marcelo <marcelo(at)humano2(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: adding columns with defaults is not implemented |
Date: | 2005-06-02 20:43:20 |
Message-ID: | 1117745000.5758.31.camel@state.g2switchworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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...
From: | Douglas McNaught <doug(at)mcnaught(dot)org> |
---|---|
To: | "Marcelo" <marcelo(at)humano2(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: adding columns with defaults is not implemented |
Date: | 2005-06-02 20:57:14 |
Message-ID: | m2hdgg79zp.fsf@Douglas-McNaughts-Powerbook.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Marcelo" <marcelo(at)humano2(dot)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
From: | "Marcelo" <marcelo(at)humano2(dot)com> |
---|---|
To: | "Scott Marlowe" <smarlowe(at)g2switchworks(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: adding columns with defaults is not implemented |
Date: | 2005-06-02 21:47:27 |
Message-ID: | 017e01c567bc$ab798e70$500aa8c0@humano2.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)g2switchworks(dot)com>
To: "Marcelo" <marcelo(at)humano2(dot)com>
Cc: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
From: | Sven Willenberger <sven(at)dmv(dot)com> |
---|---|
To: | Marcelo <marcelo(at)humano2(dot)com> |
Cc: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: adding columns with defaults is not implemented |
Date: | 2005-06-02 22:04:53 |
Message-ID: | 1117749893.23954.65.camel@lanshark.dmv.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)g2switchworks(dot)com>
> To: "Marcelo" <marcelo(at)humano2(dot)com>
> Cc: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)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
From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | Marcelo <marcelo(at)humano2(dot)com> |
Cc: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: adding columns with defaults is not implemented |
Date: | 2005-06-02 22:12:14 |
Message-ID: | 429F843E.4050800@commandprompt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)g2switchworks(dot)com>
> To: "Marcelo" <marcelo(at)humano2(dot)com>
> Cc: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)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/
From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Douglas McNaught <doug(at)mcnaught(dot)org> |
Cc: | Marcelo <marcelo(at)humano2(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: adding columns with defaults is not implemented |
Date: | 2005-06-03 14:02:07 |
Message-ID: | 20050603140207.GB8358@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, Jun 02, 2005 at 16:57:14 -0400,
Douglas McNaught <doug(at)mcnaught(dot)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.