I have a postgresql database which look like the following :
+---------------+---------------- ------+------------ ---+
| id (bigint) | name (varying(255)) | price (real) |
+---------------+---------------- ------+------------ ---+
| 1 | name 1 | 0.33 |
+---------------+---------------- ------+------------ ---+
| 1 | name 2 | 1.33 |
+---------------+---------------- ------+------------ ---+
| 1 | name 3 | 1 |
+---------------+---------------- ------+------------ ---+
And then the results of my queries :
SELECT * FROM my_table WHERE price = 1 -- OK (one row returned)
SELECT * FROM my_table WHERE price = 1.0 -- OK (one row returned)
SELECT * FROM my_table WHERE price = 1.33 -- FAIL (no row returned)
SELECT * FROM my_table WHERE price = 0.33 -- FAIL (no row returned)
When the value can't be cast to an non-floating value no lines are returned by postgresql.
I can't figure out why. Have you the same problem ? How can I fix this ?
One solution I see is to use explicit cast to real datatype:
SELECT * FROM my_table WHERE price = 0.33::real;
id | name | price
----+--------+-------
1 | name 1 | 0.33
SELECT * FROM my_table WHERE price = 1.33::real;
id | name | price
----+--------+-------
1 | name 2 | 1.33
According to documentation:
A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integer if its value fits in type integer (32 bits); otherwise it is presumed to be type bigint if its value fits in type bigint (64 bits); otherwise it is taken to be type numeric. Constants that contain decimal points and/or exponents are always initially presumed to be type numeric.
Note that:
SELECT 1.33::numeric = 1.33::real;
?column?
----------
f
(1 row)