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
Re: [SQL] A simple join question that may stump you
How about:- SELECT id FROM (a LEFT JOIN b WHERE a.flag=b.flag) GROUP BY id HAVING ((COUNT(*)=COUNT(b.flag)) AND (COUNT(*)=(SELECT COUNT(*) FROM b AS b_cnt))); This relys on COUNT(field) not counting NULLs, and that NULL is what the LEFT JOIN returns for an absent b.flag:- ID a.FLAG b.FLAG - -- -- 1 1 1 2 1 1 2 2 2 3 1 1 3 2 2 3 3 NULL 4 1 1 4 3 NULL N.B In your test table you missed the case of having some, but not all of b's flags and one or more others... ID FLAG - - 4 1 4 3 Ross Smith wrote: > > OK, I have 2 tables, table A: > >ID FLAG > - - > 1 1 > 2 1 > 2 2 > 3 1 > 3 2 > 3 3 > > and table B: > > FLAG > - > 1 > 2 > > I want to find all id's from table A that have every flag in table B > but no extra flags. So, I'd end up with: > >ID > - > 2 > > As id 2 has both flag 1 and flag 2, id 1 doesn't have flag 2, and id 3 > has flag 3. > > I know it can be done, 'cause I've done it in the past, but I've spent > hours on this to no avail. Surfing the net proved fruitless as well. > > Any help would be greatly appreciated. -- This is the identity that I use for NewsGroups. Email to this will just sit there. If you wish to email me replace the domain with knightpiesold . co . uk (no spaces). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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
Re: [SQL] CHECK problem really OK now...
Kovacs Baldvin <[EMAIL PROTECTED]> writes: > I tried, and it works: the current CVS version really runs > happily the query what sent to heaven our 7.1 version of the > backend. I believe this traces to a fix I made in May: 2001-05-27 16:48 tgl * src/: backend/executor/execJunk.c, backend/executor/execMain.c, include/executor/executor.h, include/nodes/execnodes.h: When using a junkfilter, the output tuple should NOT be stored back into the same tuple slot that the raw tuple came from, because that slot has the wrong tuple descriptor. Store it into its own slot with the correct descriptor, instead. This repairs problems with SPI functions seeing inappropriate tuple descriptors --- for example, plpgsql code failing to cope with SELECT FOR UPDATE. I didn't realize at the time that the error would also affect updates of child tables, but tracing through your example with 7.1 shows clearly that the CHECK is being applied to a slot that contains a four-column tuple and only a three-column descriptor. Ooops. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
