List the round function in postgres:
\df round
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+------
pg_catalog | round | double precision | double precision | func
pg_catalog | round | numeric | numeric | func
pg_catalog | round | numeric | numeric, integer | func
The argument for round must be double precision,numeric,integer.
select 6::float/3.3 as number;
number
--------------------
1.8181818181818183
(1 row)
If its data type is double precision :
select round(6::float/3.3,4) as number;
ERROR: function round(double precision, integer) does not exist
LINE 1: select round(6::float/3.3,4) as number;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Time: 0.517 ms
Which data type is the result of division----1.8181818181818183
?
You can always check things like that with pg_typeof()
: demo
select pg_typeof(6::float/3.3) as number_type;
number_type |
---|
double precision |
The reason your function call didn't work is that \df
showed you three different round()
functions. The first two accept exactly one argument:
round ( numeric ) → numeric
round ( double precision ) → double precision
Rounds to nearest integer. For
numeric
, ties are broken by rounding away from zero. Fordouble precision
, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule.
and only the one accepting numeric
has a variant with a 2nd argument, integer
, to specify how
many decimal places you want to round to.
round ( v numeric,
s integer
) → numeric
Rounds
v
tos
decimal places. Ties are broken by rounding away from zero.
So if you cast to ::numeric
instead or don't cast at all and let PostgreSQL presume numeric
by default, you can get that one to get picked:
select round(6/3.3,4) as number;
number |
---|
1.8182 |
Here's the doc on result types in mixed-type mathematical operations:
Calls involving multiple argument data types, such as
integer
+numeric
, are resolved by using the type appearing later in these lists.
The list in question being "smallint
, integer
, bigint
, numeric
, real
, and double precision
". Here's a cheatsheet:
divident | /smallint divisor | /integer divisor | /bigint divisor | /numeric divisor | /real divisor | /double precision divisor |
---|---|---|---|---|---|---|
smallint | smallint | integer | bigint | numeric | double precision | double precision |
integer | integer | integer | bigint | numeric | double precision | double precision |
bigint | bigint | bigint | bigint | numeric | double precision | double precision |
numeric | numeric | numeric | numeric | numeric | double precision | double precision |
real | double precision | double precision | double precision | double precision | real | double precision |
double precision | double precision | double precision | double precision | double precision | double precision | double precision |