pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedalter help needed
3+ messages / 3 participants
[nested] [flat]
* alter help needed
@ 1998-11-29 05:43 Frank Morton <fmorton@base2inc.com>
0 siblings, 2 replies; 3+ messages in thread
From: Frank Morton @ 1998-11-29 05:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] alter help needed
@ 1998-11-29 10:44 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Frank Morton <fmorton@base2inc.com>
1 sibling, 0 replies; 3+ messages in thread
From: Herouth Maoz @ 1998-11-29 10:44 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] alter help needed
@ 1998-11-30 12:41 PAX! <pvides@tnet.es>
parent: Frank Morton <fmorton@base2inc.com>
1 sibling, 0 replies; 3+ messages in thread
From: PAX! @ 1998-11-30 12:41 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-11-30 12:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-29 05:43 alter help needed Frank Morton <fmorton@base2inc.com>
1998-11-29 10:44 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-11-30 12:41 ` PAX! <pvides@tnet.es>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox