Thread: Adding a New Column Specifically In a Table
OK so I have read the docs and Google to try and find a way to add a new column to an existing table. My problem is I need this new column to be created 3rd rather than just dumping this new column to the end of my table. I can't find anywhere how I can insert my new column as the 3rd table column rather than the last (seventh). Does anyone know how I can accomplish this or if it's even possible. Seems like a common task but I checked the documentation and may have missed it in my reading. ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; The above command dumps the 'employer' column at the very end of my table which is not what I want. Thanks for any assistance...
On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens <carlos.mennens@gmail.com> wrote: > OK so I have read the docs and Google to try and find a way to add a > new column to an existing table. My problem is I need this new column > to be created 3rd rather than just dumping this new column to the end > of my table. I can't find anywhere how I can insert my new column as > the 3rd table column rather than the last (seventh). Does anyone know > how I can accomplish this or if it's even possible. Seems like a > common task but I checked the documentation and may have missed it in > my reading. > > > ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; Ah sadly I just found this after I pressed 'send' and realized PostgreSQL doesn't support it...that sucks :( http://wiki.postgresql.org/wiki/Alter_column_position
On 13 October 2010 19:04, Carlos Mennens <carlos.mennens@gmail.com> wrote: > OK so I have read the docs and Google to try and find a way to add a > new column to an existing table. My problem is I need this new column > to be created 3rd rather than just dumping this new column to the end > of my table. I can't find anywhere how I can insert my new column as > the 3rd table column rather than the last (seventh). Does anyone know > how I can accomplish this or if it's even possible. Seems like a > common task but I checked the documentation and may have missed it in > my reading. > > > ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; > > The above command dumps the 'employer' column at the very end of my > table which is not what I want. > > Thanks for any assistance... You cannot place a column before any existing column. Why is that important? You can select the columns in any order you wish in queries. And even if you had the ability to specify placement of a column before another column, its unlikely it would physically rewrite the column data to match that, so the column position would only be cosmetic. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On 13 October 2010 19:06, Carlos Mennens <carlos.mennens@gmail.com> wrote: > On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens > <carlos.mennens@gmail.com> wrote: >> OK so I have read the docs and Google to try and find a way to add a >> new column to an existing table. My problem is I need this new column >> to be created 3rd rather than just dumping this new column to the end >> of my table. I can't find anywhere how I can insert my new column as >> the 3rd table column rather than the last (seventh). Does anyone know >> how I can accomplish this or if it's even possible. Seems like a >> common task but I checked the documentation and may have missed it in >> my reading. >> >> >> ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; > > Ah sadly I just found this after I pressed 'send' and realized > PostgreSQL doesn't support it...that sucks :( > > http://wiki.postgresql.org/wiki/Alter_column_position The question is, why do you require it? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Carlos Mennens wrote: > OK so I have read the docs and Google to try and find a way to add a > new column to an existing table. My problem is I need this new column > to be created 3rd rather than just dumping this new column to the end > of my table. I can't find anywhere how I can insert my new column as > the 3rd table column rather than the last (seventh). Does anyone know > how I can accomplish this or if it's even possible. Seems like a > common task but I checked the documentation and may have missed it in > my reading. > > ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; > > The above command dumps the 'employer' column at the very end of my > table which is not what I want. > > Thanks for any assistance... Why do you want to do this? Columns should only be referenced by their names and not by any kind of ordinal position. You should treat the list of columns as being an unordered set, and so your new column isn't actually in 7th place, and you can display your columns in any order you want. Relying on any intrinsic ordinal position of columns is just going to get you into trouble. I also didn't see from http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html how you would do what you asked. The page http://wiki.postgresql.org/wiki/Alter_column_position also shows some contrivances you could go through to get what you want, but it is better to just not care about order in the first place. -- Darren Duncan
Carlos Mennens, 13.10.2010 20:06: >> OK so I have read the docs and Google to try and find a way to add a >> new column to an existing table. My problem is I need this new column >> to be created 3rd rather than just dumping this new column to the end >> of my table. I can't find anywhere how I can insert my new column as >> the 3rd table column rather than the last (seventh). Does anyone know >> how I can accomplish this or if it's even possible. Seems like a >> common task but I checked the documentation and may have missed it in >> my reading. >> >> >> ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; > > Ah sadly I just found this after I pressed 'send' and realized > PostgreSQL doesn't support it...that sucks :( The position of a column in a table has no meaning whatsoever - just like rows have no "position" as well. If you want columns returned in a specific order, simply put them in the desired order in your SELECT statement. Thomas
Dear Carlos, In old version of postgresql attnum colmn of pg_catalog.pg_attribute system catalog could be modified to get desired results. I am not sure if it should be done anymore. Rajesh Kumar Mallah. On Wed, Oct 13, 2010 at 2:06 PM, Carlos Mennens <carlos.mennens@gmail.com> wrote: > On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens > <carlos.mennens@gmail.com> wrote: >> OK so I have read the docs and Google to try and find a way to add a >> new column to an existing table. My problem is I need this new column >> to be created 3rd rather than just dumping this new column to the end >> of my table. I can't find anywhere how I can insert my new column as >> the 3rd table column rather than the last (seventh). Does anyone know >> how I can accomplish this or if it's even possible. Seems like a >> common task but I checked the documentation and may have missed it in >> my reading. >> >> >> ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; > > Ah sadly I just found this after I pressed 'send' and realized > PostgreSQL doesn't support it...that sucks :( > > http://wiki.postgresql.org/wiki/Alter_column_position > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Dear Carlos, application code should not depend on column positions. the requirement is not good. regds rajesh kumar mallah.
I think it's incorrect to expect a query to return column in any specific order if you do something like "select * from...". You may see columns returned in the order you created them, but I don't believe it's guaranteed. If you want aspecific order, then.... "select col1, col3, col5, col2, ...". -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Carlos Mennens Sent: Wednesday, October 13, 2010 2:07 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Adding a New Column Specifically In a Table On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens <carlos.mennens@gmail.com> wrote: > OK so I have read the docs and Google to try and find a way to add a > new column to an existing table. My problem is I need this new column > to be created 3rd rather than just dumping this new column to the end > of my table. I can't find anywhere how I can insert my new column as > the 3rd table column rather than the last (seventh). Does anyone know > how I can accomplish this or if it's even possible. Seems like a > common task but I checked the documentation and may have missed it in > my reading. > > > ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; Ah sadly I just found this after I pressed 'send' and realized PostgreSQL doesn't support it...that sucks :( http://wiki.postgresql.org/wiki/Alter_column_position -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 13/10/2010 19:06, Carlos Mennens wrote: > On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens > <carlos.mennens@gmail.com> wrote: >> OK so I have read the docs and Google to try and find a way to add a >> new column to an existing table. My problem is I need this new column >> to be created 3rd rather than just dumping this new column to the end >> of my table. I can't find anywhere how I can insert my new column as >> the 3rd table column rather than the last (seventh). Does anyone know >> how I can accomplish this or if it's even possible. Seems like a >> common task but I checked the documentation and may have missed it in >> my reading. >> >> >> ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; > > Ah sadly I just found this after I pressed 'send' and realized > PostgreSQL doesn't support it...that sucks :( > > http://wiki.postgresql.org/wiki/Alter_column_position Why do you need to put it in a certain position anyway? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 13/10/2010 19:04, Carlos Mennens wrote:
> OK so I have read the docs and Google to try and find a way to add a
> new column to an existing table. My problem is I need this new column
> to be created 3rd rather than just dumping this new column to the end
> of my table. I can't find anywhere how I can insert my new column as
> the 3rd table column rather than the last (seventh). Does anyone know
> how I can accomplish this or if it's even possible. Seems like a
> common task but I checked the documentation and may have missed it in
> my reading.
>
It isn't possible at the moment. This has come up a good bit in the
past, so you'll find debate in the archives...
One work-around is to add the column, and then do:
create table new_table as
select [columns in desired order] from old_table;
drop table old_table;
alter table new_table rename to old_table;
...remembering to deal with foreign key constraints as you go.
HTH
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Or create view with your desired order on this table. Regards, Vijay ----- Original Message ----- From: Thomas Kellerer <spam_eater@gmx.net> Date: Thursday, October 14, 2010 3:09 pm Subject: Re: [GENERAL] Adding a New Column Specifically In a Table To: pgsql-general@postgresql.org > Carlos Mennens, 13.10.2010 20:06: > >> OK so I have read the docs and Google to try and find a way to > add a > >> new column to an existing table. My problem is I need this new > column>> to be created 3rd rather than just dumping this new > column to the end > >> of my table. I can't find anywhere how I can insert my new > column as > >> the 3rd table column rather than the last (seventh). Does > anyone know > >> how I can accomplish this or if it's even possible. Seems like a > >> common task but I checked the documentation and may have missed > it in > >> my reading. > >> > >> > >> ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; > > > > Ah sadly I just found this after I pressed 'send' and realized > > PostgreSQL doesn't support it...that sucks :( > > The position of a column in a table has no meaning whatsoever - > just like rows have no "position" as well. > > If you want columns returned in a specific order, simply put them > in the desired order in your SELECT statement. > > Thomas > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On 13 October 2010 23:19, Raymond O'Donnell <rod@iol.ie> wrote: > On 13/10/2010 19:04, Carlos Mennens wrote: >> >> OK so I have read the docs and Google to try and find a way to add a >> new column to an existing table. My problem is I need this new column >> to be created 3rd rather than just dumping this new column to the end >> of my table. I can't find anywhere how I can insert my new column as >> the 3rd table column rather than the last (seventh). Does anyone know >> how I can accomplish this or if it's even possible. Seems like a >> common task but I checked the documentation and may have missed it in >> my reading. >> > > It isn't possible at the moment. This has come up a good bit in the past, so > you'll find debate in the archives... > > One work-around is to add the column, and then do: > > create table new_table as > select [columns in desired order] from old_table; > > drop table old_table; > > alter table new_table rename to old_table; > > ...remembering to deal with foreign key constraints as you go. ..and indexes, triggers, rules, views and every other dependency. It's a potentially tricky solution to something which shouldn't really be a problem. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On 14/10/2010 11:32, Thom Brown wrote: > ..and indexes, triggers, rules, views and every other dependency. > It's a potentially tricky solution to something which shouldn't really > be a problem. Indeed - as others have said, depending on a specific column ordering in the database is asking for trouble. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 14 October 2010 12:07, Raymond O'Donnell <rod@iol.ie> wrote: > Indeed - as others have said, depending on a specific column ordering in the > database is asking for trouble. Yes, it certainly is (in fact, 1NF says that there should be no order to the columns), but it still annoys me that I cannot re-order columns on purely aesthetic grounds. -- Regards, Peter Geoghegan
On 14 October 2010 09:51, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote: > Dear Carlos, > > In old version of postgresql attnum colmn of pg_catalog.pg_attribute > system catalog > could be modified to get desired results. I am not sure if it should > be done anymore. That will only make the column names swap round, but then the data will appear as if it's coming from the wrong column. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On 10/14/2010 04:32 AM, Thom Brown wrote: > On 13 October 2010 23:19, Raymond O'Donnell <rod@iol.ie> wrote: >> On 13/10/2010 19:04, Carlos Mennens wrote: >>> >>> OK so I have read the docs and Google to try and find a way to add a >>> new column to an existing table. My problem is I need this new column >>> to be created 3rd rather than just dumping this new column to the end >>> of my table. I can't find anywhere how I can insert my new column as >>> the 3rd table column rather than the last (seventh). Does anyone know >>> how I can accomplish this or if it's even possible. Seems like a >>> common task but I checked the documentation and may have missed it in >>> my reading. >>> >> >> It isn't possible at the moment. This has come up a good bit in the past, so >> you'll find debate in the archives... >> >> One work-around is to add the column, and then do: >> >> create table new_table as >> select [columns in desired order] from old_table; >> >> drop table old_table; >> >> alter table new_table rename to old_table; >> >> ...remembering to deal with foreign key constraints as you go. > > ..and indexes, triggers, rules, views and every other dependency. > It's a potentially tricky solution to something which shouldn't really > be a problem. > True indeed. I suspect OP is stuck using some (crud-ie?) "platform" which is automagically producing the presentation so this approach (drop,recreate all involved) is likely to be the best bet. My guess is the number of users isn't huge (still setting schema, not adding employer_id, etc) so this approach would be a pretty quick and testable fix.
Think of it this way... A person has many properties... age, nationality, eye_color, weight, etc... Does it maks sense to put these properties in a particular "order" ? Neither does a relational DB require them to be in any order. The fact that "select *" consistently shows them in one particularorder is just a behavioral artifact of the software. It's not intentional, or guaranteed. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Peter Geoghegan Sent: Thursday, October 14, 2010 8:03 AM To: rod@iol.ie Cc: Thom Brown; Carlos Mennens; pgsql-general@postgresql.org Subject: Re: [GENERAL] Adding a New Column Specifically In a Table On 14 October 2010 12:07, Raymond O'Donnell <rod@iol.ie> wrote: > Indeed - as others have said, depending on a specific column ordering in the > database is asking for trouble. Yes, it certainly is (in fact, 1NF says that there should be no order to the columns), but it still annoys me that I cannot re-order columns on purely aesthetic grounds. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general