I know that this may seem like a stale topic. I am not complaining about
outer joins not being available yet in PostgreSQL. I just want to know how
you live without them. I am migrating a production system to PostgreSQL and
I do not know how to duplicate the functionality.
For example, how can I live without outer joins in the example below:
In my current Oracle DB, I have a number of "lookup" tables that contain
something like this:
TABLE company_category:
com_cat_abbr | com_cat_long
--------------------------------------------------
SB | Small Business
LB | Large Business
NP | Not for Profit
etc.
Then in my main table, lets say the "company" table I have:
company_name | com_cat_abbr
------------------------------------------------------------
Microsoft | LB
United Way | NP
Coca Cola | NULL
If I allow nulls in my com_cat_abbr column above, then how could I do a
simple query to show the company table with the full com_cat_long
description? These alternatives do not appear attractive:
- Don't allow nulls and force a default value in the com_cat_abbr column
- Don't do the query - if you want to display it that way handle it in the
client
- get rid of the lookup table and store the full text in the company table
I like to have the lookup tables because I use them in the front end client
to populate pulldowns, they save storage space, they allow some limited
flexibility in changing the definition for the abbreviation, and they allow
administrators to be able to see the abbreviation and understand what they
are looking at. When referential integrity becomes available, I will use
these lookup tables to enforce integrity.
What are my alternatives? What is everyone else doing in their Postgres
system? Thanks.
- B
