Re: Query does not work: parse ERROR, Re: [SQL] How to enter lists into database: Problems with solution.
Frederick, I am really sorry to bother you again and I promise to buy a book after this problem is solved and stop asking these beginners' questions. Nah, this one wasn't a beginners question. Intermediate, maybe. But the last example-Query you sent me does not work, it produces: ERROR: parser: parse error at or near ( Hmmm... I can't see anything wrong with the query. Unfortunately, I have not set up this test database myself, so I can't actually run it. Maybe someone else can spot the syntax error? I've looked it over again and there's nothing missing. Is it possible, Frederick, that the comma after matches or people_attributes got cut off? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] PARSER ERROR
The commas did not get cut off and I think I did not make any other mistakes either. Is it also possible that the parser does not work properly? Thanks, Frederick Here is what happened again: ERROR: parser: parse error at or near ( when running : SELECT people.people_id, people.name, people.address, people_attributes.attribute_name, people_attributes.attribute_value FROM people, people_attributes, ( SELECT people_id, count(*) as match_count FROM people_attributes, search_attributes WHERE search_id = 31 AND people_attributes.attribute_name = search_attributes.attribute_name AND people_attributes.attribute_value ~* search_attributes.attribute_value ) matches, ( SELECT count(*) as attribute_count FROM search_attributes WHERE search_id = 31 ) searched WHERE people.people_id = people_attributes.people_id AND people.people_id = matches.people_id AND matches.match_count = searched.attribute_count; Postgresql Verson 7.0.3 and 7.1.3. __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Query does not work: parse ERROR, Re: How to enter lists into database: Problems with solution.
Hi Frederick, On Wed, 26 Sep 2001 19:08:59 + (UTC), [EMAIL PROTECTED] (Frederick Klauschen) wrote: Hi Josh, I am really sorry to bother you again and I promise to buy a book after this problem is solved and stop asking these beginners' questions. But the last example-Query you sent me does not work, it produces: ERROR: parser: parse error at or near ( and I am sure, I entered it correctly. (I also tried on PostgreSQL Versions 7.0.3 and 7.1.3) it's strange! With 7.1.3 here I don't get any parser error, but ERROR: Attribute people_attributes.people_id must be GROUPed or used in an aggregate function. So I changed Josh's statement to SELECT people.people_id, people.name, people.address, people_attributes.attribute_name, people_attributes.attribute_value FROM people, people_attributes, ( SELECT people_id, count(*) as match_count FROM people_attributes, search_attributes WHERE search_id = 31 AND people_attributes.attribute_name = search_attributes.attribute_name AND people_attributes.attribute_value ~* search_attributes.attribute_value GROUP BY people_id) matches, ( SELECT count(*) as attribute_count FROM search_attributes WHERE search_id = 31 ) searched WHERE people.people_id = people_attributes.people_id AND people.people_id = matches.people_id AND matches.match_count = searched.attribute_count; and it seems to work now. Thank you very much, Frederick HTH, Carl van Tast ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Subquery with IN or EXISTS
Hi A., On 26 Sep 2001 07:24:41 -0700, [EMAIL PROTECTED] (A. Mannisto) wrote: Hello, does anybody know why this: SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2) equals this: SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2) but this: SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2 WHERE col3='huu') equals _NOT_ this: SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2 AND col3='huu') E.g. resultset is not the same in last two statements. Can I get same set as IN statement somehow using EXISTS (performance issue)? I cannot reproduce your problem, results are equal here with PostgreSQL 7.1.3. Could you post your CREATE TABLE and INSERT statements? Re performance: There's more than one way to do it. (Where did I hear this before? ;-)) You might try: SELECT tab.* FROM tab, tab2 WHERE tab.col1 = tab2.col2; or SELECT DISTINCT ... , if col2 is not unique in tab2. Kind regards, Carl van Tast ---(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
[SQL] is it possible to get the number of rows of a table?
I would like to compare the number of rows of one table and of another and use it in a query like this: SELECT * FROM WHERE number of rows of table EQUALS number of rows of table i.e. I only want get a query result if the tables have the same number of rows. Is there a function or a way to do this ? Thanks, Frederick __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.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: [SQL] is it possible to get the number of rows of a table?
This should be really easy to implement in a function yourself. And I don't think there is already something similar in pgsql. == Wei Weng Network Software Engineer KenCast Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Frederick Klauschen Sent: Wednesday, September 26, 2001 11:15 AM To: [EMAIL PROTECTED] Subject: [SQL] is it possible to get the number of rows of a table? I would like to compare the number of rows of one table and of another and use it in a query like this: SELECT * FROM WHERE number of rows of table EQUALS number of rows of table i.e. I only want get a query result if the tables have the same number of rows. Is there a function or a way to do this ? Thanks, Frederick __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL]plpgsql function case statement
Peter Schmidt wrote: I'm having trouble finding the best solution for the following plpgsql function. The function does the following: Hi, I post in this mailing list for the first time. :-) I think your pl/pgsql function runs properly, but there is likely to exist another SQL pattern. In this case, estimation of whether the TIMESTAMP value or NULL is finished at one time. Please try it, if you have time for checking. (on v7.1.3) create function updateLastUsed(text, text) returns integer as ' declare wm integer; rows_updated integer; begin rows_updated := 0; wm := getHighmark($1,$2); -- UPDATE info SET lastused = (SELECT case when MAX(p.requesttime) is null then info.firstused else MAX(p.requesttime) end FROM usage_log p WHERE p.id = info.id AND p.seq_no = wm ) -- return rows_updated; end; ' language 'plpgsql'; M.Sugawara [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to enter lists into database: Problems with solution.
Frederick, Mary Stuart correctly. But such a query also seems to get results that contain only one of the search_attributes. e.g. a 32 Peter Smith who e.g. just has an entry 24 32 hair brown (and no mice hobby) is also found. I need to get only results that match the search completely. I would be happy if you could help me again. Thanks, Frederick Oops. You are quite correct. Unfortunately, the query that you need is somewhat more complicated: SELECT people.people_id, people.name, people.address, people_attributes.attribute_name, people_attributes.attribute_value FROM people, people_attributes, ( SELECT people_id, count(*) as match_count FROM people_attributes, search_attributes WHERE search_id = 31 AND people_attributes.attribute_name = search_attributes.attribute_name AND people_attributes.attribute_value ~* search_attributes.attribute_value ) matches, ( SELECT count(*) as attribute_count FROM search_attributes WHERE search_id = 31 ) searched WHERE people.people_id = people_attributes.people_id AND people.people_id = matches.people_id AND matches.match_count = searched.attribute_count; This structure will also allow you to search for, say, 4 out of 5 items by changing the last line to: AND matches.match_count = (searched.attribute_count - 1); Also, if you re-arrange the query slightly, you can turn it into a view. The trick is to have the search_id as an output column rather than a WHERE clause item in the sub-selects. Have fun! -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Query does not work: parse ERROR, Re: [SQL] How to enter lists into database: Problems with solution.
Hi Josh, I am really sorry to bother you again and I promise to buy a book after this problem is solved and stop asking these beginners' questions. But the last example-Query you sent me does not work, it produces: ERROR: parser: parse error at or near ( and I am sure, I entered it correctly. (I also tried on PostgreSQL Versions 7.0.3 and 7.1.3) Thank you very much, Frederick --- Josh Berkus [EMAIL PROTECTED] wrote: Frederick, Mary Stuart correctly. But such a query also seems to get results that contain only one of the search_attributes. e.g. a 32 Peter Smith who e.g. just has an entry 24 32 hair brown (and no mice hobby) is also found. I need to get only results that match the search completely. I would be happy if you could help me again. Thanks, Frederick Oops. You are quite correct. Unfortunately, the query that you need is somewhat more complicated: SELECT people.people_id, people.name, people.address, people_attributes.attribute_name, people_attributes.attribute_value FROM people, people_attributes, ( SELECT people_id, count(*) as match_count FROM people_attributes, search_attributes WHERE search_id = 31 AND people_attributes.attribute_name = search_attributes.attribute_name AND people_attributes.attribute_value ~* search_attributes.attribute_value ) matches, ( SELECT count(*) as attribute_count FROM search_attributes WHERE search_id = 31 ) searched WHERE people.people_id = people_attributes.people_id AND people.people_id = matches.people_id AND matches.match_count = searched.attribute_count; This structure will also allow you to search for, say, 4 out of 5 items by changing the last line to: AND matches.match_count = (searched.attribute_count - 1); Also, if you re-arrange the query slightly, you can turn it into a view. The trick is to have the search_id as an output column rather than a WHERE clause item in the sub-selects. Have fun! -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
