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.

There is no need to do that. Just put them into your select statement in the order you want them.
–
a_horse_with_no_nameJun 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 *
–
xenoterracideJun 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.
–
JonasJun 12 '11 at 16:22

2

@jonas you can define column names when doing inserts and updates and thus make the order irrelevant.
–
xenoterracideJun 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

2 Answers
2

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.

That's right.. but does anyone really use this method? :-)
–
MarianJun 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