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=20
> in a table?=20

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 =3D 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/=20

fmiddleton@verizon.net (Ferindo Middleton Jr) writes:
> 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.

If you're running PostgreSQL 7.4 or later, you can specify field names
in the COPY statement.  That strikes me as a better way to control
this.
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
Rules of the Evil Overlord #65.  "If I must have computer systems with
publically available  terminals, the maps  they display of  my complex
will have  a room clearly marked  as the Main Control  Room. That room
will be  the Execution Chamber. The  actual main control  room will be
marked as Sewage Overflow Containment." <http://www.eviloverlord.com/>

fmiddleton@verizon.net (Ferindo Middleton Jr) writes:
> 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.

If you're running PostgreSQL 7.4 or later, you can specify field names
in the COPY statement.  That strikes me as a better way to control
this.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
Rules of the Evil Overlord #65.  "If I must have computer systems with
publically available  terminals, the maps  they display of  my complex
will have  a room clearly marked  as the Main Control  Room. That room
will be  the Execution Chamber. The  actual main control  room will be
marked as Sewage Overflow Containment." <http://www.eviloverlord.com/>

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.

-- 
Daryl

"We want great men who, when fortune frowns, will not be discouraged."    -- Colonel Henry Knox, 1776

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