Message-ID: <40DB4AD0.8040805@sharp.fm>
Date: Thu, 24 Jun 2004 23:42:40 +0200
From: Graham Leggett <minfrin@sharp.fm>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7) Gecko/20040623
MIME-Version: 1.0
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Normalising an existing table - how?
References: <40DB229C.9040306@sharp.fm> <7184150@chezphil.org>
In-Reply-To: <7184150@chezphil.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Phil Endecott 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.

Regards,
Graham
--