StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>eg.</p> <pre> # 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) </pre> <p>Now for the tricky bit, you need to connect to your database using the postgres user so you can modify the system tables.</p> <pre> # 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) </pre> <p>attnum is a unique column, so you need to use a temporary value when you're modifying the column numbers as such:</p> <pre> # 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) </pre> <p>Again, because this is playing around with database system tables, use extreme caution if you feel you really need to do this.</p> <p>This is working as of postgres 8.3, with prior versions, your milage may vary.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload