PostgreSQL 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. ---- CREATE VIEW original_tab_vw AS SELECT a.col1, a.col3, a.col4, a.col2 FROM original_tab a WHERE a.col1 IS NOT NULL --or whatever
Here is what the above code is Doing:
1. Create a new table with the desired column order.
2. Create a view that selects the columns in the desired order from the original table.
3. Insert the data from the view into the new table.
4. Drop the original table.
5. Rename the new table to the original table’s name.
6. Drop the view.