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.
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...
...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?