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

On Sep 29, 2005, at 4:38 , Daryl Richter wrote:

> Ferindo Middleton Jr wrote:
>
>> 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
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
> Constantly changing the column order of a table doesn't make sense  
> (and is a pain!).
>
> I would suggest setting up a temporary staging table with generic  
> column names to take in the input data and then using SQL to move  
> it into its permanent location.

Or specify the columns directly in the copy statement. Instead of  
copy foo from <file>, do copy foo (bar,baz,bat) from <file>. Then you  
only have to change the copy statement.

While SQL does allow columns to be referenced by column order, it's  
far, far better to consider the columns of a table to be unordered  
and (nearly) always specify your columns explicitly. It gives you  
much more flexibility. Changing a few SQL statements to return (or  
insert) the columns how you want them is much easier than changing  
the underlying table structure and having to make sure *all* of your  
queries then fit the new table structure.

Michael Glaesemann
grzm myrealbox com