[GENERAL] Query Question
I have 4 tables: releases, artist_info, categories, and formats.
I am using this query:
SELECT DISTINCT *, categories.category_name as category_name,
categories.rec_num as category, formats.format_name as format_text,
releases.rec_num as release_rec_num, artist_info.name as artist_name FROM
releases, artist_info, formats, categories WHERE upper(releases.title) LIKE
upper('%get%') OR upper(artist_info.name) LIKE upper('%get%') AND
releases.artist_id = artist_info.rec_num AND releases.format =
formats.rec_num AND releases.category = categories.rec_num AND
releases.active_status = true ORDER BY title DESC;
to search and join the tables...
The intent is to search the releases table where title = %face% and the
artist_info table where name = %face% and return only rows that match that.
Somewhere my join is going wrong. The query is returning the results plus a
release titled 'Face to Face' joined to every artist and every format.
Where am I going wrong? It only occurs on searches where both the
releases.title and artist_info.name match the search criteria.
BTW, this is PG7.1 on MacOS X, though I'm sure it doesn't matter.
Hunter
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] installing DBD::Pg without installing postgres
Hello, It seems that there should be a way to install the DBD Pg module without having to install postgres on the local machine. I tried installing just the libs rpm, but that didn't seem to do the trick. I've done some usenet and mailing list archive searches, but all the info I'm turning up appears geared towards the assumption that you also want postgres installed locally. Am I looking in the wrong places? Thanks, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Query Question
On Mon, 23 Apr 2001, Hunter Hillegas wrote:
I have 4 tables: releases, artist_info, categories, and formats.
I am using this query:
SELECT DISTINCT *, categories.category_name as category_name,
categories.rec_num as category, formats.format_name as format_text,
releases.rec_num as release_rec_num, artist_info.name as artist_name FROM
releases, artist_info, formats, categories WHERE upper(releases.title) LIKE
upper('%get%') OR upper(artist_info.name) LIKE upper('%get%') AND
releases.artist_id = artist_info.rec_num AND releases.format =
formats.rec_num AND releases.category = categories.rec_num AND
releases.active_status = true ORDER BY title DESC;
to search and join the tables...
The intent is to search the releases table where title = %face% and the
artist_info table where name = %face% and return only rows that match that.
Somewhere my join is going wrong. The query is returning the results plus a
release titled 'Face to Face' joined to every artist and every format.
Where am I going wrong? It only occurs on searches where both the
releases.title and artist_info.name match the search criteria.
I'd guess you want parentheses around the first two logical expressions
that are ORed together.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] installing DBD::Pg without installing postgres
Fran Fabrizio wrote: Hello, It seems that there should be a way to install the DBD Pg module without having to install postgres on the local machine. I tried installing just the libs rpm, but that didn't seem to do the trick. I've done some What's the dependencies for the DBD::Pg RPM? Satisfy those dependencies, and properly set up for client-server communications with a postgresql server, and it _should_ just _work_. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Query Question
That fixed it! Thanks!
Hunter
From: Stephan Szabo [EMAIL PROTECTED]
Date: Mon, 23 Apr 2001 14:54:50 -0700 (PDT)
To: Hunter Hillegas [EMAIL PROTECTED]
Cc: PostgreSQL [EMAIL PROTECTED]
Subject: Re: [GENERAL] Query Question
On Mon, 23 Apr 2001, Hunter Hillegas wrote:
I have 4 tables: releases, artist_info, categories, and formats.
I am using this query:
SELECT DISTINCT *, categories.category_name as category_name,
categories.rec_num as category, formats.format_name as format_text,
releases.rec_num as release_rec_num, artist_info.name as artist_name FROM
releases, artist_info, formats, categories WHERE upper(releases.title) LIKE
upper('%get%') OR upper(artist_info.name) LIKE upper('%get%') AND
releases.artist_id = artist_info.rec_num AND releases.format =
formats.rec_num AND releases.category = categories.rec_num AND
releases.active_status = true ORDER BY title DESC;
to search and join the tables...
The intent is to search the releases table where title = %face% and the
artist_info table where name = %face% and return only rows that match that.
Somewhere my join is going wrong. The query is returning the results plus a
release titled 'Face to Face' joined to every artist and every format.
Where am I going wrong? It only occurs on searches where both the
releases.title and artist_info.name match the search criteria.
I'd guess you want parentheses around the first two logical expressions
that are ORed together.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Auto increment?
Hello all, Any ideas how I can duplicate the auto increment feature that MySQL uses in PostgreSQL? (Other than doing a query to find the next number in que) Just thought I'd ask. Thanks, Kevin _ Get your FREE download of MSN Explorer at http://explorer.msn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] installing DBD::Pg without installing postgres
What's the dependencies for the DBD::Pg RPM? Satisfy those dependencies, and properly set up for client-server communications with a postgresql server, and it _should_ just _work_. Well, if I had known what it took to satisfy the dependencies, I wouldn't have needed to post here. ;-) It was looking for a libpg-fe.h. This file does not appear to be in the libs rpm, which is the only thing I can install without needing to download the entire source. In the interest of a quicker resolution, I just went ahead and installed postgres. I had to install the libs rpm, then the postgres rpm itself, then the devel rpm in order to find the file. Since the devel depends on postgres itself, I did have to install postgres in order to install DBD Pg. Which seems wrong somehow. libpg-fe.h seems to be available from two places: in the source .tar.gz in the interfaces/ subdir, or in the devel rpm, which requires the source rpm. So either way, you have to grab the source in one form or another. Oh well. I just hoped that there was a libs rpm or .tar.gz that would allow me to build these other tools without requiring the eitire source of postgres itself. Maybe my hopes are misguided. =) Thanks, Fran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_dump failure: handler procedure for language plpgsql not foun d
Jeff Eckermann [EMAIL PROTECTED] writes: I am attempting to do a pg_dump, preparing to upgrade to 7.1. I have attempted a pg_dumpall, as well as pg_dump for various individual databases, and each fails with the message dumpProcLangs(): handler procedure for language plpgsql not found Hmm, does plpgsql_call_handler's proowner appear in pg_shadow? If not, add a user with the correct sysid... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] find position of an special entry in a select-result
I perform a complex select-operation on a database with 10.000 entries and with various Where-clauses I get 200 results. Now I want to get the position of one special entry (identified by its OID) in this selection. Background: The user can fully customize a query in an online-database (sort by, search, entries per page ...). When he adds a new entry to that database the application should automatically show exact the page where the new entry is displayed. thnx, peter -- mag. peter pilsl phone: +43 676 3574035 fax : +43 676 3546512 email: [EMAIL PROTECTED] sms : [EMAIL PROTECTED] pgp-key available ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] installing DBD::Pg without installing postgres
On Monday 23 April 2001 06:04 pm, Lamar Owen wrote: Fran Fabrizio wrote: Hello, It seems that there should be a way to install the DBD Pg module without having to install postgres on the local machine. I tried installing just the libs rpm, but that didn't seem to do the trick. I've done some What's the dependencies for the DBD::Pg RPM? Satisfy those dependencies, and properly set up for client-server communications with a postgresql server, and it _should_ just _work_. I don't know a lot of detail about how DBD::Pg works, but I do know that during installation (whether from tarball or CPAN, which I think is the best way to install perl modules), it requires the path of your postgres libraries and includes - so I guess it depends on a postgres installation on the local machine. What are you trying to accomplish, exactly? Michelle Michelle Murrain, Ph.D. President Norwottuck Technology Resources [EMAIL PROTECTED] http://www.norwottuck.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] installing DBD::Pg without installing postgres
I don't know a lot of detail about how DBD::Pg works, but I do know that during installation (whether from tarball or CPAN, which I think is the best way to install perl modules), it requires the path of your postgres libraries and includes - so I guess it depends on a postgres installation on the local machine. What are you trying to accomplish, exactly? Well, one thing (that I can think of) would be to have multiple webservers (without postgres) connecting to a central DB server. I would've run into this in a couple of weeks, actually, so I'm glad someone else found out about it first. :) I think functionality to retrieve the necessary libs ought to be built into the installation of DBD::Pg to facilitate just such a situation... John -- # John Madden [EMAIL PROTECTED] ICQ: 2EB9EA # FreeLists, Free mailing lists for all: http://www.freelists.org # UNIX Systems Engineer, Ivy Tech State College: http://www.ivy.tec.in.us # Linux, Apache, Perl and C: All the best things in life are free! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Failed on regression test
Limin Liu [EMAIL PROTECTED] writes: + NOTICE: _outNode: don't know how to print type 726 + NOTICE: _outNode: don't know how to print type 726 This is pretty harmless, but if you don't want to see it, don't start your postmaster with -d above 2. Someday, someone ought to add FkConstraint to the list of node types known to outfuncs.c and readfuncs.c. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: Hardcopy docs available
... if there is interest in an A4 layout of the docs, let me know... I've gotten several requests for the A4 format, and have completed four of the six docs in that format. Thanks for the feedback. They should be available in the next couple of days... OK, A4 docs are now posted on the web site and the ftp site. Also, I've put copies of the html tarballs on the ftp site, so there should now be tarballs, two kinds of postscript, and PDFs available there. If someone wants to run the A4 docs through a PDF converter, send 'em to me and I'll post them too. - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: [HACKERS] Re: Hardcopy docs available
On Tue, 24 Apr 2001, Thomas Lockhart wrote: ... if there is interest in an A4 layout of the docs, let me know... I've gotten several requests for the A4 format, and have completed four of the six docs in that format. Thanks for the feedback. They should be available in the next couple of days... OK, A4 docs are now posted on the web site and the ftp site. Also, I've put copies of the html tarballs on the ftp site, so there should now be tarballs, two kinds of postscript, and PDFs available there. If someone wants to run the A4 docs through a PDF converter, send 'em to me and I'll post them too. Tom, ps2pdf is on hub. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Var substitution in SELECT statements
This works: $res = $conn-exec(select cust, contact, user_name, email from $t where user_name = a1a); This doesn't: $c = a1a; $res = $conn-exec(select cust, contact, user_name, email from $t where user_name = $c); and returns the error: Attribute 'a1a' not found How do you do var substitution with the Pg module in Perl? -- Randy Perry sysTame Mac Consulting/Sales ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: installing DBD::Pg without installing postgres
At 04:30 PM 23-04-2001 -0400, Fran Fabrizio wrote: Hello, It seems that there should be a way to install the DBD Pg module without having to install postgres on the local machine. I tried installing just the libs rpm, but that didn't seem to do the trick. I've done some usenet and mailing list archive searches, but all the info I'm turning up appears geared towards the assumption that you also want postgres installed locally. Am I looking in the wrong places? My guess for installing DBD for any database is you at least need a client install - libraries, headers. But postgresql is actually quite easy to install, doesn't take tons of space, plus there's no cost or licensing problem, so I just install the whole thing. Whereas I had a tough time installing DBD for Oracle a couple of years ago. Cheerio, Link. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Var substitution in SELECT statements
on 4/23/01 9:20 PM, Randall Perry at [EMAIL PROTECTED] wrote: This works: $res = $conn-exec(select cust, contact, user_name, email from $t where user_name = a1a); This doesn't: $c = a1a; $res = $conn-exec(select cust, contact, user_name, email from $t where user_name = $c); and returns the error: Attribute 'a1a' not found How do you do var substitution with the Pg module in Perl? Whoops! Needed to quote the var as so (for $c above): $c = \'$c\'; Works now. -- Randy Perry sysTame Mac Consulting/Sales ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] anyone else with mod_perl/apache and 7.1
I recently upgraded my production server to 7.1 because people keep saying how performance is so much better than 7.0.3 and because 8k row length limitation was removed postgres is accessed by mod_perl processes maintaining persistent connections. Before I can count the number of mod_perl processes and the number of postgres backend processes and they are the same. Now postgres processes outnumber mod_perl processes by about 25% and for some reason the number of processes (both mod_perl and postgres) keep increasing beyond what is normal for the same amount of traffic at my site. Anyone else notice that? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] BETWEEN clause
Is the BETWEEN clause inclusive or exclusive? ie if I say WHERE latitude BETWEEN 45 and 55, will I get examples where the latitude equals 45 or not? Also, is latitude BETWEEN 45 and 55 any more efficient than latitude = 45 AND latitude = 55, or is it just a stylistic thing? -- Paul Tomblin [EMAIL PROTECTED], not speaking for anybody There is no substitute for good manners, except, perhaps, fast reflexes. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: BETWEEN clause
On Mon, 23 Apr 2001, Paul Tomblin wrote: Is the BETWEEN clause inclusive or exclusive? ie if I say WHERE latitude BETWEEN 45 and 55, will I get examples where the latitude equals 45 or not? Also, is latitude BETWEEN 45 and 55 any more efficient than latitude = 45 AND latitude = 55, or is it just a stylistic thing? yes, yes, and no: select 'exclusive' where 2 between 1 and 3; ?column? -- inclusive test=# select 'inclusive' where 1 between 1 and 3; ?column? -- inclusive test=# create view its_really_the_same_thing as select true where 1 between 1 and 3; test=# \d its_really_the_same_thing ... View definition: SELECT 't'::bool WHERE ((1 = 1) AND (1 = 3)); HTH, -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
