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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Preserving column order when recreating table.

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Preserving column order when recreating table.

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Preserving column order when recreating table.

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Preserving column order when recreating table.

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Preserving column order when recreating table.

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)