From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Graham Leggett <minfrin(at)sharp(dot)fm> |
Cc: | Frank Bax <fbax(at)sympatico(dot)ca>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Normalising an existing table - how? |
Date: | 2004-06-25 10:18:53 |
Message-ID: | 40DBFC0D.3000205@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleus Mantzios | 2004-06-25 12:56:17 | Re: Join columns |
Previous Message | Graham Leggett | 2004-06-25 09:57:30 | Re: Normalising an existing table - how? |