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

There is no need to do that. Just put them into your select statement in the order you want them. — , 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
@xeno: ah, a view would be a solution, but it's just another thing to keep updated :( The column order keeps me away from doing errors (miss columns) when I do software development. — Jonas, Jun 12 '11 at 16:24
@jonas you can define column names when doing inserts and updates and thus make the order irrelevant. — xenoterracide, Jun 12 '11 at 16:38
@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 '11 at 16:45
@xeno: Another example is that I often have a column created of type Timestamp. This is a kind of meta-column, and it helps if that is the last column in every table, even after adding columns to the table. — Jonas, Jun 12 '11 at 16:48
@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 08:06
@a_horse_with_no_name While it has no meaning in the SQL, where the tables and the query results are sets of records, it still might have a high importance on a software design sense. Furthermore, typically the order of the columns on the deep-level also exists and it might have an importance. To me, this rigidity is a serious disadvantage of the postgresql. — peterh, May 07 '19 at 12:25
@peterh: well, the only main stream DBMS that supports this is MySQL/MariaDB and I will always trade the many advantages of Postgres over the possibility to change the position of a column. — , May 07 '19 at 12:28
@a_horse_with_no_name I fully agree with that, I do the same. It is not a big problem that making such a reorder requires multiple DDL queries, in exchange what... troubles can we have with other SQL engines which practically never happen with PostgreSQL. — peterh, May 07 '19 at 12:41

score 113 · Accepted Answer · edited Mar 20 '13 at 14:27

113

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.

edited Mar 20 '13 at 14:27

Daniel Serodio

1,249
3
12
13

answered Jun 12 '11 at 14:14

Marian

15,531
2
60
74

29

"or by adding columns and rotating data until you reach the desired layout" – Jack Douglas Jun 13 '11 at 08:08
6

That's right.. but does anyone really use this method? :-) – Marian Jun 15 '11 at 07: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. – Jack Douglas Jun 15 '11 at 08: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 09 '20 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. – Jack Douglas Jan 09 '20 at 18:10
2

@JackDouglas - It would still think it's useful. I found this thread just now as I'm prototyping a couple new tables, and would like the columns to appear the way I'm thinking about them ("Oh - I see I'll need an extra column 'here'"). And in quick prototyping, I don't want to do a long select query or specify the column order in an INSERT. Also, a natural or intuitive column order from a simple \d can go a long way towards "self documentation" for someone else trying to pick up the database - maybe even a later You! :-) – Randall Sep 27 '21 at 14:45
1

In INFORMIX you add "BEFORE <column_name>" at the end of the ALTER TABLE statement. – Guasqueño Mar 09 '23 at 20:56

score 36 · Answer 2 · answered Sep 26 '11 at 20:27

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.

score 9 · Answer 3 · answered Dec 26 '18 at 17:01

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

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

3 Answers3