Re: [SQL] alter help needed

At 7:43 +0200 on 29/11/98, Frank Morton wrote:

> As an example, given a table with 2 columns, if I want to add
> a new column "between" the already existing columns, is there
> any way to use "alter table" to do this?
>
> The only way I can figure out how to do this and preserve the
> data is to dump the database and write a script to modify
> the dump by modifying the table structure plus the insert
> statements reflecting the change. This seems more painful
> and I must be missing an easier way to do this.

Yes. Create the desired table, with the proper order and everything, and
then insert the values from the other table. For example, if your old table
is defined:

num1 - int4
txt1 - text
num2 - int4

And you want to make it efficient by moving the text to the end, rename it
to some other, temporary name. Then define

CREATE TABLE my_table (
num1 int4,
num2 int4,
txt1 text );

Now do:

INSERT INTO my_table (num1, num2, txt1)
SELECT num1, num2, txt1
FROM my_renamed_table;

After that, drop the renamed table, and you are done.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma