Обсуждение: 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