Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Type: text/plain
MIME-Version: 1.0
From: "Phil Endecott" <spam_from_postgresql_sql@chezphil.org>
To: Graham Leggett <minfrin@sharp.fm>, pgsql-sql@postgresql.org
Subject: Re: Normalising an existing table - how?
Date: Thu, 24 Jun 2004 19:15:15 -0400
Message-Id: <4268211@chezphil.org>
In-Reply-To: <40DB4AD0.8040805@sharp.fm>
References: <40DB229C.9040306@sharp.fm> <7184150@chezphil.org>
	<40DB4AD0.8040805@sharp.fm>

Graham Leggett <minfrin@sharp.fm> wrote:
> >>- Select the money column from the table
> >>- Populate the new normalised table with each row containing
> >>  the value from the original money column
> >>- Write the primary keys of the new rows in the normalised
> >>  table, back to a new column in the original table added for
> >>  this purpose.
> 
> > Change the order.  Do the third step first:
> > 
> > alter table T add column X integer;
> > update T set X = nextval(somesequence);
> > 
> > Now do the first and second steps together:
> > 
> > select X, MoneyColumn from T into NewTable;
> > 
> > Is this the sort of thing you need?
> 
> I think it is - though the select foo into NewTable part, does
> NewTable have to be empty first, or can it already exist?
> 
> In my case NewTable has some rows in it already, as the database is 
> currently partially normalised - I need to finish the job.

Check the docs.  I believe that SELECT INTO does the same as CREATE TABLE AS, i.e. it creates a new table.  It will presumably fail if the table already exists.  You probably need INSERT SELECT, i.e.

  insert into NewTable select X, MoneyColumn from T;


--Phil.