Well, let's solve the problem, adding an exotic suggestion. Some people argue that it doesn't make sense for PostgreSQL to round a number of float datatype, because float is a binary representation, it requires rounding the number of bits or its hexadecimal representation.
Round to the nth bit or other numeric representation So, although it is not standard for PostgreSQL, it can be standard for your projects, by a centralized and reusable "library of snippets", like pg_pubLib.
Postgres sequel code#
Is something like copy/paste small piece of code instead of use a function call.Ĭonclusion: the above ROUND(float,int) function, after optimizations, is so fast than answer it will compile to (exactly) the same internal representation. For JIT optimizations (and sometimes for parallelism) language SQL can obtain better optimizations. PLpgSQL is the preferred language, except for "pure SQL". The IMMUTABLE clause is very important for code snippets like this, because, as said in the Guide: "allows the optimizer to pre-evaluate the function when a query calls it with constant arguments" Two precautions must be taken when implementing user-defined cast functions for high performance: The build-in functions, such as ROUND of the pg_catalog, can be overloaded with no performance loss, when compared to direct cast encoding. There are a lack of overloads in some PostgreSQL functions, why (?): I think "it is a lack" (!), but and the PostgreSQL team agree about "pg's historic rationale". The to_char function apply internally the round procedure, so, when your aim is only to show a final result in the terminal, you can use the FM modifier as a prefix to a numeric format pattern: SELECT round(x::numeric,2), trunc(x::numeric,2), to_char(x, 'FM99.99') The pg_catalog functions are the default ones, see at Guide the build-in math functions.
Where float is synonymous of double precision and myschema is public when you not use a schema. Pg_catalog | round | numeric | numeric, int PS: the command \df round, on psql after overloadings, will show something like this table Other alternative, overloading ROUND function again, and using all range of accuracy-precision of a floating point number, is to return a float when the accuracy is defined (see IanKenney's answer), CREATE FUNCTION ROUND(Īccuracy float - accuracy, the "counting unit" An alternative is to use round(f,3)::float or to create a round_tofloat() function. In another applications we need a float also as result. The ROUND(float,int) function is f_round, it returns a (decimal) NUMERIC datatype, that is fine for some applications: problem solved! The FM prefix tells to_char that you don't want any padding with leading spaces. To_char will round numbers for you as part of formatting. Use to_char (see: data type formatting functions in the manual), which lets you specify a format and gives you a text result that isn't affected by whatever weirdness your client language might do with numeric values. If you're formatting for display to the user, don't use round. Just append ::numeric for the shorthand cast, like round(val::numeric,2). You must cast the value to be rounded to numeric to use the two-argument form of round. You can see that PostgreSQL is expanding it in the output).
(In the above, note that float8 is just a shorthand alias for double precision. Regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2) Pg_catalog | round | numeric | numeric, integer | normal Pg_catalog | round | numeric | numeric | normal Pg_catalog | round | double precision | double precision | normal Pg_catalog | dround | double precision | double precision | normal Schema | Name | Result data type | Argument data types | Type regress=> SELECT round( float8 '3.1415927', 2 ) ĮRROR: function round(double precision, integer) does not exist For reasons Sherrill 'Cat Recall' explains in the comments, the version of round that takes a precision is only available for numeric. PostgreSQL does not define round(double precision, integer).
0 kommentar(er)
