Re: [SQL] writing a simple sql parser and database program
On 11/7/06, Peter Michaux <[EMAIL PROTECTED]> wrote: I would like to learn how to write a simple SQL parser and database program ... Is there a link or book that explains the basics of how a database parses SQL and how it then operates on the data? For a fairly good learning resource, you can check out LEAP (http://leap.sourceforge.net/); it doesn't use SQL and instead, has it's own relational language... but it's a good place to start if you want to learn how this stuff works. You can also check out pql (http://www.ibiblio.org/pub/Linux/apps/database/sql/pql-0.8.1.src+bin.tgz), which uses a SQL-ish language, or SQLite for a little more advanced example (http://www.sqlite.org) Of course, there's a lot of fairly simple and easy-to-understand Java databases like hsql or McKoi too. Most Java databases are going to be using ANLTR or JavaCC for their parsers while C/C++-based databases use Bison, ANTLR/PCCTS, or a hand-written SQL parser. A Java database is going to be a lot easier to understand the concepts of than one in C. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Nested select
On 11/6/06, Hubert Retif <[EMAIL PROTECTED]> wrote: I am migrating my application from MySQL to Postgresql and have met following situation: SELECT (sum(sold_price)/(select sum(sold_price) from car_archive))*100 as CA_pcent, reason_text FROM car_archive group by reason_text order by CA_pcent desc works perfectly in MySQL, but not in Postgresql. The problem seems to be the nested select which should deliver a sum of the whole table, but it does not. If I replace this nested select with a fix value, then it works: SELECT (sum(sold_price)/(333))*100 as CA_pcent, reason_text FROM car_archive group by reason_text order by CA_pcent descAre you suffering from round off error? That is the only thing I can think of from what you have said. What happens if you cast the price to a real or double precision?== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Distribution of results
On 11/1/06, Raghuraman K <[EMAIL PROTECTED]> wrote: Hi, I have a table like this: create table(xyz char(10), answer number(4)). There are a lot of rows in this table. I am looking at a query that will help me represent the distribution of data records based on the column answer. For example, we may take that the highest entry for answer column is 90 and the lowest is 2 and there are 1000 records. I am looking at a query that will tell how the 1000 records are distributed between the highest and lowest answer (in this case between 90 and 2). Can anyone please help?It helps to know what kind of distribution information you are after.Mean:select sum(number) / count(*) from xyz;Median: Check out this URL http://72.14.203.104/search?q=cache:kvZMBQuoAbkJ:people.planetpostgresql.org/greg/index.php%3F/categories/13-Math+postgresql+median+mean+functions&hl=en&gl=us&ct=clnk&cd=1&client=firefox-aRange: select max(number) - min(number) from xyz;Population Variance:select power(sum(number - mean), 2) / count(*)from xyzinner join (select sum(number) / count(*) as mean from xyz) as xyz_mean ;Sample Variance:select power(sum(number - mean), 2) / (count(*) - 1) from xyz inner join ( select sum(number) / count(*) as mean from xyz ) as xyz_mean ;Note that I did not check the syntax for typos.Anything more than this will require you whip out a Statistics book. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Nested select
On Mon, 2006-11-06 at 05:08, Hubert Retif wrote: > Hi, > > > > I am migrating my application from MySQL to Postgresql and have met > following situation: > SELECT > (sum(sold_price)/(select sum(sold_price) from car_archive))*100 as > CA_pcent, > reason_text > FROM car_archive > group by reason_text > order by CA_pcent desc > works perfectly in MySQL, but not in Postgresql. The problem seems to > be the nested select which should deliver a sum of the whole table, > but it does not. Yes, I'm pretty sure that I know what the problem is here. It's that MySQL is sloppy about group by and select lists, and PostgreSQL is pretty exacting about them. In MySQL, this will work: table_1 col1 | col2 1 | 2 1 | 3 select col1, col2 from table_1 group by col1 and you'll get back either (1,2) or (1,3) but you really can't be sure which one. In PostgreSQL, that table and that query would get an error, because any column in the select list must be in the group by. The SQL spec allows for certain other situations where you'd be guaranteed for col2 to be unique, but postgresql isn't programmed for them. So, with your query, (select sum(sold_price) from car_archive)) isn't in the group by, so it's illegal. You should be able to use a subselect to do a workaround, and as an added bonus, said subselect should work on other SQL compliant databases as well, not just postgresql. select (sold_price/sum(sold_price))*100 as pct, reason_text from car_archive gets us a list of all the sold_prices/sum as pct, and the reasons... then, we subselect on that and use group by to get what we want. select a.pct, a.reason_text from ( select (sold_price/sum(sold_price))*100 as pct, reason_text from car_archive ) as a group by a.reason_text, a.pct that might work. or be close. no guarantees, guaranteed void in Tennessee. ---(end of broadcast)--- TIP 1: 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] delete and select with IN clause issues
On Fri, 3 Nov 2006, Tom Lane wrote: Well, I can't find anything wrong :-(. There are some differences in the list of contained keys, but they're all up near the end of the range, which is consistent with the assumption that the table is live and had some changes between your two dumps of the index. In particular, there's no difference in the entries for the troublesome key value: 3863562924080 25 3863562924080 26 3863562924080 27 So I dunno what to make of it. If it happens again, we need to look more closely. Well, it's been working wonderfully since the REINDEX, so I don't know what to say. Any idea if having a too small max_fsm_pages could hose an index, because I know that happened not too long before we started seeing this problem. The fsm settings were increased prior to the problem occurring, but it's possible the index was already damaged? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] converting Informix outer to Postgres
> --- [EMAIL PROTECTED] wrote: > > > Hi all, > > I have been working on this Informix SQL query which has an outer > join. > > I have attached Informix query and my "supposedly" solution to this > query > > but I cannot get the same count. I appreciate for any help. > > Thanks. > > > > --Informix query > > select count(u.id) > > from user u, invention i, inv_contracts ic, inv_milestones im1, > milestonedef mdef1, > > OUTER inv_milestones im2, > > milestonedef mdef2 > > where u.id = i.user_id and > > ic.inv_id = i.id and > > ic.contract_id = mdef1.contract_id and > > im1.inv_id = i.id and > > mdef1.id = im1.milestone_id and > > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > > ic.contract_id = mdef2.contract_id and > > im2.inv_id = i.id and > > mdef2.id = im2.milestone_id and > > im1.datereceived IS NULL > > > > --Postges query > > select count(u.id) > > from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef > mdef1, > > --OUTER inv_milestones im2, > > milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = > im2.milestone_id > > LEFT OUTER JOIN invention i ON im2.inv_id = i.id > > where u.id = i.user_id and > > ic.inv_id = i.id and > > ic.contract_id = mdef1.contract_id and > > im1.inv_id = i.id and > > mdef1.id = im1.milestone_id and > > im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and > > ic.contract_id = mdef2.contract_id and > > --im2.inv_id = i.id and --QUERY1 > > --mdef2.id = im2.milestone_id and --QUERY2 > > im1.datereceived IS NULL > > Is there a reason that these two lines are commented out in the > postgresql query? The reason is that once it is converted to postgres (LEFT OUTER), those two query moved into LEFT OUTER JOIN clause. For simplicity; if the Informix query were as; select count(u.id) from user u, OUTER inv_milestones im2, milestonedef mdef2 where mdef2.id = im2.milestone_id --QUERY2 in Postgres it would have been as; select count(u.id) from dbuser u, --OUTER inv_milestones im2, milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id --where --mdef2.id = im2.milestone_id --QUERY2 In original Informix outer query has one OUTER but has two queries(QUERY1,QUERY2). I can test my conversion at one query a time meaning I can do my conversion if there were only QUERY1 or QUERY2. In my test cases they return the same number on count, but I cannot do it for QUERY1 and QUERY2 at the same time. I have done it before for this kind of query but for this case my solution is not working. thanks for help. - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [ADMIN] Is there anyway to...
Moving to -general (and please start a new thread instead of hijacking an existing one). On Thu, Nov 02, 2006 at 01:14:22PM -0500, louis gonzales wrote: > Hello all, > Is there an existing mechanism is postgresql that can automatically > increment/decrement on a daily basis w/out user interaction? The use > case I'm considering is where a student is in some type of contract with > an instructor of some sort, and that contract puts a time limit on the > student requiring her to pay a fee by a certain day. IF that day comes > to pass - or a certain number of days elapse - and that payment > requirement hasn't been met, I want to trigger a function. > > The one requirement I want to impose is, that no end user of the DB > application, needs to do anything to set the trigger, other than the > initialization of making the student of this type. > > An example would be: > Day1 - Application user(typically the instructor) creates a profile for > a new student - John Doe, which sets a 30 day time limit for John Doe to > pay $100.00 > Day2 -> Day31 - John Doe didn't make the payment > Day 31 - Trigger of event occurs when the instructor logs in. > > Basically on Day 1 when John Doe's profile was created, I want a > decrement counter to occur daily on his profile(some attribute/timer) > and nothing should happen until day 31 when he doesn't pay. While you could setup a cron job to decrement some counter every day, I think that's not the best approach. Instead, I'd run a query once a day that finds all students that are past-due and takes some kind of action. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
