From: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
To: Graham Leggett <minfrin@sharp.fm>
To: 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> (raw)
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.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_from_postgresql_sql@chezphil.org, minfrin@sharp.fm
Subject: Re: Normalising an existing table - how?
In-Reply-To: <4268211@chezphil.org>
* 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