How can I specify the position for a new column in PostgreSQL?

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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