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

Question

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

I know that you shouldn't rely on column order - it's not essential to what I am doing - I only need it to make some auto-generated stuff come out in a way that is more pleasing, so that the field order matches all the way from pgadmin through the back end and out to the front end.

Answer 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.

Answer 2

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.

Answer 3

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.

Answer 4

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;

Answer 5

Reorder the columns in postgresql walkthrough

Warning: this approach deletes table properties such as unique indexes and other unintended consequences that come with doing a drop your_table. So you'll need to add those back on after.

--create a table where column bar comes before column baz:
CREATE TABLE foo ( moo integer, bar character varying(10), baz date ); 

--insert some data
insert into foo (moo, bar, baz) values (34, 'yadz', now()); 
insert into foo (moo, bar, baz) values (12, 'blerp', now()); 
select * from foo; 
    ┌─────┬───────┬────────────┐ 
    │ moo │  bar  │    baz     │ 
    ├─────┼───────┼────────────┤ 
    │  34 │ yadz  │ 2021-04-07 │ 
    │  12 │ blerp │ 2021-04-07 │ 
    └─────┴───────┴────────────┘ 

-- Define your reordered columns here, don't forget one, 
-- or it'll be missing from the replacement.
drop view if exists my_view;
create view my_view as ( select moo, baz, bar from foo );
select * from my_view; 

DROP TABLE IF EXISTS foo2; 
--foo2 is your replacement table that has columns ordered correctly
create table foo2 as select * from my_view; 
select * from foo2;
--finally drop the view and the original table and rename
DROP VIEW my_view; 
DROP TABLE foo; 
ALTER TABLE foo2 RENAME TO foo; 

--observe the reordered columns:
select * from foo;
    ┌─────┬────────────┬───────┐ 
    │ moo │    baz     │  bar  │ 
    ├─────┼────────────┼───────┤ 
    │  34 │ 2021-04-07 │ yadz  │ 
    │  12 │ 2021-04-07 │ blerp │ 
    └─────┴────────────┴───────┘ 

Get the prior order of column names for copying and pasting

If your table you want to reorder has hundreds of columns, you'll want to automate the getting of the given order of columns so you can copy, nudge, then paste into the above views.

SELECT string_agg(column_name, ',') from ( 
    select * FROM INFORMATION_SCHEMA.COLUMNS  
    WHERE table_name = 'your_big_table'  
    order by ordinal_position asc 
) f1;

Which prints:

column_name_1,column_name_2, ..., column_name_n

You copy the above named ordering, you move them to where they belong then paste into the view up top.

Answer 6

You can get the column ordering that you want by creating a new table and selecting columns of the old table in the order that you want them to present:

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

Note that this copies data only, not modifiers, constraints, indexes, etc..

Once the new table is modified the way you want, drop the original and alter the name of the new one:

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

Answer 7

Unfortunately, no, it's not. Column order is entirely up to Postgres.

Answer 8

Specifying the column order in the query is the only reliable (and sane) way. That said, you can usually get a different ordering by altering the table as shown in the example below as the columns are usually (not guaranteed to be) returned in the order 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=#