Thread: Normalising an existing table - how?
Hi all, I have an existing table containing a column in it containing a money value. I would like to normalise this column into a separate table, as the money value is to be expanded to contain a tax value, etc. I have been trying to find a SQL query that will do the following: - Select the money column from the table - Populate the new normalised table with each row containing the value from the original money column - Write the primary keys of the new rows in the normalised table, back to a new column in the original table added for this purpose. This third step I am struggling with - can anyone suggest a query that might achieve the writing back of the primary key to the original table? Regards, Graham --
Graham Leggett wrote: > I have an existing table containing a column in it containing > a money value. I would like to normalise this column into a > separate table, as the money value is to be expanded to contain > a tax value, etc. > > I have been trying to find a SQL query that will do the > following: > > - Select the money column from the table > - Populate the new normalised table with each row containing > the value from the original money column > - Write the primary keys of the new rows in the normalised > table, back to a new column in the original table added for > this purpose. Change the order. Do the third step first: alter table T add column X integer; update T set X = nextval(somesequence); Now do the first and second steps together: select X, MoneyColumn from T into NewTable; Is this the sort of thing you need? --Phil.
At 02:51 PM 6/24/04, Graham Leggett wrote: >I have an existing table containing a column in it containing a money >value. I would like to normalise this column into a separate table, as the >money value is to be expanded to contain a tax value, etc. > >I have been trying to find a SQL query that will do the following: > >- Select the money column from the table >- Populate the new normalised table with each row containing the value >from the original money column >- Write the primary keys of the new rows in the normalised table, back to >a new column in the original table added for this purpose. > >This third step I am struggling with - can anyone suggest a query that >might achieve the writing back of the primary key to the original table? Do all three steps in one command: create table newtable as (select key1, key2, money from oldtable); Frank
Phil Endecott wrote: >>- Select the money column from the table >>- Populate the new normalised table with each row containing >> the value from the original money column >>- Write the primary keys of the new rows in the normalised >> table, back to a new column in the original table added for >> this purpose. > Change the order. Do the third step first: > > alter table T add column X integer; > update T set X = nextval(somesequence); > > Now do the first and second steps together: > > select X, MoneyColumn from T into NewTable; > > Is this the sort of thing you need? I think it is - though the select foo into NewTable part, does NewTable have to be empty first, or can it already exist? In my case NewTable has some rows in it already, as the database is currently partially normalised - I need to finish the job. Regards, Graham --
Frank Bax wrote: > Do all three steps in one command: > > create table newtable as (select key1, key2, money from oldtable); How would I put the primary key of each row in newtable back into oldtable? Also, newtable already exists and contains data - I need to add normalised data to an already partially normalised database. Regards, Graham --
Graham Leggett <minfrin@sharp.fm> wrote: > >>- Select the money column from the table > >>- Populate the new normalised table with each row containing > >> the value from the original money column > >>- Write the primary keys of the new rows in the normalised > >> table, back to a new column in the original table added for > >> this purpose. > > > Change the order. Do the third step first: > > > > alter table T add column X integer; > > update T set X = nextval(somesequence); > > > > Now do the first and second steps together: > > > > select X, MoneyColumn from T into NewTable; > > > > Is this the sort of thing you need? > > I think it is - though the select foo into NewTable part, does > NewTable have to be empty first, or can it already exist? > > In my case NewTable has some rows in it already, as the database is > currently partially normalised - I need to finish the job. Check the docs. I believe that SELECT INTO does the same as CREATE TABLE AS, i.e. it creates a new table. It will presumablyfail if the table already exists. You probably need INSERT SELECT, i.e. insert into NewTable select X, MoneyColumn from T; --Phil.
Graham Leggett wrote: > Frank Bax wrote: > >> Do all three steps in one command: >> >> create table newtable as (select key1, key2, money from oldtable); > > > How would I put the primary key of each row in newtable back into > oldtable? Also, newtable already exists and contains data - I need to > add normalised data to an already partially normalised database. How can newtable contain data if you don't have any keys for it? Perhaps a fuller example, with the schemas of the tables in question would help. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: >> How would I put the primary key of each row in newtable back into >> oldtable? Also, newtable already exists and contains data - I need to >> add normalised data to an already partially normalised database. > How can newtable contain data if you don't have any keys for it? > > Perhaps a fuller example, with the schemas of the tables in question > would help. I have a system that keeps track of money. Part of the system's money handling is already normalised, in that there is a money table, containing information about the amount concerned, the amount of tax appropriate, the currency involved, etc. Part of the system is not normalised, in that a simple column in table contains the amount of money, but not the tax appropriate, nor the currency involved. My task is to fix this situation to make it consistent throughout. 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. Regards, Graham --
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