Is it possible to change the column order of a table in postgresql/postgis 2.0? If so, how is it done?

  • 1
    What benefit would it bring to change the column order? – AndreJ Sep 12 '13 at 10:59

This type of thing isn't directly supported with PostgreSQL. However, there are many tips on how to alter the column positions.


PostgresSQL/PostGIS is a relational database management system ( RDBMS) and the original order of the columns have no significance. If you want to change the order in which they appear, use SQL:

select Field2, Field3, Field1 from table;
select Field1, Field3, Field2 from table;

And if you want to keep this ordered result as a table in the database, use SQL Views;

create view "my_order" as
select Field1, Field3, Field2 from table;

or create new table:

create table "my_neworder" as
select Field2, Field3, Field1 from table;

And with PostGIS, use spatial views: How to make a spatial view in PostGIS and add it as a layer in QGIS?

  • 2
    +1 for explaining that the order of columns (and rows) does not have any significance at all. I wish more people understood this. – Devdatta Tengshe Sep 12 '13 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.