Re: Normalising an existing table - how?

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