postgres reorder columns 1

postgres reorder columns

    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.

Similar Posts