UPDATE some_table SET some_float_field=1919.987 WHERE id=123
SELECT * FROM some_table WHERE id=123
where some_float_field is a field defined as "float" (without any specific size values).
The expected resulting value would be 1919.987; instead, it is rounded to 1919.99
Why? A 32bit (single precision) float has enough precision for storing that correctly!
When you run the query:
SELECT * FROM some_table WHERE id = 123
You are relying on the user interface to format the floating point numbers. The interface you are using is using two characters rather than more. After all, there is no information on the "right" number to show.
You can convince the interface to show the right number by formatting the number as a string or as a decimal. For instance:
select format(some_float_field, 3)
will convert this to a string with three decimal places. One caution: it will also add commas which you might not want. This should also work:
select cast(some_float_field as decimal(8, 3))
Note that you can readily validate that the data is correct by doing something like:
select *
from some_table
where some_float_field between 1919.987 - 0.0001 and 1919.987 + 0.0001;
Note that you don't want to use = on floating point values, but you already understand that.