Can PostgreSQL Really Change Column Order?

Modifying Column Position in PostgreSQL: Exploring Limitations and Alternatives

The PostgreSQL database may seem inflexible when it comes to adjusting the position of columns in a table. Despite attempts like the one you mentioned ("ALTER TABLE person ALTER COLUMN dob POSITION 37;"), modifying the physical location of columns through such commands remains impossible.

According to the PostgreSQL Wiki's "Alter column position" page, amending the order of columns relies on the attnum value in the pg_attribute table. Any changes necessitate either table recreation or complex data manipulation until the desired arrangement is attained.

As paradoxical as it may seem, the SQL standard itself doesn't provide a means to reposition columns. Database vendors that offer such functionality are essentially extending the SQL syntax.

It's crucial to note that manipulating column order can have performance implications, as database access paths may be optimized based on the existing column layout.

While the ability to reposition columns may not be inherently supported, you can work around this limitation by creating a VIEW. By defining a VIEW, you can specify the order of columns to your preference without altering the base table's underlying structure.

The above is the detailed content of Can PostgreSQL Really Change Column Order?. For more information, please follow other related articles on the PHP Chinese website!