Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

If I have a table with the columns: 

id | name | created_date

and would like to add a column, I use:

alter table my_table add column email varchar(255)

Then the column is added after the created_date column.

Is there any way I can specify the position for the new column? e.g. so I can add it after name and get a table like:

id | name | email | created_date
share|improve this question
2  
There is no need to do that. Just put them into your select statement in the order you want them. – a_horse_with_no_name Jun 12 '11 at 16:00
@jonas so you could also create a view that show's it in that order... technically the position of the column shouldn't matter as you can define them in any order in a query... and you generally shouldn't be doing a select * – xenoterracide Jun 12 '11 at 16:00
@a_horse: Well, it's much harder to work (as a developer/admin) with the tables when the columns has many different orders. When I have 15 columns in a table I really prefer to have them in the same order in all databases. – Jonas Jun 12 '11 at 16:22
2  
@jonas you can define column names when doing inserts and updates and thus make the order irrelevant. – xenoterracide Jun 12 '11 at 16:38
1  
@Jonas: Then write your own alternative to \d that reports the columns in the order you want (it is only a query on the system tables: try using the psql -E switch to see the actual query) – Jack Douglas♦ Jun 13 '11 at 8:06
show 3 more comments

2 Answers

up vote 4 down vote accepted

ALTER TABLE ADD COLUMN will only add the new column at the end, as the last one. In order to create a new column in another position you need to recreate the table and copy the data from the old/current table in this new table.

share|improve this answer
2  
1  
That's right.. but does anyone really use this method? :-) – Marian Jun 15 '11 at 7:25
1  
If it is easier than recreating the table and all children tables, foreign keys and grants :) I thought the link might be useful either way as it offers an explanation of why you can't specify position and hope that it might be implemented in a future version. – Jack Douglas♦ Jun 15 '11 at 8:49
up vote 0 down vote

You'll need to recreate the table if you want a certain order. Just do something like:

alter table tablename rename to oldtable;
create table tablename (column defs go here);
insert into tablename (col1, col2, col3) select col2, col1, col3 from oldtable;

Create indexes as needed etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.