Thread: Outer Joins
I've been looking at the open-source databases for a project I am working on and while reading about Postgres I saw thatthey do not support outer joins yet. I was intrigued by their solution of using a union query. Something Like: select a.col1, b.col2 from a,b where a.col1 = b.col2 union select a.col1, NULL from a where a.col1 not in (select b.col2 from b) But I was wondering if the following would work (it does in some other databases) select a.col1, b.col2 from a,b where a.col1 = b.col2 or b.col2 is null or maybe even select a.col1, b.col2 from a,b where a.col1 = b.col2 or a.col1 not in (select b.col2 from b) These would seem to be far more efficient than a union query (I would try this but I don't have a Unix box at the moment to install PostgreSQL on!) Marc Rohloff
Marc, I did not look at your queries, but outer joins are supported in the latest development version of PostgreSQL, and will besupported in the upcoming 7.1 release of the software - beta due soon. Regards, Grant Marc Rohloff wrote: > I've been looking at the open-source databases for a project I am working on and while reading about Postgres I saw thatthey do not support outer joins yet. I was intrigued by their solution of using a union query. > > Something Like: > select a.col1, b.col2 from a,b where a.col1 = b.col2 > union > select a.col1, NULL from a where a.col1 not in (select b.col2 from b) > > But I was wondering if the following would work (it does in some other databases) > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 > or b.col2 is null > > or maybe even > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 > or a.col1 not in (select b.col2 from b) > > These would seem to be far more efficient than a union query > (I would try this but I don't have a Unix box at the moment to install PostgreSQL on!) > > Marc Rohloff -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) Software Engineer Universal Computer Services Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421 Johannesburg, South Africa
Marc,
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
> or b.col2 is null
The above would select each record in a that matched a record in b, plus
each record in b for every possible a where b.col2 was null - not where
there was no match.
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
> or a.col1 not in (select b.col2 from b)
This would work, but it would be *much* slower than a UNION query. "Not
In" queries are perhaps the slowest you can run; see the earlier thread
"Query Problem" for a discussion. UNION queries are, in fact, very fast
... just awkward to code and manipulate.
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small
businesses fax 436-0137 and non-profit organizations. pager 338-4078 San
Francisco
>> select a.col1, b.col2 from a,b >> where a.col1 = b.col2 >> or a.col1 not in (select b.col2 from b) >This would work, but it would be *much* slower than a UNION query. "Not >In" queries are perhaps the slowest you can run; see the earlier thread >"Query Problem" for a discussion. UNION queries are, in fact, very fast >... just awkward to code and manipulate. Why should this be slower since the UNION Query still has an identical not in clause? This is far easier (for me) to read. Marc
Marc,
> >This would work, but it would be *much* slower than a UNION query. "Not
> >In" queries are perhaps the slowest you can run; see the earlier thread
> >"Query Problem" for a discussion. UNION queries are, in fact, very fast
> >... just awkward to code and manipulate.
>
> Why should this be slower since the UNION Query still has an identical not in clause?
> This is far easier (for me) to read.
Good point. Frankly, if you have a relevant large population of data
(>10,000 rows) to test, I'd love to see comparative execution tests
between the two query structures.
Fortunately, this will all soon become moot; Tom says that outer joins
have been stable in the 7.1 build for a while. Speaking of which,
when's the 7.1 "release"? Huh, huh?
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small
businesses fax 436-0137 and non-profit organizations. pager 338-4078 San
Francisco
Marc, Tom, > Good point. Frankly, if you have a relevant large population of data > (>10,000 rows) to test, I'd love to see comparative execution tests > between the two query structures. > > Fortunately, this will all soon become moot; Tom says that outer joins > have been stable in the 7.1 build for a while. Speaking of which, > when's the 7.1 "release"? Huh, huh? On second thought, couldn't we use some kind of EXCLUDES clause to expedite this? Tom? Further, it occurs to me that as in my query, you don't want to use "NOT IN" on *either* version. Instead, use "NOT EXISTS", which is much, much faster. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Hello, I've been searching off-and-on for an answer to this question, but I haven't found anything but fleeting references. Here's the problem/question: On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use of the up arrow key for history and the escape/tab key for command completion, but on my remote web host (webpipe.net) those keys don't work. What do I need to do to get these features working on my remote web host? I've seen reference to .psqlrc in the psql man file, but not much else (no syntax, etc). Thanks in advance! Joe
Joe Conway writes: > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use > of the up arrow key for history and the escape/tab key for command > completion, but on my remote web host (webpipe.net) those keys don't work. > What do I need to do to get these features working on my remote web host? > I've seen reference to .psqlrc in the psql man file, but not much else (no > syntax, etc). If you compiled from source, make sure you had the readline-devel package installed. $ man psql <snip> If you have the readline library installed but psql does not seem to use it, you must make sure thatPostgres's top-level configure script finds it. configure needs to find both the library libreadline.a(or a shared library equivalent) and the header files readline.h and history.h (or readline/readline.hand readline/history.h) in appro priate directories. If you have the library and header files installed in an obscure place you must tell config ure about them, for example: $ ./configure --with-includes=/opt/gnu/include--with-libs=/opt/gnu/lib ... Then you have to recompile psql (not necessarily the entire code tree). The GNU readline library can be obtained from the GNU pro ject's FTP server at ftp://ftp.gnu.org <URL:ftp://ftp.gnu.org>. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Thu, Nov 23, 2000 at 07:31:36PM +0100, Peter Eisentraut allegedly wrote: > Joe Conway writes: > > > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use > > of the up arrow key for history and the escape/tab key for command > > completion, but on my remote web host (webpipe.net) those keys don't work. > > What do I need to do to get these features working on my remote web host? > > I've seen reference to .psqlrc in the psql man file, but not much else (no > > syntax, etc). > > If you compiled from source, make sure you had the readline-devel package > installed. Also, check the version of the psql shell. 6.x won't do a lot of those neat little things that make life easier in 7.x. Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum
> > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use > > of the up arrow key for history and the escape/tab key for command > > completion, but on my remote web host (webpipe.net) those keys don't work. > > What do I need to do to get these features working on my remote web host? > > I've seen reference to .psqlrc in the psql man file, but not much else (no > > syntax, etc). > > If you compiled from source, make sure you had the readline-devel package > installed. > Thanks for your responses, Peter & Mathijs. Actually, since the issue is on a hosted system, I don't have access to recompile. I was hoping that maybe there would be something I could configure from my own (non-root and non-postgres) account. My question actaully took several days just to make it to the list, so in the meantime I found my own work-around -- I copied local copies of psql and libpq up to my account on the web host. Then I put my own account folder to the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it works just like on my own machine! Thanks again for the response. Joe
Joe Conway wrote: > > > > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows > use > > > of the up arrow key for history and the escape/tab key for command > > > completion, but on my remote web host (webpipe.net) those keys don't > work. > > > What do I need to do to get these features working on my remote web > host? > > > I've seen reference to .psqlrc in the psql man file, but not much else > (no > > > syntax, etc). > > > > If you compiled from source, make sure you had the readline-devel package > > installed. > > > > Thanks for your responses, Peter & Mathijs. > > Actually, since the issue is on a hosted system, I don't have access to > recompile. I was hoping that maybe there would be something I could > configure from my own (non-root and non-postgres) account. > > My question actaully took several days just to make it to the list, so in > the meantime I found my own work-around -- I copied local copies of psql and > libpq up to my account on the web host. Then I put my own account folder to > the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it > works just like on my own machine! > > Thanks again for the response. > > Joe you could use a version of pgsql on your own machine and use the host switch to connect to an extrenal db of course your db isp will have to allow your ip to connect, not an easy thing if your dial up!! this way you could recompile your own ver of psql hope that helps
On Sun, 19 Nov 2000, Joe Conway wrote: > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use > of the up arrow key for history and the escape/tab key for command > completion, but on my remote web host (webpipe.net) those keys don't work. > What do I need to do to get these features working on my remote web host? > I've seen reference to .psqlrc in the psql man file, but not much else (no > syntax, etc). *sigh* another candidate for the FAQ, I think. The history keys rely on the GNU readline library. If that library is not available on your system, the history keys don't work. If you compile postgres from scratch, make sure you have also installed the readline-devel rpm as well, so you get the header files, else the configure phase of the compile will not properly detect and use the library. Brett W. McCoy http://www.chapelperilous.net/~bmccoy/ --------------------------------------------------------------------------- Criticism comes easier than craftsmanship. -- Zeuxis
Good morning. Since I asked this question before psql question, I would mention other aspects as well. I had same problem, history not working etc. So I was told to install readline. I checked, and readline rpm was installed. So next thing, I was told, was to upgrade my postgres. I checked for update rpm-s and installed them. Problem solved, case closed. I didn't mention, that all that happened with Mdk 7.2 distribution. So it isn't always readline or postgres. Maybe its readline, postgres 7.0.2 and distribution, because postgres 7.0.3 works well now. Ok, lets end this thread Greetings,Antti
>>>>> "Clayton" == clayton cottingham <clayton@marketingchallenge.com> writes: Clayton> you could use a version of pgsql on your own machine and Clayton> use the host switch to connect to an extrenaldb Clayton> of course your db isp will have to allow your ip to Clayton> connect, not an easy thing if your dial up!! Or, assuming you have ssh access, use ssh to setup a tunnel between the two. Sincerely, Adrian Phillips -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]