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

by

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.

More Related Contents:

  1. How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
  2. Generating time series between two dates in PostgreSQL
  3. ‘password authentication failed for user “postgres”‘
  4. PostgreSQL error: Fatal: role “username” does not exist
  5. How to do an update + join in PostgreSQL?
  6. Store common query as column?
  7. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  8. How to drop a PostgreSQL database if there are active connections to it?
  9. See and clear Postgres caches/buffers?
  10. “psql: could not connect to server: Connection refused” Error when connecting to remote database
  11. postgresql duplicate key violates unique constraint
  12. ClassNotFoundException with PostgreSQL and JDBC
  13. gem install pg –with-pg-config works, bundle fails
  14. How to get function parameter lists (so I can drop a function)
  15. How to convert a postgres database to sqlite
  16. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  17. Error when setting n_distinct using a plpgsql variable
  18. How can I change a PostgreSQL user password?
  19. Docker wait for postgresql to be running
  20. I forgot the password I entered during PostgreSQL installation
  21. Function to loop through and select data from multiple tables
  22. SQL query column does not exist error
  23. Switch role after connecting to database
  24. SQL Sub queries in check constraint
  25. When do Postgres column or table names need quotes and when don’t they?
  26. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  27. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  28. PostgreSQL how to concat interval value ‘2 days’
  29. Displaying image in Ireports using PostgreSql
  30. Orbeon Forms Postgres DB connection

Leave a Comment