I want to select some rows from quiz_grades and order them by the grade column. Now unfortunately, the grade column is varchar (why?), so if you ORDER BY grade ASCENDING, a value of "100" actually goes before a value of "80" because "1" < "8".
I 'd prefer to have the database order the rows instead of pulling them out and sorting in PHP because it has to be faster that way. Now in MySQL, I can achieve the desired result by using the trick "ORDER BY grade + 0 ASCENDING", which correctly evaluates the columns as a numeric type.
Can anyone tell me if that's compatible with Postgres? Because it's definitely the most elegant solution I can think of...
Thanks!
Jon
Postgres compatibility & quiz_grades, please advise
Number of replies: 17Re: Postgres compatibility & quiz_grades, please advise
Re: Postgres compatibility & quiz_grades, please advise
Agreed, Postgres supports casting. I tried the two syntaxes:
SELECT id,grade FROM mdl_quiz_grades ORDER BY CAST(grade as integer);
SELECT id,grade FROM mdl_quiz_grades ORDER BY grade::int;
The only problem is that it doesn't seem to work. Postgres doesn't like casting varchar to ints
Two alternatives:
- if you are certain the field is numeric, we should change it to int...
- otherwise, I hate the idea of suggesting you sort it in PHP, but...
SELECT id,grade FROM mdl_quiz_grades ORDER BY CAST(grade as integer);
SELECT id,grade FROM mdl_quiz_grades ORDER BY grade::int;
The only problem is that it doesn't seem to work. Postgres doesn't like casting varchar to ints
Two alternatives:
- if you are certain the field is numeric, we should change it to int...
- otherwise, I hate the idea of suggesting you sort it in PHP, but...
Re: Postgres compatibility & quiz_grades, please advise
Did anyone actually try the grade + 0 trick? 
I have no easy access to a Postgres install to try it myself...
As for the field, I guess that's Henrik's domain and I would have to assume that if it's varchar, there is a good reason for it. Actually the default value for that field is "0.0", so maybe the good reason is that it allows you to test with empty() [empty() returns true for the string "0"]. But that's only guessing, and I don't wish to disrupt huge unknown quantities of quiz code by changing the column type.
I have no easy access to a Postgres install to try it myself...As for the field, I guess that's Henrik's domain and I would have to assume that if it's varchar, there is a good reason for it. Actually the default value for that field is "0.0", so maybe the good reason is that it allows you to test with empty() [empty() returns true for the string "0"]. But that's only guessing, and I don't wish to disrupt huge unknown quantities of quiz code by changing the column type.
Re: Postgres compatibility & quiz_grades, please advise
yup:
select * from mdl_quiz_grades order by grade +0 asc;
ERROR: operator does not exist: character varying + integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
select * from mdl_quiz_grades order by grade +0 asc;
ERROR: operator does not exist: character varying + integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
Re: Postgres compatibility & quiz_grades, please advise
How about: SELECT id, to_number(grade, '999') AS grade FROM mdl_quiz_grades ORDER BY to_number(grade,'999');
You also might have to add a regular expression to exclude those values that are not numeric:
SELECT id, to_number(grade, '999') AS grade FROM mdl_quiz_grades WHERE grade ~ '[0-9]' ORDER BY to_number(grade,'999');
I know this goes database specific but in this case there might not be any other choice...
You also might have to add a regular expression to exclude those values that are not numeric:
SELECT id, to_number(grade, '999') AS grade FROM mdl_quiz_grades WHERE grade ~ '[0-9]' ORDER BY to_number(grade,'999');
I know this goes database specific but in this case there might not be any other choice...
Re: Postgres compatibility & quiz_grades, please advise
Thanks Martin, Penny, Janne...
I really appreciate the input but unfortunately, I don't think this problem wants to be solved.
So it seems that there are only two viable solutions:
Jon
I really appreciate the input but unfortunately, I don't think this problem wants to be solved.
- MySQL does the really neat automatic conversion so is OK. It's not portable to any other DB though, it seems.
- I might be able to do the trick with CAST, which is portable to boot, but Martin says that Postgres doesn't like the varchar -> integer cast, plus the field clearly implies that it might contain non-integer values, and there is no cast to float!
- I could try to write non-portable SQL and formulate a query tailored to the database from PHP (using to_number for Postgres), but to_number expects you to know what numbers you are dealing with, which I don't! (How many digits? How many decimals?). Plus, after googling a bit, I found a couple of "huh?" stories regarding server locales and to_number, and frankly that convinced me to not pursue it further.
So it seems that there are only two viable solutions:
- Discipline that $^!@%$#&*$@# grade column!
- Sort the results in PHP (...and this actually needs to be sorted by grade first, and then also by timestamp if the grades are the same!)
Jon
Re: Postgres compatibility & quiz_grades, please advise
Going slightly OT here:
[Update:]
mod/quiz/locallib.php line 1447:So this line at least doesn't know anything about decimal points in the grades. I wonder what else I 'm going to find...
return (round($grade->grade,0));
[Update:]
mod/quiz/locallib.php line 1463:
$grade->grade = round($bestgrade, 2);
...or is it two decimals?
Re: Postgres compatibility & quiz_grades, please advise
+1 for smack the column into shape 
Re: Postgres compatibility & quiz_grades, please advise
I went, I saw, and I 'm now in the process of conquering. 
That column is going to be converted to the true ways of the REAL very soon (incidentally it seems that "real" is the most portable way to say "float", anyone know otherwise?).
No problem with new installs. What about ALTER TABLE though? MySQL obviously won't give any trouble if we change the varchar to a real, but what about Postgres? I 'd hate to break the upgrade procedure to 1.5, so please try doing an upgrade after getting my latest commit to see what happens.
It would probably help if you could add one or two records with a couple decimals in the grade and see if it preserves them, too.
Thanks for all the help here!
Jon
That column is going to be converted to the true ways of the REAL very soon (incidentally it seems that "real" is the most portable way to say "float", anyone know otherwise?).
No problem with new installs. What about ALTER TABLE though? MySQL obviously won't give any trouble if we change the varchar to a real, but what about Postgres? I 'd hate to break the upgrade procedure to 1.5, so please try doing an upgrade after getting my latest commit to see what happens.
It would probably help if you could add one or two records with a couple decimals in the grade and see if it preserves them, too.
Thanks for all the help here!
Jon
Re: Postgres compatibility & quiz_grades, please advise
I just tried this and it doesn't work
Here's some output:
(postgres7): BEGIN
Success
(postgres7): ALTER TABLE mdl_quiz_grades ADD COLUMN "grade_alter_column_tmp" real
Success
(postgres7): UPDATE mdl_quiz_grades SET "grade_alter_column_tmp"='0'
Success
(postgres7): ALTER TABLE mdl_quiz_grades ALTER COLUMN "grade_alter_column_tmp" DROP NOT NULL
Success
(postgres7): ALTER TABLE mdl_quiz_grades ALTER COLUMN "grade_alter_column_tmp" SET DEFAULT '0'
Success
(postgres7): UPDATE mdl_quiz_grades SET "grade_alter_column_tmp" = "grade" -1: ERROR: column "grade_alter_column_tmp" is of type real but expression is of type character varying HINT: You will need to rewrite or cast the expression.
-- broken from now on since we're using transactions --
The problem is copying the contents of the old column (varchar) into the new column (real). It must need to be cast first, although I couldn't figure out how to do SET column1 = cast(column2 AS int) and make it work
At any rate, it's not going to play nicely with table_column.
Here's some output:
(postgres7): BEGIN
Success
(postgres7): ALTER TABLE mdl_quiz_grades ADD COLUMN "grade_alter_column_tmp" real
Success
(postgres7): UPDATE mdl_quiz_grades SET "grade_alter_column_tmp"='0'
Success
(postgres7): ALTER TABLE mdl_quiz_grades ALTER COLUMN "grade_alter_column_tmp" DROP NOT NULL
Success
(postgres7): ALTER TABLE mdl_quiz_grades ALTER COLUMN "grade_alter_column_tmp" SET DEFAULT '0'
Success
(postgres7): UPDATE mdl_quiz_grades SET "grade_alter_column_tmp" = "grade" -1: ERROR: column "grade_alter_column_tmp" is of type real but expression is of type character varying HINT: You will need to rewrite or cast the expression.
-- broken from now on since we're using transactions --
The problem is copying the contents of the old column (varchar) into the new column (real). It must need to be cast first, although I couldn't figure out how to do SET column1 = cast(column2 AS int) and make it work
At any rate, it's not going to play nicely with table_column.
Re: Postgres compatibility & quiz_grades, please advise
This really has got to be solved somehow ... bug 2460
Re: Postgres compatibility & quiz_grades, please advise
I've been trying to find an answer to this and I THINK you can go like:
UPDATE mdl_quiz_question_grades SET numericfield = cast(cast(varcharfield AS TEXT) AS REAL);
If you cast it to text first and then to real, it seems to go, at least, I get update 0 rather than "error, you die"
http://archives.postgresql.org/pgsql-novice/2004-12/msg00204.php
(edit: maybe table_column should be more clever about this sort of thing and detect the datatypes of the to and from columns and cast them appropriately? sounds like hard though - in this case Jon, you can probably get away with making postgres7.php do the steps manually but change the update to use the nested cast)
UPDATE mdl_quiz_question_grades SET numericfield = cast(cast(varcharfield AS TEXT) AS REAL);
If you cast it to text first and then to real, it seems to go, at least, I get update 0 rather than "error, you die"
http://archives.postgresql.org/pgsql-novice/2004-12/msg00204.php
(edit: maybe table_column should be more clever about this sort of thing and detect the datatypes of the to and from columns and cast them appropriately? sounds like hard though
Re: Postgres compatibility & quiz_grades, please advise
OK, that might do then. But what I 'd like you guys to check is this:
If there are a few records in there with decimal values like 7.47 and 11.2, will this preserve the values correctly during the update? Because if it doesn't we have to find some other way...
If there are a few records in there with decimal values like 7.47 and 11.2, will this preserve the values correctly during the update? Because if it doesn't we have to find some other way...
Re: Postgres compatibility & quiz_grades, please advise
test=# create table test (foo varchar(15));
CREATE TABLE
test=# insert into test values('7.47');
INSERT 1019070 1
test=# insert into test values('11.12');
INSERT 1019071 1
test=# insert into test values('34.59');
INSERT 1019072 1
test=# alter table test add column bar real;
ALTER TABLE
test=# update test set bar = cast(cast(foo as text) as real);
UPDATE 3
test=# select foo,bar from test;
foo | bar
-------+-------
7.47 | 7.47
11.12 | 11.12
34.59 | 34.59
(3 rows)
test=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+-----------
foo | character varying(15) |
bar | real |
test=#
Re: Postgres compatibility & quiz_grades, please advise
I updated datalib.php to try and be smart about this kind of thing. So... does the table_column() call now work correctly?
(fingers crossed)
(fingers crossed)
Re: Postgres compatibility & quiz_grades, please advise
Hi Jon!
I changed WHERE 0 to LIMIT 0 and it worked (WHERE 0 is not postgres friendly).. & I've committed that fix.
Yay! Closure!
I changed WHERE 0 to LIMIT 0 and it worked (WHERE 0 is not postgres friendly).. & I've committed that fix.
Yay! Closure!
Re: Postgres compatibility & quiz_grades, please advise
Take that, you Postgres column alteration! 