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