What determines the data type that PostgreSQL filters?
I am well aware that IEEE 754 single precision and double precision data have their advantages. I have read articles on what every computer scientist should know about floating point arithmetic, but I am puzzled by the choice Postgresql makes.
I am creating a table
create table ttt(f4 float4, f8 float8, fn numeric);
I am adding several values:
insert into ttt (f4,f8,fn) values(12.206,12.206,12.206);
I am making a request:
select count(*) from ttt where f4=12.206;
count
-------
0
(1 row)
I am asking for an explanation:
gsh=# explain select count(*) from ttt where f4=12.206;
QUERY PLAN
----------------------------------------------------------
Aggregate (cost=23.77..23.78 rows=1 width=0)
-> Seq Scan on ttt (cost=0.00..23.75 rows=6 width=0)
Filter: (f4 = 12.206::double precision)
(3 rows)
So the query tries to match the 12.206 double preview version to the value stored as a single criterion, and they are not matching, which is not a surprise.
What's surprising to me is that, by default, postgresql seems to treat numbers as numeric:
gsh=# select 12.206;
?column?
----------
12.206
(1 row)
gsh=# select pg_typeof(12.206);
pg_typeof
-----------
numeric
(1 row)
If numbers are treated as numeric by default, why doesn't the filter compare against 12.206 as numeric rather than double? I consider this a violation of least surprise.
Any thoughts?
source to share
This can be explained by operator permission rules .
As you correctly noted, the type is on the left side of the operator =
in your expression real
, and the type on the right side is numeric
.
There are implicit drops from numeric
to double precision
and real
from real
to double precision
(check the " \dCS
" in psql
). If you examine the available operators =
(check the " \doS =
" c psql
), you will see that after step 3.a. the following operators remain:
real = double precision double precision = double precision
In step 3.c. the first of the operators is selected because it exactly matches the left input type.
You can enforce the operator real = real
using a constant real
as in
... WHERE f4 = 12.206::real
or
... WHERE f4 = REAL '12.206'
source to share