Обсуждение: 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