Which data type is the result of division？

Question

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？

Answer 1

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. For double 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 to s 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