Understand rounded results after division involving floating point types

Question

In Postgres 14, I see rounded results that do not make sense to me. Trying to understand what is going on. In all cases I am dividing 19 by 3.

Casting either integer value to a real:

SELECT  19::real /3;

yields a value of 6.333333333333333

SELECT 19/3::real;

yields a value of 6.333333333333333

However, casting both sides to real yields:

SELECT 19::real/3::real;  

yields a value of 6.3333335

Interestingly enough, if I cast both sides to double precision or float, the answer is 6.333333333333333

Also

SELECT 19.0 / 3.0;

yields 6.333333333333333

SELECT 19.0::real / 3.0::real;

yields 6.3333335

SELECT ( 16.0 / 3.0) :: real;

yields 6.3333335

I now see that:

SELECT 6.333333333333333::real;

yields 6.33333335

So the real issue seems to be:

1. Why are we rounding in this weird way (I know that real / floats are inexact but this seems extreme.)

2. What data type is 19::real / 3;

Answer 1

Why are we rounding in this weird way? (I know that real / floats are inexact but this seems extreme.)

Because real (float4) only uses 4 bytes for storage, and that's the closest possible value it can encode.

What data type is 19::real / 3;?

Check with pg_typeof() if your client does not indicate the column type (like pgAdmin4 does).

test=> SELECT pg_typeof(19::real / 3);
    pg_typeof     
------------------
 double precision
(1 row)

test=> SELECT pg_typeof(19/3::real);
    pg_typeof     
------------------
 double precision
(1 row)

test=> SELECT pg_typeof(19::real/3::real);
 pg_typeof 
-----------
 real
(1 row)

This is the complete list of available division operators involving real:

test=> SELECT oprleft::regtype, oprright::regtype, oprresult::regtype
test-> FROM   pg_operator
test-> WHERE  oprname = '/'
test-> AND    'real'::regtype IN (oprleft, oprright);
     oprleft      |     oprright     |    oprresult     
------------------+------------------+------------------
 real             | real             | real
 money            | real             | money
 real             | double precision | double precision
 double precision | real             | double precision
(4 rows)

For combinations of types that have no exact match here, Postgres finds the closest match according to its operator resolution rules. Postgres aims to preserve precision, so the only division that produces real is real / real. All other variants produce double precision (float8). (money being a corner case exception.)