Обсуждение: 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
RES: Add column and specify the column position in a table
От
"Alejandro Michelin Salomon \( Adinet \)"
Дата:
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