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