pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVIEW on lookup table
3+ messages / 2 participants
[nested] [flat]
* VIEW on lookup table
@ 2004-02-27 22:18 JJ Gabor <jj.gabor@ntlworld.com>
2004-03-05 16:39 ` Re: VIEW on lookup table Jonathan M. Gardner <jgardner@jonathangardner.net>
0 siblings, 1 reply; 3+ messages in thread
From: JJ Gabor @ 2004-02-27 22:18 UTC (permalink / raw)
To: pgsql-sql
Hello all,
I have a lookup table consisting of 100,000+ rows.
99% of the lookup values resolve to 'Unknown'.
Building the lookup table takes a long time.
I would like to remove the 'Unknown' entries from the
table and provide a VIEW to emulate them.
The VIEW would need to provide all 100,000+ rows by
using the reduced lookup data and generating the
remaining values on the fly.
The lookup table structure:
CREATE TABLE lookup_data (
id1 INTEGER,
id2 INTEGER,
name TEXT,
PRIMARY KEY (id1, id2)
);
id1 is an INTEGER; from 0 through to 50,000+
id2 is an INTEGER; either 9 or 16.
Example data:
INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a');
INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b');
INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c');
INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd');
INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e');
INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f');
INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g');
INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h');
INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i');
INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j');
INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k');
INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l');
..
In the example data, entries where id1 is 5,6,7,9 are 'Unknown';
The VIEW would return:
id1, id2, name
1, 9, 'a'
1, 16, 'b'
2, 9, 'c'
2, 16, 'd'
3, 9, 'e'
3, 16, 'f'
4, 9, 'g'
4, 16, 'h'
5, 9, 'Unknown'
5, 16, 'Unknown'
6, 9, 'Unknown'
6, 16, 'Unknown'
7, 9, 'Unknown'
7, 16, 'Unknown'
8, 9, 'i'
8, 16, 'j'
9, 9, 'Unknown'
9, 16, 'Unknown'
10, 9, 'k'
10, 16, 'l'
I am using Postgres 7.2.1, which prevents me using a
function to return a result set.
Can I achieve this in pure SQL?
Many thanks,
JJ Gabor.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: VIEW on lookup table
2004-02-27 22:18 VIEW on lookup table JJ Gabor <jj.gabor@ntlworld.com>
@ 2004-03-05 16:39 ` Jonathan M. Gardner <jgardner@jonathangardner.net>
2004-03-07 13:40 ` Re: VIEW on lookup table JJ Gabor <jj.gabor@ntlworld.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jonathan M. Gardner @ 2004-03-05 16:39 UTC (permalink / raw)
To: JJ Gabor <jj.gabor@ntlworld.com>; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Friday 27 February 2004 2:18 pm, JJ Gabor wrote:
> Hello all,
>
> I have a lookup table consisting of 100,000+ rows.
>
> 99% of the lookup values resolve to 'Unknown'.
>
> Building the lookup table takes a long time.
>
> I would like to remove the 'Unknown' entries from the
> table and provide a VIEW to emulate them.
>
> The VIEW would need to provide all 100,000+ rows by
> using the reduced lookup data and generating the
> remaining values on the fly.
>
> The lookup table structure:
>
> CREATE TABLE lookup_data (
>
> id1 INTEGER,
> id2 INTEGER,
> name TEXT,
>
> PRIMARY KEY (id1, id2)
> );
>
> id1 is an INTEGER; from 0 through to 50,000+
> id2 is an INTEGER; either 9 or 16.
>
> Example data:
>
> INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a');
> INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b');
> INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c');
> INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd');
> INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e');
> INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f');
> INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g');
> INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h');
> INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i');
> INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j');
> INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k');
> INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l');
> ..
>
> In the example data, entries where id1 is 5,6,7,9 are 'Unknown';
>
> The VIEW would return:
>
> id1, id2, name
> 1, 9, 'a'
> 1, 16, 'b'
> 2, 9, 'c'
> 2, 16, 'd'
> 3, 9, 'e'
> 3, 16, 'f'
> 4, 9, 'g'
> 4, 16, 'h'
> 5, 9, 'Unknown'
> 5, 16, 'Unknown'
> 6, 9, 'Unknown'
> 6, 16, 'Unknown'
> 7, 9, 'Unknown'
> 7, 16, 'Unknown'
> 8, 9, 'i'
> 8, 16, 'j'
> 9, 9, 'Unknown'
> 9, 16, 'Unknown'
> 10, 9, 'k'
> 10, 16, 'l'
>
> I am using Postgres 7.2.1, which prevents me using a
> function to return a result set.
>
> Can I achieve this in pure SQL?
Yes. If you create a table with all of the values, 1 to 100,000+, and then
join that with lookup_data, using a "left outer join", and then use a
case statement for the value -- when NULL, 'Unknown', then it should
work.
I would look at bending the requirements a bit before I do this. Why do
you want the string "Unknown" and not NULL? What is this table going to
be used for? Also, just because you can't write a function in the
database to do this doesn't mean you can't write a function in perl or
python outside of the database to do it.
Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really
shouldn't be used anymore.
- --
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W
YiJY3ZYsAXNfjjBTCF0vGKE=
=5EIl
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: VIEW on lookup table
2004-02-27 22:18 VIEW on lookup table JJ Gabor <jj.gabor@ntlworld.com>
2004-03-05 16:39 ` Re: VIEW on lookup table Jonathan M. Gardner <jgardner@jonathangardner.net>
@ 2004-03-07 13:40 ` JJ Gabor <jj.gabor@ntlworld.com>
0 siblings, 0 replies; 3+ messages in thread
From: JJ Gabor @ 2004-03-07 13:40 UTC (permalink / raw)
To: pgsql-sql
> Yes. If you create a table with all of the values, 1 to 100,000+, and then
> join that with lookup_data, using a "left outer join", and then use a
> case statement for the value -- when NULL, 'Unknown', then it should
> work.
This would still require constructing a large table, which is what I
want to avoid.
> I would look at bending the requirements a bit before I do this. Why do
> you want the string "Unknown" and not NULL? What is this table going to
> be used for? Also, just because you can't write a function in the
> database to do this doesn't mean you can't write a function in perl or
> python outside of the database to do it.
The technology used to access the database does not cope very well
with NULL/missing rows/colunns :/
As it turns out, the lookup table has been ditched.
> Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really
> shouldn't be used anymore.
Mutch as I would like to, this is not an option.
Thanks for your help,
JJ
On Fri, Mar 05, 2004 at 08:39:12AM -0800, Jonathan M. Gardner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Friday 27 February 2004 2:18 pm, JJ Gabor wrote:
> > Hello all,
> >
> > I have a lookup table consisting of 100,000+ rows.
> >
> > 99% of the lookup values resolve to 'Unknown'.
> >
> > Building the lookup table takes a long time.
> >
> > I would like to remove the 'Unknown' entries from the
> > table and provide a VIEW to emulate them.
> >
> > The VIEW would need to provide all 100,000+ rows by
> > using the reduced lookup data and generating the
> > remaining values on the fly.
> >
> > The lookup table structure:
> >
> > CREATE TABLE lookup_data (
> >
> > id1 INTEGER,
> > id2 INTEGER,
> > name TEXT,
> >
> > PRIMARY KEY (id1, id2)
> > );
> >
> > id1 is an INTEGER; from 0 through to 50,000+
> > id2 is an INTEGER; either 9 or 16.
> >
> > Example data:
> >
> > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k');
> > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l');
> > ..
> >
> > In the example data, entries where id1 is 5,6,7,9 are 'Unknown';
> >
> > The VIEW would return:
> >
> > id1, id2, name
> > 1, 9, 'a'
> > 1, 16, 'b'
> > 2, 9, 'c'
> > 2, 16, 'd'
> > 3, 9, 'e'
> > 3, 16, 'f'
> > 4, 9, 'g'
> > 4, 16, 'h'
> > 5, 9, 'Unknown'
> > 5, 16, 'Unknown'
> > 6, 9, 'Unknown'
> > 6, 16, 'Unknown'
> > 7, 9, 'Unknown'
> > 7, 16, 'Unknown'
> > 8, 9, 'i'
> > 8, 16, 'j'
> > 9, 9, 'Unknown'
> > 9, 16, 'Unknown'
> > 10, 9, 'k'
> > 10, 16, 'l'
> >
> > I am using Postgres 7.2.1, which prevents me using a
> > function to return a result set.
> >
> > Can I achieve this in pure SQL?
>
> Yes. If you create a table with all of the values, 1 to 100,000+, and then
> join that with lookup_data, using a "left outer join", and then use a
> case statement for the value -- when NULL, 'Unknown', then it should
> work.
>
> I would look at bending the requirements a bit before I do this. Why do
> you want the string "Unknown" and not NULL? What is this table going to
> be used for? Also, just because you can't write a function in the
> database to do this doesn't mean you can't write a function in perl or
> python outside of the database to do it.
>
> Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really
> shouldn't be used anymore.
>
> - --
> Jonathan Gardner
> jgardner@jonathangardner.net
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.3 (GNU/Linux)
>
> iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W
> YiJY3ZYsAXNfjjBTCF0vGKE=
> =5EIl
> -----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-03-07 13:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-27 22:18 VIEW on lookup table JJ Gabor <jj.gabor@ntlworld.com>
2004-03-05 16:39 ` Jonathan M. Gardner <jgardner@jonathangardner.net>
2004-03-07 13:40 ` JJ Gabor <jj.gabor@ntlworld.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