Reorder PostgreSQL tables to match a target structure
When no columns are entered, the table's current columns are printed and the script returns.
Columns can be any number of arguments. When entered as "col1 col2 col3" the listed
columns will be placed at the start of the table. When entered as
"col1 col2 ... col3" the first two columns will be placed at the start of the table,
and the last column will be placed at the end of the table. When entered as
"... col1 col2 col3" all three columns will be placed at the end of the table.
-e, --exclude TEXT Exclude a column (can be used multiple times).
-d, --database TEXT The name of the database.
-n, --schema TEXT The schema of the target table.
-u, --user TEXT User name.
-p, --password TEXT Password.
-m, --migrate Output full migration sql.
-f, --file FILENAME Write output into a file.
-h, --help Show this message and exit.
Say we have the following table, books:
| author | year_published | title | id |
|---|---|---|---|
| Mark Z. Danielewski | 2000 | House of Leaves | 1 |
| Harper Lee | 1960 | To Kill a Mockingbird | 2 |
| Douglas Adams | 1979 | Hitchhiker's Guide to the Galaxy | 3 |
We want to move the id column to the start and the year_published column to the end. Simply run the following to get the query that will rearrange the table for you:
./reorder.py books id ... year_publishedTo run the sql, you can copy and paste it into a query, pipe it directly into the psql command, or save it to a file with the --file argument. Running the sql will result in the following table:
| id | author | title | year_published |
|---|---|---|---|
| 1 | Mark Z. Danielewski | House of Leaves | 2000 |
| 2 | Harper Lee | To Kill a Mockingbird | 1960 |
| 3 | Douglas Adams | Hitchhiker's Guide to the Galaxy | 1979 |
Script is designed to run on the same host as the database.
Special thanks to JiCiT for adding windows support and username/password options