[SQL] Querying Hierarchical Data
Hi, How do I access hierarchical data under PostgreSQL? Does it have SQL command similar to Oracle's CONNECT BY? Any help is appreciated Eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] complex query
I have two tables, users and options. The common element between the tables is "username". I want to select the "email" from "user" but only if the "mailing_list" option is set to true in the "options" table. Here are the tables: select username, email from users; username| email ---+ joe | [EMAIL PROTECTED] heidi | [EMAIL PROTECTED] payday| [EMAIL PROTECTED] fake | [EMAIL PROTECTED] mattgerg | [EMAIL PROTECTED] god | [EMAIL PROTECTED] select username, mailing_list from options; username| mailing_list ---+-- payday| t god | t fake | t mattgerg | t I want to write a query that will return the emails of only the users payday, god, fake, and mattgerg. Is this at all possible? I am new to sql, and I am having trouble. --Matt -- Matt Gerginski <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] HardCORE QUERY HELP!!!
Metnetsky <[EMAIL PROTECTED]> writes: > It's for a class and my professor has a thing for brain teaser type > questions. Incidentally, TAs and Profs aren't stupid, and have been known to check on newsgroups and mailing lists for students asking for people to do their homework for them. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Forcing query to use an index
On Monday, Mar 3, 2003, at 15:32 US/Pacific, Josh Berkus wrote: Check out the thread: Re: [SQL] OUTER JOIN with filter in today's list; this relates to your problem. Then try your query as: I'll read through this, thank you. LEFT OUTER JOIN (SELECT * FROM Customer_Month_Summary WHRE CMS.MonthStart = DATE '2003-02-01' ) CMS ON C.ID = CMS.CustomerID This works beautifully! Thanks. This set of expressions has "seq scan" written all over it. I hihgly suggest that you try to find a way to turn these into anchored text searches, perhaps using functional indexes on lower(column). Hmm, I'll have to give this one some thought, since it looks like there won't be any easy way to index those text columns, since the text I'm searching for could be anywhere within the column. Thank you everyone for all the help! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Forcing query to use an index
One suggestion I'll make about your data model -- I'm not sure it would actually help this query, but might help elsewhere: WHERE ( C.Disabled > '2003-02-28' OR C.Disabled IS NULL ) Don't use NULL values like this. Most databases don't index NULLs (Oracle) or even if they do, don't make "IS NULL" an indexable operation (postgres). There's been some talk of changing this in postgres but even then, it wouldn't be able to use an index for an OR clause like this. If you used a very large date, like -01-01 as your "not deactivated" value then the constraint would be C.disabled > '2003-02-28' and postgres could use an index on "disabled". Alternatively if you have a disabled_flag and disabled_date then you could have an index on disabled_flag,disabled_date and uhm, there should be a way to use that index though I'm not seeing it right now. This won't matter at first when 99% of your customers are active. And ideally in this query you find some way to use an index to find "kate" rather than doing a fully table scan. But later when 90% of the clients are disabled, then in a bigger batch job where you actually want to process every active record it could prevent postgres from having to dig through a table full of old inactive records. > This may make better use of your index, because the planner will have a more > accurate estimate of the number of rows returned from the outer join. > > AND: > >AND ( C.Accountnum~* 'kate' > OR C.Firstname ~* 'kate' > OR C.Lastname ~* 'kate' > OR C.Organization ~* 'kate' > OR C.Address ~* 'kate' > OR C.Postal~* 'kate' > OR C.City ~* 'kate' > OR EA.Name || '@' || JoinDomain(EA.Domain) ~* 'kate' > > This set of expressions has "seq scan" written all over it. I hihgly suggest > that you try to find a way to turn these into anchored text searches, perhaps > using functional indexes on lower(column). If you really need to find substring matches everywhere you might want to look into the full text search module in contrib/tsearch. I haven't started using it yet but I expect I will have to when I get to that part of my project. > Finally: > > OR CMS.Package ~* 'kate' *confusion*. Oooh, Yeah, this one is a big problem. It means it's not clear which end of the join to start with. Maybe it would be better to separate this into two separate queries, give the user the option to search for a user "kate" or a package "kate" but not both simultaneously. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Forcing query to use an index
> -> Merge Join (cost=6106.42..6335.30 rows=2679 width=265) (actual time=859.77..948.06 rows=1 loops=1) Actually another problem, notice the big discrepancy between the estimated row and the actual rows. That's because you have the big OR clause so postgres figures there's a good chance one of the clauses will be true so it estimates a lot of rows will match. In fact of course they're all very selective and you'll usually probably only get a few records. If you're stuck with the unanchored text search it will always do a full table scan so it will never be lightening fast. But it would probably be a bit faster if you put a limit clause (on a subquery) on the table that's doing the full table scan. That will convince postgres that there won't be thousands of resulting records, which might convince it to do a nested loop. Also, as a beneficial side effect will also limit the damage if one your users does a search for "e"... This only really helps if you can get rid of the OR CMS.package clause... otherwise it actually needs all the records in case they match a summary record with a kate package. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] SCHEMA's
Need some help with schema's. I want to be able make a user have a default schema other than public. I want to be able to have several schema's with the same group of tables define in each one. (Example) schema a (users 1,2,3) table a table b table c schema b (users 4,5,6) table a table b table c schema c (users 7,8,9) table a table b table c When a user logs into the database they will go to there default schema and not the public schema. (Example) User 5 logs in and by default uses a, b, c tables under schema b. Any ideas? Thanks, Mike Hepworth. ---(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] SCHEMA's
"Hepworth, Mike" <[EMAIL PROTECTED]> writes: > I want to be able make a user have a default schema other than public. If the schema name is the same as the user name then this happens automatically. Otherwise, you want to set a search_path value for the user --- see ALTER USER and read up on the search_path runtime parameter. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query Against a dblink View Takes Too Long to Return
Dawn Hollingsworth <[EMAIL PROTECTED]> writes: > Nested Loop (cost=0.00..466.51 rows=1 width=24) (actual > time=226.39..1018072.99 rows=9353 loops=1) > -> Subquery Scan t1 (cost=0.00..0.01 rows=1 width=0) (actual > time=225.99..345.86 rows=9353 loops=1) > -> Result (cost=0.00..0.01 rows=1 width=0) (actual > time=225.98..303.29 rows=9353 loops=1) > -> Seq Scan on allowed_station_view av (cost=0.00..162.53 rows=9353 > width=20) (actual time=0.01..31.77 rows=9353 loops=9353) > Total runtime: 1018092.69 msec The planner evidently thinks that dblink() will return only one row, and consequently it chooses a plan that would be fast in that case ... but is dog-slow for 9000 rows. Probably the easiest answer is to update to 7.3 and use the new function-in-FROM syntax for invoking dblink. That should result in more useful assumptions about the number of rows returned. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
