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

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/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

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

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

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

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 a specific 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

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:

Dear Carlos,

application code should not depend on column positions.
the requirement is not good.

regds
rajesh kumar mallah.

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

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

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 particular order 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

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.