PostgreSQL Interview Question: difference between numeric vs float ?

Hi everyone, this is a random short post about PostgresSQL. When I worked on Postgres, we can easily ignore the difference between numeric and float data type. Both seems representing double but in Postgres, you have to be careful.

“Disclaimer: The views and opinions expressed in this blog post are solely my own and do not reflect those of any entity with which I have been, am now, or will be affiliated. This content was written during a period in which the author was not affiliated with nor belong to any organization that could influence their perspectives. As such, these are author’s personal insights, shared without any external bias or influence.”

Here are some examples why you have to be careful when you cast your value to either numeric or float.

ROUND() function — different behavior between float and numeric

The behavior of ROUND() is different between numeric and Float. Usually, what you expect from Microsoft Excel’s ROUND( value, number of digit you want to round) is for NUMERIC data type.

If you use float, it only round up to 0 by default. It means you cannot control the digits.

--suppose value is 123.456
SELECT ROUND(value::float); -- Returns 123