From: Dawid Kuroczko <qnex42@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Columns into rows.
Date: Thu, 13 Jan 2005 11:26:04 +0100
Message-ID: <758d5e7f05011302265be7e12a@mail.gmail.com> (raw)
I'm working with a quite flat table schema (think: mySQL ;)),
and I want to divide it into two tables.
Lets start with how it looks now:
CREATE TABLE old_table (
id serial PRIMARY KEY,
body text,
path_a varchar(1024),
gendate_a date,
path_bb varchar(1024),
gendate_bb date,
path_ccc varchar(1024),
gendate_ccc date
);
I want to have:
CREATE TABLE new_table (
id serial PRIMARY KEY,
body text
);
CREATE TABLE new_table_paths (
id integer NOT NULL REFERENCES (new_table),
pathtype NOT NULL varchar(10),
path varchar(1024),
gendate date,
PRIMARY KEY(id,pathtype)
);
...what I'm looking for is how to, most efficiently
write a join between new_table and new_table_paths
so it looks like old_table (for compatibility (for other
people)).
I'm thinking about a trigger on schema-table
(with pathtypes), which would automagically update
view whenever schema-table is updated (new pathtype
(like 'a', 'bb', 'ccc')) is added/removed); but this is
implementation.
Now, for the join.
I can write:
SELECT * FROM (SELECT id, path AS path_a FROM new_table_paths WHERE id
= <<NUM>> AND pathtype = 'a') AS a NATURAL FULL OUTER JOIN (SELECT id,
path AS path_bb FROM new_table_paths WHERE id = <<NUM>> AND pathtype =
'bb') AS bb; [ and so on... ]
And its fast; however if I move WHERE id = <<NUM>> outside selects
(for views), it first "materializes" old layout, and then selects
id... total waste.
SELECT * FROM (SELECT id, path AS path_a FROM new_table_paths WHERE
pathtype = 'a') AS a NATURAL FULL OUTER JOIN (SELECT id, path AS
path_bb FROM new_table_paths WHERE pathtype = 'bb') AS bb WHERE id =
<<NUM>>;
I wonder if you know other way to write this join so it has good performace?
Once again, I need a SELECT since I want a VIEW. :-)
Regards,
Dawid
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: qnex42@gmail.com
Subject: Re: Columns into rows.
In-Reply-To: <758d5e7f05011302265be7e12a@mail.gmail.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