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.
    – user1822
    Jun 12, 2011 at 16:00
  • 4
    @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, 2011 at 16:22
  • 2
    @jonas you can define column names when doing inserts and updates and thus make the order irrelevant. Jun 12, 2011 at 16:38
  • 1
    @xeno: Yes. But the problem is when I list the columns using \d mytable in psql and check with my fields in my source code e.g. both in Java and JavaScript.
    – Jonas
    Jun 12, 2011 at 16:45
  • 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) Jun 13, 2011 at 8:06

3 Answers 3


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.

  • 30
  • 6
    That's right.. but does anyone really use this method? :-)
    – Marian
    Jun 15, 2011 at 7:25
  • 5
    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. Jun 15, 2011 at 8:49
  • 2
    @JackDouglas and 8 years later I'm in a position where I might be forced to use this exact method! Love you, man!
    – Marian
    Jan 9, 2020 at 15:02
  • 3
    I guess I have mixed feelings about this still being useful! It looks like the latest effort to make progress on this was in 2015 in case you are interested. Jan 9, 2020 at 18:10

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.


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

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 and acknowledge you have read our privacy policy.

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