changing float->numeric casts from assignment to implicit, dangerous?

Question

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding precision.

In MySQL, round(some_float_column, 2) works as expected, returning the value of some_float_column rounded to two decimal places. In Postgres it errors with ERROR: function round(double precision, integer) does not exist and suggests HINT: No function matches the given name and argument types. You might need to add explicit type casts..

As indicated by the docs... http://www.postgresql.org/docs/9.1/static/functions-math.html ...Postgres has two round functions, round(value) which takes a double precision or a numeric, and round(value, precision) which takes a numeric only and an integer.

So, I don't understand why the two-argument form of round doesn't take a double to begin with, but whatever. In searching I discovered two solutions to this problem. One is to simply create my own version of round(value, precision) that takes (double, int) and wraps the existing (numeric, int) version with an explicit cast. That certainly works, but I don't like it (my background is in Oracle which doesn't even have a true floating point type). It seems to me that float/double ought to be implicitly castable to numeric. And it turns out that an ASSIGNMENT cast for these types comes pre-defined. But ASSIGNMENT doesn't work for function calls, as we see here, it would need to be IMPLICIT. The trouble with that is that only one cast can be defined per pair of types, and the float4->numeric and float8->numeric assignment casts are required by the system and can't be dropped. So the only way to make those casts implicit is to update pg_cast set castcontext = 'i' where castsource in (700,701) and casttarget = 1700.

Now we're into hacking the catalog, and that's a sign that this might be a bad idea. But I don't have any hard evidence that it's bad. Float/double values are inexact, and numeric values are exact, so it seems to me that casting from the former to the latter would be fully data-preserving and therefore logically safe. The only potential problem I know of would be introducing ambiguity into other functions' argument patterns, but the purpose of asking this question is mainly to fish for potential problems I don't know of.

So, is it dangerous to change the float->numeric casting behavior from ASSIGNMENT to IMPLICIT?

Answer 1

Yes, hacking into the catalog is bad. Reason #1 is that if you upgrade to new version and forget to move the hack, things start breaking. Just running pg_dump and loading to the same version on another instance will also lose the hack. There's also always the chance that a new version of Postgres will change so much that your hack is now not possible and force you to go back and re-engineer.

Overriding with your own function is the correct way to go.

Answer 2

Please be aware that different rounding algorithms are used for different data types.

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE

When rounding values, the numeric type rounds ties away from zero, while (on most machines) the real and double precision types round ties to the nearest even number. For example:

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;
  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4

Answer 3

The simple solution would be to cast your value:

SELECT round(some_float_column::numeric, 2);

The result is of data type numeric of course. Cast again, if you need to:

SELECT round(some_float_column::numeric, 2)::float8;

If you must, create a wrapper function like you describe. PostgreSQL supports function overloading. Never hack the catalog for this - see @Matthew's answer for rationale.