Can I change the natural order of columns in Postgres?

Question

Can I change the natural order of columns in Postgres 8.1?

I know that you should not rely on the order of the columns - this is not important for what I am doing - I only need some automatically generated things to come out in such a way that it would be more pleasant, so that the field order matches the whole path from pgadmin through the posterior end to the anterior end.

Answer 1

You can actually just reorder the columns, but I would hardly recommend it, and you have to be very careful if you decide to do this.

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 a complex bit, you need to connect to the database using the postgres user so that 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 |  -four
     27666 |  xmin |  -3
     27666 |  ctid |  -one
     27666 |  b |  one
     27666 |  a |  2
     27666 |  c |  3
 (9 rows)

attnum is a unique column, so you need to use a temporary value when you change 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, since this plays with database system tables, use extreme caution if you feel that you really need to do this.

This works with postgres 8.3 with previous versions, your movement may vary.

Answer 2

If your database is not very large and you can afford some downtime, you can:

• Disable write access to the database
  this is necessary, because otherwise, any changes after starting the next item will be lost.
• pg_dump --create --column-inserts databasename > databasename.pgdump.sql
• Edit the appropriate CREATE TABLE statement in databasename.pgdump.sql
  If the file is too large for your editor, just split it with the split command, edit it and then build it back with cat
• drop database databasename
  You have the latest backup, just in case, you?
• psql --single-transaction -f databasename.pgdump.sql
  If you do not use --single-transaction , it will be very slow.

If you use the so-called large objects, make sure that they are included in the dump. I am not sure if they are by default in 8.1.

Answer 3

I asked this question in pgsql-admin in 2007. Tom Lane himself stated that it is practically impossible to reorder catalogs. Clarification: .. for users with real tools (still true for v9.1). This does not mean that it is impossible to implement. IMO, this should be.
http://archives.postgresql.org/pgsql-admin/2007-06/msg00037.php

Answer 4

As mentioned in other answers, you cannot reorder columns, right down to postgres. You can (and should!) Solve your problem in terms of. For the purposes of your report request, it will look just like a table. Something like:

 create view my_view as select * from my_table order by some_col; 

Answer 5

Specifying the column order in a query is the only reliable way (and reasonable) . However, you can usually get a different order by modifying the table, as shown in the example below, since columns are usually (not guaranteed) returned in the order in which they were added to the table.

 postgres=# create table a(a int, b int, c int); CREATE TABLE postgres=# insert into a values (1,2,3); INSERT 0 1 postgres=# select * from a; a | b | c ---+---+--- 1 | 2 | 3 (1 row) postgres=# alter table a add column a2 int; ALTER TABLE postgres=# select * from a; a | b | c | a2 ---+---+---+---- 1 | 2 | 3 | (1 row) postgres=# update a set a2 = a; UPDATE 1 postgres=# alter table a drop column a; ALTER TABLE postgres=# alter table a rename column a2 to a; ALTER TABLE postgres=# select * from a; b | c | a ---+---+--- 2 | 3 | 1 (1 row) postgres=# 

Answer 6

Unfortunately, no, this is not so. The order of the columns depends entirely on Postgres.

Answer 7

You can get the ordered ordering of the columns by creating a new table and selecting the columns of the old table in the order you want:

 CREATE TABLE test_new AS SELECT b, c, a FROM test; SELECT * from test_new; b | c | a ---+---+--- 2 | 3 | 1 (1 row) 

Please note that this only copies data, not modifiers, restrictions, indexes, etc.

Once the new table is changed the way you want, discard the original and change the name of the new one:

 BEGIN; DROP TABLE test; ALTER TABLE test_new RENAME TO test; COMMIT;