How do I alter the position of a column in a PostgreSQL database table?

sql database postgresql alter-table
Huuuze picture Huuuze · Nov 13, 2008 · Viewed 130.4k times · Source

I've tried the following, but I was unsuccessful:

ALTER TABLE person ALTER COLUMN dob POSITION 37;

Answer

Bill Karwin picture Bill Karwin · Nov 13, 2008

"Alter column position" in the PostgreSQL Wiki says:

PostgreSQL currently defines column order based on the attnum column of the pg_attribute table. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout.

That's pretty weak, but in their defense, in standard SQL, there is no solution for repositioning a column either. Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax.

One other idea occurs to me: you can define a VIEW that specifies the order of columns how you like it, without changing the physical position of the column in the base table.

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.…

Read More
Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),…

Read More