Alter column position

From PostgreSQL wiki

Many people new to postgresql often ask if it has support for altering column positions within a table. Currently it does not; if you want to change column positions, you must either recreate the table, or add new columns and move data. The idea of allowing re-ordering of column position is not one the postgresql developers are against, it is more a case where no one has stepped forward to do the work. The rest of this document is meant to explain the workarounds, and track the issues that must be dealt with should someone want to implement this functionality.

There are two main reasons why being able to alter column position would be useful within postgres

physical layout can be optimized by putting fixed size columns at the start of the table

ordering columns can make working with a table easier, either by putting result sets in an order that is visually appealing, or by grouping columns based on similar function within a table.

Postgres currently defines column order based on the attnum column of the pg_attribute table. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout.

Contents

Alter column workarounds

Recreate the table

In our first example, we have a table named film_actor, which contains three columns, an actor_id, a film_id, and a last_update column. We have noticed that many developers often confuse which id is the films and which is the actors, so to eliminate this, we want to change the order of the columns.

If we were to continue, we would need to recreate the views referenced above to reference our new version of the table. Also don't forget, we would also need to recreate the missing indexes on our new table, and recreate the trigger, and then rename all of the tables / indexes / etc... to ensure our final schema matches the previous schema. While you can do all of this work in a transaction, it is important to be thorough about the work, as you can run into a lot of dependency issues.

Add columns and move data

Sometimes it is too much trouble to recreate a table, so instead you can use the add column and move data approach. In this example, we have an actor with id, first and last name column, and alast_updated column. We are going to be adding a middle_name column into our table, and we would like to add it between the first_name and last_name columns.

To do this, we will first add the middle name column (which will go onto the end), then add a new last_name column, and another new last_update column, update the respective columns, and then drop the old columns.

Again, before we completed this change, we would need to recreate the above views, and deal with any indexes, triggers, or other dependencies that might arise. It is also important that the update will cause all rows in the table to be rewritten, which will then require maintenance (most likely some for of cluster/reindex based on your table)

Hide the differences with a view

for completeness, there is another method for changing table order, which is to hide the changes with a view. The basic idea is you add any new columns (if you're adding a new column), rename the table, and create a view with the old table name that contains your desired column order. As of PostgreSQL 9.3, this view should act just like an ordinary table with regard to writes. While this works for logical column re-ordering, if you want to re-order to optimize physical layout, you would still need to use one of the above reasons.

Adding alter column syntax into postgres

Since the above methods have a number of issues, it has often been expressed that we would like to add capabilities for postgres to allow reordering of columns. The flip side of this is that it would also be desirable for postgres to automatically order columns physically for optimum layout, regardless of the logical order they are given in. The current problem with implementing this lies in that currently postgres uses the same identifiers for both the logical and physical position within a table. The current hot plan for solving this would be to change the system to reference three identifiers... a permanent identifier for the column, as well as a separate logical and physical identifier. This would allow places that need to deal specifically with column order at the logical level (ie. select *) to reference the logical number, while places that interact with disk system can access the physical number, and all other places just use the column's permanent id.

The other bit is that we would also need to determine syntax for how users would interact with this new functionality. The most common implementations typically use a set of BEFORE/AFTER keywords, followed by an existing column name.