Thread: How do you live without OUTER joins?
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
It's maybe not the best solution, what I usually is : - Copy the lines you want from company into a temporary table tmp, with "com_cat_long" added with the right type, initialized with NULL - Update this column in tmp from company_category - Select from tmp - Drop tmp Or maybe : select company_name,com_cat_long from company,company_category where ... union select company_name,NULL as com_cat_long from company where com_cat_abbr=NULL I'm not sure about the syntax for the second one, sorry I can't try it now. Alain > 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 > > What are my alternatives? What is everyone else doing in their Postgres > system? Thanks.
I'm not sure if this is what you're looking for, but you can try: select company_category.com_cat_long, company.company_name from company_category, company where company_category.com_cat_abbr=company.com_cat_abbr; This is what I use myself, but I'm not quite sure this is the best solution. If I could have feedback from other people as well, I'd be very greatful also. > 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 > > > > ************ > >
> I'm not sure if this is what you're looking for, but you can try: > select company_category.com_cat_long, company.company_name from > company_category, company where > company_category.com_cat_abbr=company.com_cat_abbr; A simple join like that will inlcude only the company records with a com_cat_abbr equal to an entry in the company_category table. You would not get all the records in the company table. In the example below, you would only get records for Microsoft and the United Way...the Coca Cola entry would not be included in the query. Thus the need for outer joins.... > > This is what I use myself, but I'm not quite sure this is the best > solution. If I could have feedback from other people as well, I'd be very > greatful also. > > > 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 > > > > > > > > ************ > > > > >
Bruce Bantos wrote: > > > I'm not sure if this is what you're looking for, but you can try: > > select company_category.com_cat_long, company.company_name from > > company_category, company where > > company_category.com_cat_abbr=company.com_cat_abbr; > > A simple join like that will inlcude only the company records with a > com_cat_abbr equal to an entry in the company_category table. You would not > get all the records in the company table. In the example below, you would > only get records for Microsoft and the United Way...the Coca Cola entry > would not be included in the query. Thus the need for outer joins.... You may solve the problem with a union select by selecting the joined records in the first statement and the remaining records in the second statement. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Bruce, Can't a sub select do this (mind you not sure if PostgreSQL supports sub selects)? eg select company_name, (select com_cat_long from company_category cat where cat.com_cat_abbr = cpy.com_cat_abbr) from company cpy Dave
> Bruce, > > Can't a sub select do this (mind you not sure if PostgreSQL supports sub > selects)? > > eg > > select company_name, (select com_cat_long from company_category cat > where cat.com_cat_abbr = cpy.com_cat_abbr) from company cpy > Only 7.0 will support subselects in the target list. 6.5.* only supports them in the WHERE clause. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
How about using a union? select cc.com_cat_long, co.company_name from company_category cc, company co where cc.com_cat_abbr = co.com_cat_abbr union select 'unknown' as com_cat_long, company_name from company where com_cat_abbr is null; You still won't see entries for companies which have been assigned an abbreviation that isn't in the company_category table. I suppose you'll have some other way to enforce the data integrity. I hope this helps. Sarah Officer officers@aries.tucson.saic.com Bruce Bantos wrote: > > > I'm not sure if this is what you're looking for, but you can try: > > select company_category.com_cat_long, company.company_name from > > company_category, company where > > company_category.com_cat_abbr=company.com_cat_abbr; > > A simple join like that will inlcude only the company records with a > com_cat_abbr equal to an entry in the company_category table. You would not > get all the records in the company table. In the example below, you would > only get records for Microsoft and the United Way...the Coca Cola entry > would not be included in the query. Thus the need for outer joins.... > > > > > This is what I use myself, but I'm not quite sure this is the best > > solution. If I could have feedback from other people as well, I'd be very > > greatful also. > > > > > 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 > > > > > > > > > > > > ************ > > > > > > > > > > ************
On Tue, 11 Jan 2000, Bruce Bantos wrote:
> In my current Oracle DB, I have a number of "lookup" tables
> that contain something like this:
You make a "lookup" function, and you call the
function in your select list.
It's been a few months since I've played with
PostgreSQL, so I don't remember the syntax.
An Oraclish example:
CREATE FUNCTION LOOKUP_CATEGORY_LONG_(VARCHAR2 ABBREV)
AS
myVar COMPANY_CATEGORY.COM_CAT_LONG%TYPE;
BEGIN
SELECT COM_CAT_LONG
INTO myVar
FROM COMPANY_CATEGORY
WHERE COM_CAT_ABBR = ABBREV;
RETURN myVar;
END;
SELECT COMPANY_NAME, LOOKUP_CATEGORY_LONG(COM_CAT_ABBR) CATEGORY
FROM COMPANY;
Sarah Officer wrote: > How about using a union? > > select cc.com_cat_long, co.company_name > from company_category cc, company co > where cc.com_cat_abbr = co.com_cat_abbr > union > select 'unknown' as com_cat_long, company_name > from company > where com_cat_abbr is null; Yes, would be the best way to go. Unfortunately I need one of this outer joins in a VIEW, and seems to me that a VIEW cannot be created with a UNION. Therefore, for one of our projects we had to setup an intermediate table kept consistent using a number of triggers. Having outer joins or UNION in VIEWs would definitively be a much better way! -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://www.sevenseas.org/~alessio Nicosia, Cyprus phone: +357-2-750652 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
select * from company comp, company_category cat where comp.com_cat_abbr = cat.com_cat_abbr and comp.com_cat_abbr not in (select com_cat_addr from company_category); If sub selects work as advertised in postgresql this is the same as the oracle syntax: select * from company comp, company_category cat where comp.com_cat_abbr = cat.com_cat_abbr(+); bob Bruce Bantos wrote: > 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 > > ************ -- rdavis@lillysoftware.com rsdavis@mediaone.net http://people.ne.mediaone.net/rsdavis
Whoops. The "and" should be an "or". select * from company comp, company_category cat where comp.com_cat_abbr = cat.com_cat_abbr or comp.com_cat_abbr not in (select com_cat_addr from company_category); I read the other posts on a different named thread and they seem to indicate a "union" is necessary. I am not sure why though? Robert Davis wrote: > select * from company comp, company_category cat > where comp.com_cat_abbr = cat.com_cat_abbr > and comp.com_cat_abbr not in (select com_cat_addr from company_category); > > If sub selects work as advertised in postgresql > this is the same as the oracle syntax: > > select * from company comp, company_category cat > where comp.com_cat_abbr = cat.com_cat_abbr(+); > > bob > > Bruce Bantos wrote: > > > 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 > > > > ************ > > -- > rdavis@lillysoftware.com > rsdavis@mediaone.net > http://people.ne.mediaone.net/rsdavis > > ************ -- rdavis@lillysoftware.com rsdavis@mediaone.net http://people.ne.mediaone.net/rsdavis
Okay 3rd try: select comp.*, cat.com_cat_abbr from company comp, company_category cat where comp.com_cat_abbr = cat.com_cat_abbr union all select comp.*, null from company comp where comp.com_cat_abbr not in (select com_cat_addr from company_category); This is the same as bruce m. The real problem then is there is no generic way because you are forced to add enough nulls to pad out the other table. >>I read the other posts on a different named thread and they seem to indicate a "union" is necessary. >>I am not sure why though? The union is necessary because the "or" causes a cartesian product on the 2 clauses. Robert Davis wrote: > Whoops. The "and" should be an "or". > > select * from company comp, company_category cat > where comp.com_cat_abbr = cat.com_cat_abbr > or comp.com_cat_abbr not in (select com_cat_addr from company_category); > > I read the other posts on a different named thread and they seem to indicate a "union" is necessary. > I am not sure why though? > > Robert Davis wrote: > > > select * from company comp, company_category cat > > where comp.com_cat_abbr = cat.com_cat_abbr > > and comp.com_cat_abbr not in (select com_cat_addr from company_category); > > > > If sub selects work as advertised in postgresql > > this is the same as the oracle syntax: > > > > select * from company comp, company_category cat > > where comp.com_cat_abbr = cat.com_cat_abbr(+); > > > > bob > > > > Bruce Bantos wrote: > > > > > 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 > > > > > > ************ > > > > -- > > rdavis@lillysoftware.com > > rsdavis@mediaone.net > > http://people.ne.mediaone.net/rsdavis > > > > ************ > > -- > rdavis@lillysoftware.com > rsdavis@mediaone.net > http://people.ne.mediaone.net/rsdavis > > ************ -- rdavis@lillysoftware.com rsdavis@mediaone.net http://people.ne.mediaone.net/rsdavis
HI all,
I have a script which generates a simple table, fills it with
about 11000 unique numbers, in the primary key field,
and then at the end, shows that the index has been
lost for at least one of the values, allowing another
entry to be made that duplicates the index. This is an
abstract simplification of a problem we are experiencing in
our field database.
The compressed "script" is 64k - I didn't want to attach it to this
message, but I would be happy to send it to anyone who might
be able to use it to track down whatever bug(s) might be causing
it. (I am assuming that this is a bug. If not, please accept my
apologies).
[PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3]
Thanks,
John
------------
johnbr@incanta.net