Limit decimal digits in Double Precision or float8 type in postgresql

Question

I have a postgresql database and am using it for a project which handles monetary value. My question is: How do I limit the number of decimal digits to two for a Double Precision or float8 type column in a postgresql table?

Answer 1

Simply use Decimal (Numeric) type instead, as documented in the manual, e.g. cost decimal(10,2).

The first number (10) defines the total length of the number (all digits, including those after the decimal point), while the second number (2) tells how many of them are after the decimal point. The above declaration gives you 8 digits in front of the point. Of course, you can increase that - the limitations of the Numeric type are much, much higher.

In my opinion there is no need to use floating point when you handle currencies or other monetary-related values. I have no idea about the performance comparison between the two types, but Decimal has one advantage - it is an exact number (which usually is not the case with floating point). I also suggest to read an interesting, but short discussion on the topic.