Обсуждение: Add column and specify the column position in a table

Hello,

I am trying to insert one column to a specific position in a table.

In mysql, I can do:
. create table test(id varchar(3), name varchar(12));
. alter table test add column givename varchar(12) after id;


I am looking for similar things in postgresql to add a new column to the
correct position in a table.

Could someone hint me please.

Thanks alot!
Ying Lu

Hi Ying:

This feature seems by a mysql add tu create table command, only mysql can do
dat.

Alejandro Michelin Salomon



-->-----Mensagem original-----
-->De: pgsql-general-owner@postgresql.org
-->[mailto:pgsql-general-owner@postgresql.org] Em nome de Emi Lu
-->Enviada em: quarta-feira, 17 de maio de 2006 18:21
-->Para: pgsql-general@postgresql.org
-->Assunto: [GENERAL] Add column and specify the column
-->position in a table
-->
-->
-->Hello,
-->
-->I am trying to insert one column to a specific position in a table.
-->
-->In mysql, I can do:
-->. create table test(id varchar(3), name varchar(12));
-->. alter table test add column givename varchar(12) after id;
-->
-->
-->I am looking for similar things in postgresql to add a new
-->column to the
-->correct position in a table.
-->
-->Could someone hint me please.
-->
-->Thanks alot!
-->Ying Lu
-->
-->
-->
-->
-->
-->---------------------------(end of
-->broadcast)---------------------------
-->TIP 6: explain analyze is your friend
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.392 / Virus Database: 268.6.0/341 - Release
-->Date: 16/5/2006
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.392 / Virus Database: 268.6.0/341 - Release
-->Date: 16/5/2006
-->
-->

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 16/5/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 16/5/2006

>This feature seems by a mysql add tu create table command, only mysql can do
>dat.
>
>Alejandro Michelin Salomon
>
>
>

I think it is a very useful feature for postgresql to support it.

If we have this feature supported, I do not have to recreate the table
and resetup all foreign key constraints, views, triggers, etc that are
based on the table.


>-->I am trying to insert one column to a specific position in a table.
>-->
>-->In mysql, I can do:
>-->. create table test(id varchar(3), name varchar(12));
>-->. alter table test add column givename varchar(12) after id;
>-->
>-->I am looking for similar things in postgresql to add a new
>-->column to the
>-->correct position in a table.
>
>-->Could someone hint me please.
>-->Thanks alot!
>
>

>Ying Lu
>
>
>

Emi Lu wrote:
> Hello,
>
> I am trying to insert one column to a specific position in a table.
>
> In mysql, I can do:
> . create table test(id varchar(3), name varchar(12));
> . alter table test add column givename varchar(12) after id;
>
>
> I am looking for similar things in postgresql to add a new column to the
> correct position in a table.
>
> Could someone hint me please.

You can't. You can only add columns to the end of a table.

>
> Thanks alot!
> Ying Lu
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

On Wed, May 17, 2006 at 05:48:52PM -0400, Emi Lu wrote:
> I think it is a very useful feature for postgresql to support it.
>
> If we have this feature supported, I do not have to recreate the table
> and resetup all foreign key constraints, views, triggers, etc that are
> based on the table.

Uh, why do you have to do that? Please don't tell me you're using SELECT
*...

In any case, there's extensive discussion about this in the -hackers
archives. IIRC, there is consensus that this would be nice to have but
no one has cared enough to actually make it happen. There are some
non-trivial issues since this would mean either completely re-writing
the table when you do an ALTER or you'd have to be able to divorce the
catalog representation of a table with the on-disk representation.
Though there are other advantages to doing the later, it's non-trivial.

> >-->I am trying to insert one column to a specific position in a table.
> >-->
> >-->In mysql, I can do:
> >-->. create table test(id varchar(3), name varchar(12));
> >-->. alter table test add column givename varchar(12) after id;
> >-->
> >-->I am looking for similar things in postgresql to add a new
> >-->column to the
> >-->correct position in a table.
> >
> >-->Could someone hint me please.
> >-->Thanks alot!
> >
> >
>
> >Ying Lu
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: 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
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

the position doesn't really matter in any relational structure.. does it?

On 5/17/06, Emi Lu <emilu@encs.concordia.ca> wrote:
> Hello,
>
> I am trying to insert one column to a specific position in a table.
>
> In mysql, I can do:
> . create table test(id varchar(3), name varchar(12));
> . alter table test add column givename varchar(12) after id;
>
>
> I am looking for similar things in postgresql to add a new column to the
> correct position in a table.
>
> Could someone hint me please.
>
> Thanks alot!
> Ying Lu
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Jim C. Nasby wrote:

>On Wed, May 17, 2006 at 05:48:52PM -0400, Emi Lu wrote:
>
>
>>I think it is a very useful feature for postgresql to support it.
>>
>>If we have this feature supported, I do not have to recreate the table
>>and resetup all foreign key constraints, views, triggers, etc that are
>>based on the table.
>>
>>
>
>Uh, why do you have to do that? Please don't tell me you're using SELECT
>*...
>
>
>
No. It is not for select.

I have tens of tables with very clean structure. For example, username,
application_code, last_modified_by, etc in specific orders.

Since the business model is changed, I have to add some columns to
serveral tables.

I prefer columns orders following my other tables.


>In any case, there's extensive discussion about this in the -hackers
>archives. IIRC, there is consensus that this would be nice to have but
>no one has cared enough to actually make it happen. There are some
>non-trivial issues since this would mean either completely re-writing
>the table when you do an ALTER or you'd have to be able to divorce the
>catalog representation of a table with the on-disk representation.
>Though there are other advantages to doing the later, it's non-trivial.
>
>
If it does not support, I will recreate my tables.

Thanks.

Emi Lu wrote:
>>
> No. It is not for select.
>
> I have tens of tables with very clean structure. For example, username,
> application_code, last_modified_by, etc in specific orders.
>
> Since the business model is changed, I have to add some columns to
> serveral tables.
>
> I prefer columns orders following my other tables.
>
>
>> In any case, there's extensive discussion about this in the -hackers
>> archives. IIRC, there is consensus that this would be nice to have but
>> no one has cared enough to actually make it happen. There are some
>> non-trivial issues since this would mean either completely re-writing
>> the table when you do an ALTER or you'd have to be able to divorce the
>> catalog representation of a table with the on-disk representation.
>> Though there are other advantages to doing the later, it's non-trivial.
>>
>>
> If it does not support, I will recreate my tables.
>
> Thanks.
>

Have you considered creating a view over the tables in question? You can
order the attributes in any fashion you like that way.

On Wednesday 17 May 2006 18:12, Jim C. Nasby wrote:
> In any case, there's extensive discussion about this in the -hackers
> archives. IIRC, there is consensus that this would be nice to have but
> no one has cared enough to actually make it happen. There are some
> non-trivial issues since this would mean either completely re-writing
> the table when you do an ALTER or you'd have to be able to divorce the
> catalog representation of a table with the on-disk representation.
> Though there are other advantages to doing the later, it's non-trivial.
>

I recall back when we actually tried to split physical with logical
representation, but I don't recall anyone trying to do the table rewrite
method. Perhaps there are objections to it but istm it might be worth looking
into... we already do it for type.  Incidentally that is the same method that
mysql uses to implement it (for the table types that will actually do it,
some just ignore your request silently!)

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL