pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNormalising an existing table - how?
9+ messages / 4 participants
[nested] [flat]
* Normalising an existing table - how?
@ 2004-06-24 18:51 Graham Leggett <minfrin@sharp.fm>
2004-06-24 20:55 ` Re: Normalising an existing table - how? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-24 21:08 ` Re: Normalising an existing table - how? Frank Bax <fbax@sympatico.ca>
0 siblings, 2 replies; 9+ messages in thread
From: Graham Leggett @ 2004-06-24 18:51 UTC (permalink / raw)
To: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
@ 2004-06-24 20:55 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-24 21:42 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
1 sibling, 1 reply; 9+ messages in thread
From: Phil Endecott @ 2004-06-24 20:55 UTC (permalink / raw)
To: Graham Leggett <minfrin@sharp.fm>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-24 20:55 ` Re: Normalising an existing table - how? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
@ 2004-06-24 21:42 ` Graham Leggett <minfrin@sharp.fm>
2004-06-24 23:15 ` Re: Normalising an existing table - how? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
0 siblings, 1 reply; 9+ messages in thread
From: Graham Leggett @ 2004-06-24 21:42 UTC (permalink / raw)
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>; +Cc: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-24 20:55 ` Re: Normalising an existing table - how? Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-24 21:42 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
@ 2004-06-24 23:15 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
0 siblings, 0 replies; 9+ messages in thread
From: Phil Endecott @ 2004-06-24 23:15 UTC (permalink / raw)
To: Graham Leggett <minfrin@sharp.fm>; pgsql-sql
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 presumably fail if the table already exists. You probably need INSERT SELECT, i.e.
insert into NewTable select X, MoneyColumn from T;
--Phil.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
@ 2004-06-24 21:08 ` Frank Bax <fbax@sympatico.ca>
2004-06-24 21:45 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
1 sibling, 1 reply; 9+ messages in thread
From: Frank Bax @ 2004-06-24 21:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-24 21:08 ` Re: Normalising an existing table - how? Frank Bax <fbax@sympatico.ca>
@ 2004-06-24 21:45 ` Graham Leggett <minfrin@sharp.fm>
2004-06-25 08:37 ` Re: Normalising an existing table - how? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Graham Leggett @ 2004-06-24 21:45 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-24 21:08 ` Re: Normalising an existing table - how? Frank Bax <fbax@sympatico.ca>
2004-06-24 21:45 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
@ 2004-06-25 08:37 ` Richard Huxton <dev@archonet.com>
2004-06-25 09:57 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
0 siblings, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2004-06-25 08:37 UTC (permalink / raw)
To: Graham Leggett <minfrin@sharp.fm>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-24 21:08 ` Re: Normalising an existing table - how? Frank Bax <fbax@sympatico.ca>
2004-06-24 21:45 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-25 08:37 ` Re: Normalising an existing table - how? Richard Huxton <dev@archonet.com>
@ 2004-06-25 09:57 ` Graham Leggett <minfrin@sharp.fm>
2004-06-25 10:18 ` Re: Normalising an existing table - how? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Graham Leggett @ 2004-06-25 09:57 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-sql
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
--
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Normalising an existing table - how?
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-24 21:08 ` Re: Normalising an existing table - how? Frank Bax <fbax@sympatico.ca>
2004-06-24 21:45 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-25 08:37 ` Re: Normalising an existing table - how? Richard Huxton <dev@archonet.com>
2004-06-25 09:57 ` Re: Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
@ 2004-06-25 10:18 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2004-06-25 10:18 UTC (permalink / raw)
To: Graham Leggett <minfrin@sharp.fm>; +Cc: Frank Bax <fbax@sympatico.ca>; 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-06-25 10:18 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-24 18:51 Normalising an existing table - how? Graham Leggett <minfrin@sharp.fm>
2004-06-24 20:55 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-24 21:42 ` Graham Leggett <minfrin@sharp.fm>
2004-06-24 23:15 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-24 21:08 ` Frank Bax <fbax@sympatico.ca>
2004-06-24 21:45 ` Graham Leggett <minfrin@sharp.fm>
2004-06-25 08:37 ` Richard Huxton <dev@archonet.com>
2004-06-25 09:57 ` Graham Leggett <minfrin@sharp.fm>
2004-06-25 10:18 ` Richard Huxton <dev@archonet.com>
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