postgres=# CREATE OR REPLACE FUNCTION my_div(double precision, double precision) RETURNS double precision AS $$ SELECT CASE WHEN $2 = 0 THEN NULL::double precision ELSE $1::double precision / $2::double precision END; $$ LANGUAGE sql; CREATE FUNCTION postgres=# select my_div(2,3); my_div ------------------- 0.666666666666667 (1 row) postgres=# select my_div(2,0); my_div -------- (1 row) postgres=# CREATE OPERATOR // (PROCEDURE = my_div, LEFTARG = double precision, RIGHTARG= double precision); CREATE OPERATOR postgres=# SELECT 10//3; ?column? ------------------ 3.33333333333333 (1 row) postgres=# SELECT 10//0; ?column? ---------- (1 row)
Pages
▼
Monday, March 28, 2011
MySQL div
I have to port one benchmark from MySQL. Test wasn't success because there was a div by zero. A result in MySQL is NULL, PostgreSQL raises a exception. I created new operator, that simulates MySQL's behave:
Even on MySQL that's wrong. That's why I like to set the sql_mode to ERROR_FOR_DIVISION_BY_ZERO when possible.
ReplyDeleteIs it an opensource benchmark?
I cannot to change MySQL application. These tests are single-use non public OLAP tests. But I can say so PostgreSQL is better than MySQL on Amazon Cluster.
ReplyDelete