Is it possible to change the natural order of columns in Postgres?

I'm wanting the same. Yes, order isn't essential for my use-case, but it just rubs me the wrong way :)

What I'm doing to resolve it is as follows.

This method will ensure you KEEP any existing data,

1. Create a new version of the table using the ordering I want, using a temporary name.
2. Insert all data into that new table from the existing one.
3. Drop the old table.
4. Rename the new table to the "proper name" from "temporary name".
5. Re-add any indexes you previously had.
6. Reset ID sequence for primary key increments.

Current table order:

id, name, email

1. Create a new version of the table using the ordering I want, using a temporary name.

In this example, I want email to be before name.

CREATE TABLE mytable_tmp
(
  id SERIAL PRIMARY KEY,
  email text,
  name text
);

2. Insert all data into that new table from the existing one.

INSERT INTO mytable_tmp   --- << new tmp table
(
  id
, email
, name
)
SELECT
  id
, email
, name
FROM mytable;  --- << this is the existing table

3. Drop the old table.

DROP TABLE mytable;

4. Rename the new table to the "proper name" from "temporary name".

ALTER TABLE mytable_tmp RENAME TO mytable;

5. Re-add any indexes you previously had.

CREATE INDEX ...

6. Reset ID sequence for primary key increments.

SELECT setval('public.mytable_id_seq', max(id)) FROM mytable;

I have asked that question in pgsql-admin in 2007. Tom Lane himself declared it practically unfeasible to change the order in the catalogs.

• http://archives.postgresql.org/pgsql-admin/2007-06/msg00037.php

Clarification: this applies for users with the present tools. Does not mean, it could not be implemented. IMO, it should be.
Still true for Postgres 12.

If your database is not very big and you can afford some downtime then you can:

1. Disable write access to the database
   this is essential as otherwise any changes after starting the next point will be lost
2. pg_dump --create --column-inserts databasename > databasename.pgdump.sql
3. Edit apropriate CREATE TABLE statement in databasename.pgdump.sql
   If the file is too big for your editor just split it using split command, edit, then assemble back using cat
4. drop database databasename
   You do have a recent backup, just in case, do you?
5. psql --single-transaction -f databasename.pgdump.sql
   If you don't use --single-transaction it will be very slow

If you use so called large objects make sure they are included in the dump. I'm not sure if they are by default in 8.1.

You can actually just straight up change the column order, but I'd hardly recommend it, and you should be very careful if you decide to do it.

eg.

# CREATE TABLE test (a int, b int, c int);
# INSERT INTO test VALUES (1,2,3);
# SELECT * FROM test;
 a | b | c 
---+---+---
 1 | 2 | 3
(1 row)

Now for the tricky bit, you need to connect to your database using the postgres user so you can modify the system tables.

# SELECT relname, relfilenode FROM pg_class WHERE relname='test';
 relname | relfilenode 
---------+-------------
 test_t  |       27666
(1 row)

# SELECT attrelid, attname, attnum FROM pg_attribute WHERE attrelid=27666;
 attrelid | attname  | attnum 
----------+----------+--------
    27666 | tableoid |     -7
    27666 | cmax     |     -6
    27666 | xmax     |     -5
    27666 | cmin     |     -4
    27666 | xmin     |     -3
    27666 | ctid     |     -1
    27666 | b        |      1
    27666 | a        |      2
    27666 | c        |      3
(9 rows)

attnum is a unique column, so you need to use a temporary value when you're modifying the column numbers as such:

# UPDATE pg_attribute SET attnum=4 WHERE attname='a' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=1 WHERE attname='b' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=2 WHERE attname='a' AND attrelid=27666;
UPDATE 1

# SELECT * FROM test;
 b | a | c 
---+---+---
 1 | 2 | 3
(1 row)

Again, because this is playing around with database system tables, use extreme caution if you feel you really need to do this.

This is working as of postgres 8.3, with prior versions, your milage may vary.