I was following the posts from a month or two ago about reordering
columns using syntax similar to MySQL's:
ALTER TABLE tbl ALTER COL col1 AFTER/BEFORE col2;
I have been working on a new project that adds some functionality to
an existing database schema, which caused some tables to expand with
new columns. As I was adding columns to these tables on my dev
server I was updating the orignal CREATE TABLE install script placing
the new columns next to existing columns where they made sense to be
(as opposed to dumping them all on the end) - so I wound up with
something like:
CREATE TABLE newtable (
oldcol1 INT
newcol1 TEXT
oldcol2 INT
oldcol3 TEXT
newcol2 INT
etc
)
This is fine, but I had a problem when I went to copy the database
from the test server to a production server that had a fresh install
using the CREATE TABLE above. I only needed to copy data from
certain tables from the old data to the new and I couldn't use PG
Dump because I had to preserve what was already there. Long story
short, I had a horrible time because COPY TABLE copied the data in
the order of the columns I had on the dev server which didn't match
the order on the production server. Both servers are running 8.1.
A couple things would have saved me lots of time and head scratching:
Being able to execute COPY TABLE where table name is a view - not
sure why this isn't possible.
Being able to COPY TABLE with the result of a SELECT command where I
can specify the column order (I think this is in 8.3, great feature)
Being able to alter the table to re-order the columns.
I also just thought of another scenario, I could've created a temp
table from a SELECT with the correct column order - wish I had
thought of that before writing this email.
Anyway, sorry to beat a dead horse, but I do believe this is a valid
use-case for being able to alter the column order in a table,
something I was opposed to before. :)
Josh