Re: [SQL] changing a column's position in table, how do you do that
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] changing a column's position in table, how do you do that
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:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] changing a column's position in table, how do you do that
[EMAIL PROTECTED] (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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
