0

I have a table with some columns and I would like to change the order of the columns akin to the following example

SomeTable

columnId, column1, column2, column3 etc...

I have tried the following syntax:

ALTER TABLE some_table
    ORDER BY id, column2, column3, column1;

ALTER TABLE table_name 
    MODIFY COLUMN column_name1 data_type AFTER column_name2;

I am using postgresql server 15. I want to execute the changes through liquibase migration.

I could drop the table and redo the sql in the desired order, but this seems overkill for what it achieves.

Now, I get that this is a small gripe as it is almost inconsequential beyond some admittedly minor human ergonomic improvements, but, tbh for me it is more a case of professional curiosity. How is it that it isn't possible.

According to this article this is basically impossible.

However! This article is last edited 8 years ago and there has been consistent interest in this functionality for over 20 years - as explained in the article itself. So, if this functionality truly has not been implemented, there is perhaps a significant hurdle that disables the functionality or renders it's implementation not worthwhile.

So at this point while finding out how to do it effectively is still something I am after, underlying knowledge on the "Why" is perhaps even more intriguing.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stefan Stefanov
  • 3
  • 3
  • 1
    You can't do that. As to why, it is not supported. Searching [pgsql-hackers](https://www.postgresql.org/list/pgsql-hackers/) may get you the reasoning. – Adrian Klaver Mar 08 '23 at 15:56
  • You WILL NOT *find out how to do it effectively*. **By design** in the relational model order of columns is irrelevant; you access them by name not position. Image 2 applications using the same data but a different presentation layout of that data. If you want/need a specific column order for presentation then create a view having that column sequence, or as many views as you want. – Belayer Mar 08 '23 at 19:03

0 Answers0