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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
