75

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
  • 4
    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
  • 2
    @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
  • 2
    @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
54

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.

22

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.

2

If you want this just for looks, I find it easier to keep a view per each table with desired order of columns, and select from it instead of the table.

create table my_table (
create view view_my_table as
  select id, name, created_date from my_table;

-- adding a new column
begin;
alter table my_table add column email varchar(255);
drop view view_my_table;
create view view_my_table as
  select id, name, email, created_date from my_table;
commit;

For all other purposes (like insert, union) it is better to always specify the column list.

-- bad
insert into my_table values (...);
(select * from my_table)
  union all
(select * from my_table);

-- good
insert into my_table (id, name, email, created_date) values (...);
(select id, name, email, created_date from my_table)
  union all
(select id, name, email, created_date from my_table);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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