Message-ID: <40DBFC0D.3000205@archonet.com>
Date: Fri, 25 Jun 2004 11:18:53 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
MIME-Version: 1.0
To: Graham Leggett <minfrin@sharp.fm>
Cc: Frank Bax <fbax@sympatico.ca>, pgsql-sql@postgresql.org
Subject: Re: Normalising an existing table - how?
References: <5.2.1.1.0.20040624170431.04384a70@pop6.sympatico.ca>
	<40DB4B64.5080101@sharp.fm> <40DBE463.7060308@archonet.com>
	<40DBF70A.6060605@sharp.fm>
In-Reply-To: <40DBF70A.6060605@sharp.fm>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Graham Leggett wrote:
> Because the database is partially normalised, the money table already 
> contains rows corresponding to the properly normalised part of the 
> database. New rows need to be added on top of the existing rows, 
> replacing the rest of the columns that need to be normalised. As a 
> result, creating a new money table is not possible, as this table 
> already exists.

Ah! (sound of penny dropping). You want something like this:

BEGIN;

CREATE TABLE old_money (
   old_id     int4,
   old_amount numeric(10,2),
   PRIMARY KEY (old_id)
);

CREATE TABLE new_money (
   new_id     SERIAL,
   new_total  numeric(10,2),
   new_tax    numeric(10,2),
   PRIMARY KEY (new_id)
);

COPY old_money FROM stdin;
11  100
12  200
13  300
\.

-- Now make our changes

ALTER TABLE old_money ADD COLUMN money_ref int4;

UPDATE old_money SET money_ref = nextval('new_money_new_id_seq');

INSERT INTO new_money
SELECT money_ref, old_amount, 0 FROM old_money;

UPDATE old_money SET old_amount=NULL;

ALTER TABLE old_money ALTER COLUMN money_ref SET NOT NULL;
ALTER TABLE old_money ADD CONSTRAINT valid_money_ref FOREIGN KEY 
(money_ref) REFERENCES new_money;

COMMIT;

This gives you:
Before:
SELECT * FROM old_money;
  old_id | old_amount
--------+------------
      11 |     100.00
      12 |     200.00
      13 |     300.00
(3 rows)

After:
SELECT * FROM old_money ;
  old_id | old_amount | money_ref
--------+------------+-----------
      11 |            |         1
      12 |            |         2
      13 |            |         3
(3 rows)

richardh=# SELECT * FROM new_money ;
  new_id | new_total | new_tax
--------+-----------+---------
       1 |    100.00 |    0.00
       2 |    200.00 |    0.00
       3 |    300.00 |    0.00
(3 rows)

Is that what you're after? The key are the UPDATE with nextval() to set 
the money_ref in old_money and then INSERT...SELECT to make sure you get 
the reference right in new_money.

-- 
   Richard Huxton
   Archonet Ltd