4

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

PyMapr
  • 1,630
  • 1
  • 16
  • 31

2 Answers2

9

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?

Community
  • 1
gene
  • 54,868
  • 3
  • 110
  • 187
  • 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
3

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

Mike T
  • 42,095
  • 10
  • 126
  • 187