Postgres Pro
Downloads
Home   >   mailing lists

Thread: VIEW on lookup table

VIEW on lookup table

From
JJ Gabor
Date:
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.

Re: VIEW on lookup table

From
"Jonathan M. Gardner"
Date:
-----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-----

Re: VIEW on lookup table

From
JJ Gabor
Date:

> 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-----