Thread: changing a column's position in table, how do you do that

Is there a way to change the position attribute of a column in a table? 
I have data that I need to import into various tables in my db on a 
consistent basis... I usually us e the COPY ... FROM query but I can't 
control the -order- of the fields my client dumps the data so I would 
like to be able to change the position the columns in my table to be 
able to better align the data to be imported with the format of my 
table. I was thinking I could do something like "ALTER TABLE ALTER 
COLUMN ... " or something like that to change the columns position in 
the table but I can't figure out how.

Ferindo

Ferindo,

> Is there a way to change the position attribute of a column
> in a table?

AFAIK, there's no way to change this easily. The best way to do it would
be as follows:

BEGIN WORK;
LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE;
ALTER TABLE mytable ADD COLUMN col_to_move_2 coltype;
UPDATE mytable SET col_to_move_2 = col_to_move;
ALTER TABLE mytable DROP COLUMN col_to_move;
ALTER TABLE mytable RENAME col_to_move_2 TO col_to_move;
COMMIT WORK;

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/