Обсуждение: Preserving column order when recreating table.
Hi all, I'm struggling with a situation where I want to recreate a table (in more than 30 databases) to fix the column order (attnum sequence) and in another case, fix different definitions for the same column in a table e.g. amount numeric(16,2) in stead of : amount numeric(16,5) The complication comes in when the newly created table could have extra (new) columns, or its column order is not the same as the original table's, but the corresponding column names are the same. My question is : Is it possible to do this in DML/DDL ? That is, dumping the data, dropping the table, recreating the table, and reimporting the data in the correct order ? I've tried different things, but I cannot write SQL to do this. I know it's easy from a script, but I don't have any other type of access to the database servers. Kind Regards Stefan
On Wed, Sep 15, 2004 at 02:24:45PM +0200, Stef wrote: > I'm struggling with a situation where I > want to recreate a table (in more than 30 databases) to > fix the column order (attnum sequence) and in another case, > fix different definitions for the same column in a table e.g. > amount numeric(16,2) > in stead of : > amount numeric(16,5) I'm not sure why you want to do the former, but in any case, it's possible by creating a new table which has things the way you want; select all the old data from the old table into the new table (using the column names to get everything in the order you like, of course), and then rename the old table, rename the new table to the old table name, and drop the old table if you like. A -- Andrew Sullivan | ajs@crankycanuck.ca The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun
Andrew Sullivan mentioned : => I'm not sure why you want to do the former, but in any case, it's Because lazy people write inserts without specifying column names. => possible by creating a new table which has things the way you want; => select all the old data from the old table into the new table (using => the column names to get everything in the order you like, of course), I like this idea, but each database may have a different table definition for the same table, and if I want to automate this, I need to figure out the column names on the fly. => and then rename the old table, rename the new table to the old table => name, and drop the old table if you like. I think I've got the solution now. I'll do it in two steps. Fist add/drop all the columns that are not there/not supposed to be there, and in the second step do what you suggested. Thanks!! Kind Regards Stefan
On Wed, Sep 15, 2004 at 04:22:01PM +0200, Stef wrote: > Andrew Sullivan mentioned : > => I'm not sure why you want to do the former, but in any case, it's > Because lazy people write inserts without specifying column names. Ugh. Sorry to say so, but this sounds to me really a lot like the cure is worse than the disease. The answer to "Bob did something incredibly stupid" is not "We'll bend ourselves into contortions to support it." (This is not to say I don't sympathise. You wouldn't believe how much I do.) > => possible by creating a new table which has things the way you want; > => select all the old data from the old table into the new table (using > => the column names to get everything in the order you like, of course), > > I like this idea, but each database may have a different table definition > for the same table, and if I want to automate this, I need to figure out the > column names on the fly. That's a little trickier, but you could figure it out with some queries from pg_class and pg_attribute. > Thanks!! No problem, but I think you need to have a long talk with your developers. Possibly while holding a baseball bat or something. Furrfu. This no-column-names thing is bound to bite you some day, and probably in tender bits where such bites would be unpleasant. A -- Andrew Sullivan | ajs@crankycanuck.ca This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie
Andrew mentioned : => Ugh. Sorry to say so, but this sounds to me really a lot like the => cure is worse than the disease. The answer to "Bob did something => incredibly stupid" is not "We'll bend ourselves into contortions to => support it." (This is not to say I don't sympathise. You wouldn't => believe how much I do.) Funny you say. It looks like "Bob" had a hand in pg_dump -d , 'cause I've many times wished there were column names specified there, too :) (I'm talking Prior 7.4 here, dunno if it's changed already) => and then rename the old table, rename the new table to the old table => name, and drop the old table if you like. The only problem I've run into now, is duplicate index names. I think this is why I didn't use this solution originally. But I figured out a way to modify pieces of the "create table" statement to drop all the indexes and constraints first. Is there an easier way around this? Stef
On Wed, Sep 15, 2004 at 05:07:00PM +0200, Stef wrote: > Funny you say. It looks like "Bob" had a hand in pg_dump -d , 'cause I've > many times wished there were column names specified there, too :) > (I'm talking Prior 7.4 here, dunno if it's changed already) Dunno about previous, but pg_dump -D does what you want. I think the -d switch did it this way because you can get away with that if you're also creating the schema in the same breath. I agree that "Bob's" fingers have left their grotty marks in plenty of places. > is why I didn't use this solution originally. But I figured out a way to > modify pieces of the "create table" statement to drop all the indexes > and constraints first. > > Is there an easier way around this? I doubt it. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner