[SQL] Data Calculation
I need some help for which I could not find much help for within the
Postgres book I am looking at, or the online tutorial. I don't know if
this is even possible, but here goes...
I am writing for advice, as the method I followed is not the most
effecient, I think.
Scenario: Grading results. I have two tables set up - one with the
answerkey and one with the students results from a test:
answerkey:
question_number int,
answer varchar(2)
So something like this (select * from answerkey):
1 | 2 | 3 .. | 30
--
A | B | C. | D
Student results are similar as the answerkey table, but contain a unique
student id:
student_id | 1 | 2 | 3 .
-
010019 | B | C | C
029393 | B | B | C.
Currently, to calculate how each student did, within Perl, I obtain the
table results in an array and do an array calculation:
if ($student_answer->[$i] eq $correct_answer[$i-1]){$answer_correct++;}
This works fine and is quite fast, though could postgres be used to do
this faster?
The reason being is that once I have the number of correct answers for a
student, I then calculate the % score for each student, based on the
weight of the question (also in another table).
Now, all this data that Perl calculates is displayed for the end user in a
table. The user can also sort by a field, such as %.
Because Perl did the % calculations, I have to re-calculate everything and
dump it into a temporary table, to make sorting easier:
student_id, answer_correct, weights_score, percentage_score
Then, if the user wants to sort by the percentage field, I do a select *
from temp_answers order by $field.
This works fine, and of a class with 500 students, all this happens in
about 10 seconds.
I am new to the Postgres world, and am just wondering: is there any way I
can make Postgres do all the calculations, therefore, bypassing the need
to create a temporary table upon every lookup, just for sorting purposes?
A second scenario is this. I have a list of 12,000 students. The end user
selects what criteria to search for (ie. to look up students belonging in
a certain class and/or teacher). The select is fine and works, however,
then the user needs to be taken to a reports page, where this a different
Perl program running for each different report.
How do I pass this SQL statement to the perl programs? Currently, I select
the students that match the criteria and put their IDs into a temporary
table and pass the name of this table name to the other perl programs. Is
there a way to bypass this creation of a table?
Thank you very much for your time.
Ogden Nefix
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Inconsistent or incomplete behavior obverse in where clause
Hello, Our application development group has observed what we feel is inconsistent behavior when comparing numeric column references to constant/literal values in SQL. I would appreciate comments on the best approach to this problem that will allow for the highest portability of our application code. I have searched the archives and online docs, but so far have not found anyone addressing the problem quite this way. Assume wuActive is a numeric field ( with scale but no precision ) in the table WU: select count(wuid) from WU where wuActive = 0 --works fine select count(wuid) from WU where wuActive = '0' --works fine select count(wuid) from WU where wuActive = '0.0' --works fine select count(wuid) from WU where wuActive = 0.0 --throws the following exception: "Unable to identify an operator '=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast" Second, assume tPct is a numeric field ( having scale of 4 and precision of 1 ) in the table T select count(tid) from T where tPct > 77 --works fine select count(tid) from T where tPct > '77' --works fine select count(tid) from T where tPct > '77.5' --works fine select count(tid) from T where tPct > 77.5 -- again throws the exception: "Unable to identify an operator '>' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast" This seems to occur regardless of connectivity drivers used (ODBC, JDBC, etc..) I am aware of the use of type casting to force the desired behavior in these situations. I have also started to go down the road of creating functions and operators to force numeric to numeric comparison operations when comparing numeric to float, but realize that this approach is fraught with pitfalls, in fact it is interesting to us to note that with an operator in place to force numeric = float comparisons to parse as numeric = numeric, we started getting the opposite behavior. Queries with 'column reference' = 0.0 worked fine, but queries with 'column reference' = 0 threw a variant of the previous exception: "Unable to identify an operator '=' for types 'numeric' and 'integer'" Overall, this behavior appears to be inconsistent and is not the same behavior I have experienced with many other DBMS's. Specifically, it seems strange that the parser does not treat values 0.0 or 77.5 as numeric(s[,p]) when comparing the values to a column reference known to be of type numeric (s,[p]). Is an unquoted number in the form of NN.N always treated as a float? If the planner could somehow recognize that the constant/ literal value was being compared to a column reference of the type numeric (s,p) and treat the value accordingly, then would operator identification no longer be a problem? We are looking to maintain a high degree of portability in our application code, and while "CAST ( expression as type )" is fairly portable, no one here feels that it is a portable as column reference = literal/constant value. If someone knows of a better approach, or can point us to documentation of build or run-time configuration that affects the query planner where this issue is concerned, it would be much appreciated. Thanks, Paul Ogden Database Administrator/Programmer Claresco Corporation (510) 549-2290 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: [SQL] System´s database table
It's not ERD but I've found the information in the Developer's Guide regarding system catalogs to be useful in the past. This http://www.postgresql.org/idocs/index.php?catalogs.html will get you started. Thanks, Paul Ogden Claresco Corporation > -Original Message- > From: [EMAIL PROTECTED] > [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Jean-Luc Lachance > Sent: Wednesday, November 13, 2002 12:37 > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] System´s database table > > > While we are on the subject, > is there any ERD of the system's table somewhere? > > JLL > > > Josh Berkus wrote: > > > > Pedro, > > > > > I´m looking for the name of the table that contains all > databases in my > > system. I already see this in the postgre manual, but i´m > forgot where > > > > pg_database > > > > -- > > -Josh Berkus > > Aglio Database Solutions > > San Francisco > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(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] Inconsistent or incomplete behavior obverse in where
of the postgres user class than hacker class, so that's out of our scope to undertake, but we'd sure use it. > > We are looking to maintain a high degree of portability in our > > application code, and while "CAST ( expression as type )" is > > fairly portable, no one here feels that it is a portable as > > column reference = literal/constant value. If someone knows > > of a better approach, or can point us to documentation of build or > > run-time configuration that affects the query planner where this > > issue is concerned, it would be much appreciated. > > Hopefully someone else will respond to your message as well. I'll > re-phrase one of your questions for the Hackers list: > > QUESTION: Is there any way we could distinguish between literals and > column references when processing operators? That is, while we would > *not* want to implicitly convert a float column to numeric for equality > comparison, would it be possible to convert a literal value to match > the column to which it is compared? Or is literal processing completed > before any expressions are evaluated? > > -Josh Berkus > Thanks for doing that. - Paul Ogden > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] numeric problems
Gee, this sounds familiar. See the question ( http://archives.postgresql.org/pgsql-sql/2002-11/msg00191.php ) I posted of a similar nature a couple of days ago. We decided to wait for 7.3/7.4 and in the mean time we're using CAST ( 'constant value' as numeric ). Thanks, Paul Ogden Claresco Corporation (510) 549-2290 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of jack > Sent: Thursday, November 14, 2002 18:22 > To: [EMAIL PROTECTED] > Subject: [SQL] numeric problems > > > I'm using pgAdmin II version 1.3.82 , psqlODBC 7.2.3 , > postgreSQl 7.2.1. There is a problem when I do an SQL query > with a field of numeric, for eample NUMERIC (10.2). Following > staement causes an error such as "Unable to indentify an > operator '>' for type 'numeric' and 'double precision'..." > > select itemNo, listprice from itmt_info > where listprice > 50.99; > > And this one works. > > select itemNo, listprice from itmt_info > where listprice > '50.99'; > > It seems all numeric operators are not available on NUMERIC > FIELDS. Is there > anyone has done somethings on this problems? Or should I change > NUMERIC type > to FLOAT type? Please advise, thank you in advance. > > Jack > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(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
