[SQL] Recursive select
Hi all! Anyone know if it's possible to make a recursive select from a table ? My problem: I got a table of "some categories" which all points to its parrent one(tree)...shown below. And I want to select all names of parrent categories of one child, lets say "fast[4]". Now I'm solving that with many SQL queries like : "SELECT main_id FROM cat WHERE id=4;"but I would like to optimize this. Anyone can help or point me to a way ?? Thanks a lot,Minca Table : CAT ID|Main_id|Name = 10Car 21Crash 31Wash 43Fast 51Second_hand 60House 73Slow etc *(root)[0] -Car[1] -Crash[2] -Wash[3] -Fast[4] -Slow[7] -Second hand[5] -House[6] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Select question
Hi all, although not new to databases, I'm new to the wonderful world of PostGreSQl and SQL in general. Question: I do this query phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit 2 ; And I get theis result cdate| ctime | countrycode | success | carrier | duration | phonenumber | areacode | pseq +---+-+-+-+--+-- ---+--+-- 2001-04-01 | 0 | 370 | 1 | 1 |8 | "3703348" | "33" | 4005 2001-04-01 | 0 | 98 | 1 | 1 | 15 | "9871162" | "71" | 3889 Although I specified that I want only dates > 5/18/2001, I get dates 4/1/2001. Clearly, I ask the system the wrong question. How do I ask this question the correct way? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.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] index/join madness
On Wed, 23 May 2001, Michael Richards wrote: > Finally, I'm planning on moving this to 7.2 and converting all the > joins to use outer joins. Will there be a significant penalty in > performance running outer joins? Why are you planning on using outer joins? Yes there is a performance penalty because postgres will have to emit more tuples. Are you sure that you need to use outer joins? -- Dave ---(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] Select question
I'm not sure, but... Does it work if you say cdate > '2001-05-18' ? (Possibly ::date too) I'd guess your date value you're trying to put there is getting treated as an integer expression. On Wed, 23 May 2001, Chris Ruprecht wrote: > Hi all, > > although not new to databases, I'm new to the wonderful world of PostGreSQl > and SQL in general. > Question: > > I do this query > phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit > 2 ; > > And I get theis result > >cdate| ctime | countrycode | success | carrier | duration | > phonenumber | areacode | pseq > +---+-+-+-+--+-- > ---+--+-- > 2001-04-01 | 0 | 370 | 1 | 1 |8 | "3703348" > | "33" | 4005 > 2001-04-01 | 0 | 98 | 1 | 1 | 15 | "9871162" > | "71" | 3889 > > > Although I specified that I want only dates > 5/18/2001, I get dates > 4/1/2001. Clearly, I ask the system the wrong question. How do I ask this > question the correct way? ---(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] Select question
"Chris Ruprecht" <[EMAIL PROTECTED]> writes: > phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit > 2 ; Try select * from phonelog where cdate > '2001-05-18' order by cdate limit 2 I think it's interpreting your query as where cdate > 1978 (result of integer subexpression) and then doing some weird integer-to-date conversion. In general, any constant of a non-numeric datatype needs to be quoted in SQL queries. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] index/join madness
"Michael Richards" <[EMAIL PROTECTED]> writes: > [ a severely incomplete problem description ] Table schema? Full text of the query? > It has one index defined on: > Index "formdata_pkey" > Attribute | Type > ---+- > formid| integer > occid | integer > userid| integer > fieldid | integer > unique btree (primary key) > In my case I'm ignoring occid since it's always 1 for these values. > Is there any way I can coerce this into using a multifield index? It won't use *that* multifield index, at least not as a multifield index, if you provide no constraint on occid. Per the documentation: : The query optimizer can use a multi-column index for queries that : involve the first n consecutive columns in the index (when used with : appropriate operators), up to the total number of columns specified in : the index definition. For example, an index on (a, b, c) can be used in : queries involving all of a, b, and c, or in queries involving both a and : b, or in queries involving only a, but not in other combinations. (In a : query involving a and c the optimizer might choose to use the index for : a only and treat c like an ordinary unindexed column.) > Finally, I'm planning on moving this to 7.2 and converting all the > joins to use outer joins. Will there be a significant penalty in > performance running outer joins? Compared to what? Outer joins are surely a lot faster than most of the possible substitutes, but you didn't tell us what you're doing instead. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Why indexes are not used when scanning from functions?
=?KOI8-R?B?IuzP19DB3sUg4crEwc3J0iI=?= <[EMAIL PROTECTED]> writes: > create function get_fio1(text) returns varchar > as 'select fio from patient_temp where fio like $1::text || \'%\';' > language 'sql'; You won't get an indexscan for this because the LIKE pattern is not a constant at planning time, and so the planner cannot extract indexscan bounds from it. In 7.1 it's possible to obtain the desired result in plpgsql, by using EXECUTE. You'd need to substitute the pattern into the query string as a literal constant, not as a plpgsql variable. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] where's ALTER TABLE table DROP [ COLUMN ] column???
Hello! just trying to write an upgrade script for my bookkeeping system and noticed that it seems that i can't throw out the legacy stuff accumulated over time as far as i can tell, i can add columns to a table, but can't remove them later on. is this true? any easy way to circumvent this inside the DB, without destroying the existing tables (the problem: make backup f the existing table, without the offending column, drop the table, recreate it, feed it with the backup) if someone has any solution for this, i am taker! -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing ---(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] Re: Recursive select
Don't drive yourself crazy ( like me ;). You'll have to write some procedural code - sorry. I believe IBM's DB/2 supports recursive queries as defined by SQL3. Oracle provide a couple of non SQL standard clauses (CONNECT BY, LEVELS) to provide similar funcionality. -Ron- GPG and other info at: http://www.yellowbank.com/ Martin Smetak wrote: > > Hi all! > > Anyone know if it's possible to make a recursive select from a table ? > My problem: I got a table of "some categories" which all points to its > parrent one(tree)...shown below. And I want to select all names of > parrent categories of one child, lets say "fast[4]". Now I'm solving that > with > many SQL queries like : "SELECT main_id FROM cat WHERE id=4;"but I would > like to optimize this. > > Anyone can help or point me to a way ?? > > Thanks a lot,Minca > > Table : CAT > ID|Main_id|Name > = > 10Car > 21Crash > 31Wash > 43Fast > 51Second_hand > 60House > 73Slow > etc > > *(root)[0] > -Car[1] > -Crash[2] > -Wash[3] > -Fast[4] > -Slow[7] > -Second hand[5] > -House[6] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
