Re: Normalising an existing table - how?

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
presumablyfail if the table already exists.  You probably need INSERT SELECT, i.e.
 
 insert into NewTable select X, MoneyColumn from T;


--Phil.