Thread: alter help needed

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.

I am trying to come up with an automated way to deal with
multiple revisions of a database structure.

Any help? Thanks.

_________________________________
Frank Morton (fmorton@mail.base2inc.com)
Voice: (317) 876-3355
FAX: (317) 876-3398
Home: (317) 574-0815

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

Frank Morton wrote:

I think you coul do this with one select and one alter table. First you
create the new table with the adecuate structure, with a temp name. You
dum all the data with a select and  drop the first table. Then you
change the name with alter table.

I hope it's useful.

>
> 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.
>
> I am trying to come up with an automated way to deal with
> multiple revisions of a database structure.
>
> Any help? Thanks.
>
> _________________________________
> Frank Morton (fmorton@mail.base2inc.com)
> Voice: (317) 876-3355
> FAX: (317) 876-3398
> Home: (317) 574-0815

--
La risa es la actitud primigenia hacia la vida:
un modo de acercamiento que pervive sólo en
criminales y artistas
            -- Oscar Wilde