Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
None of these answers even try to answer the question: Why won't Postgres implicitly cast double precision to numeric type for function matching. According to 10.3.4 of the manual "Function Type Resolution - Look for the best match" postgresql.org/docs/current/static/typeconv-func.html it seems like it should. –  user1532557 Feb 4 at 16:51
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.