pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Frank Morton <fmorton@base2inc.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] alter help needed
Date: Sun, 29 Nov 1998 12:44:34 +0200
Message-ID: <l03110704b286d4cc3239@[147.233.159.109]> (raw)
In-Reply-To: <005f01be1b5b$3d5ecef0$8355e5ce@tesla.base2inc.com>

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





view thread (3+ messages)  latest in thread

Message-ID: <l03110704b286d4cc3239@[147.233.159.109]>
Permalink:  ../l03110704b286d4cc3239@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110704b286d4cc3239@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, fmorton@base2inc.com
  Subject: Re: [SQL] alter help needed
  In-Reply-To: <l03110704b286d4cc3239@[147.233.159.109]>
  References: <005f01be1b5b$3d5ecef0$8355e5ce@tesla.base2inc.com> <l03110704b286d4cc3239@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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