From: Richard Huxton <dev@archonet.com>
To: Graham Leggett <minfrin@sharp.fm>
Cc: Frank Bax <fbax@sympatico.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Normalising an existing table - how?
Date: Fri, 25 Jun 2004 11:18:53 +0100
Message-ID: <40DBFC0D.3000205@archonet.com> (raw)
In-Reply-To: <40DBF70A.6060605@sharp.fm>
References: <5.2.1.1.0.20040624170431.04384a70@pop6.sympatico.ca>
<40DB4B64.5080101@sharp.fm>
<40DBE463.7060308@archonet.com>
<40DBF70A.6060605@sharp.fm>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, minfrin@sharp.fm, fbax@sympatico.ca
Subject: Re: Normalising an existing table - how?
In-Reply-To: <40DBFC0D.3000205@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox