PostgreSQL - Is it possible to alter the column order (position) in a table?

The answer is no. You can’t change the column order.

Currently, PostgreSQL doesn’t support altering column position within a table. PostgreSQL defines column order based on the attnum column of the pg_attribute table and you can’t change it.

There are still some workarounds to make it possible, just not the official solution:

1. Recreating the whole table

This is the best way to achieve the desired order. You can create a new table with the column position you want, move all data from the current table to that new table, then drop the old table.

By doing this, you also have to recreate everything referenced to the old table such as views, indexes, triggers, etc, to make sure that the new table schema matches with the old one. Otherwise, you will run into a lot of dependency issues when it’s not done thoroughly.

2. Create new columns within current table

If the table schema is complicated and recreating it would be troublesome, you can create new columns by desired order, move data from the current columns to the new columns, then drop the old columns. This way, you can still keep the current table. Just remember to update views, triggers, indexes if required too.

3. Create a view

The same case with above, if recreating table is somewhat troublesome, you can create a new instead. This method doesn’t change anything of the table, it just create a new way of displaying data which you can customize and reorder the way you want.

Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.

TablePlus