3 ms·

Unfortunately, the only way to change column order is to either recreate the table, or add new columns and move data. https://wiki.postgresql.org/wiki/Alter_co

by onnnon 4y ago

Unfortunately, the only way to change column order is to either recreate the table, or add new columns and move data. https://wiki.postgresql.org/wiki/Alter_column_position https://wiki.postgresql.org/wiki/Alter_column_position

pvillano 4y ago
> 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.
- pvillano 4y ago
  The hard part aside... Some cool optimizations are possible if the size of each row doesn't change. * Rows could be done in parallel. * The simplest method would be to copy the row to a scratch buffer, and then copy over data to their final position. * Furthermore, with some fancy permutation, you only need a register of scratch space. Although, with more random memory access, that could result in worse performance.
uhoh-itsmaciek 4y ago
For what it's worth, there is currently some active work in this area [1]. It's too late for 15, but it might land in 16 (next fall). [1]: https://www.postgresql.org/message-id/flat/20220628083230.c4qhp4fdp3dp22ig%40jrouhaud https://www.postgresql.org/message-id/flat/20220628083230.c4...
- terandle 4y ago
  Not sure they really need to complicate the system with different physical/logical identifiers. Just an easy way to move physical columns around if you're willing to do a full table lock would be nice.
- mekster 4y ago
  Finally? I'm about to move off from PostgreSQL back to MySQL primarily for this reason. After a period of development, the ordering becomes a mess by keep adding columns to the end with no logical grouping and it's a pain to view the data that way.

by onnnon 4y ago

pvillano 4y ago

> 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.

pvillano 4y ago
The hard part aside... Some cool optimizations are possible if the size of each row doesn't change. * Rows could be done in parallel. * The simplest method would be to copy the row to a scratch buffer, and then copy over data to their final position. * Furthermore, with some fancy permutation, you only need a register of scratch space. Although, with more random memory access, that could result in worse performance.

uhoh-itsmaciek 4y ago

For what it's worth, there is currently some active work in this area [1]. It's too late for 15, but it might land in 16 (next fall). [1]: https://www.postgresql.org/message-id/flat/20220628083230.c4qhp4fdp3dp22ig%40jrouhaud https://www.postgresql.org/message-id/flat/20220628083230.c4...

terandle 4y ago
Not sure they really need to complicate the system with different physical/logical identifiers. Just an easy way to move physical columns around if you're willing to do a full table lock would be nice.
mekster 4y ago
Finally? I'm about to move off from PostgreSQL back to MySQL primarily for this reason. After a period of development, the ordering becomes a mess by keep adding columns to the end with no logical grouping and it's a pain to view the data that way.