postgresql suffixing .00 when round produces whole numbers

Question

I use postgresql 9.3. When I round off whole numbers, I want to get results by suffixing .00.

SELECT ROUND(12345, 2) //Result:12345

How can I get a result like 12345.00

I found numeric formatting functions like TO_CHAR can generate such a result.

SELECT TO_CHAR(ROUND(12345, 2), '99999D99') // 12345.00 

Is there a way to get result in numeric data type itself?

Answer 1

No, you cannot and there is also no point. What you are after is a certain representation of a numeral and that is exactly what the to_char() function does. PG does not care about a .00 suffix, it's all bits on the inside anyway.

However, if you want to work with an exact representation of a real number, you should use the numeric data type with the appropriate modifiers such as:

12345::numeric(7,2)

Keep in mind though that the numeric data type is very slow compared to the other numeral types such as integer and double.

Answer 2

This answer has 5 paragraphs.

This answer also has 5.0 paragraphs. Or 5.000.

The number of paragraphs in this answer doesn't change depending on whether you say it is 5 or 5.00, just the string used to represent the number.

ROUND returns a number, not a string, so you can't say it did or didn't have a suffix, because numbers don't have suffices. It does have a precision of 2, and e.g. my trying it on pgAdmin III shows it as 12345.00, but that's up to the tool used with the value examining the precision or not, not to the number.

You can force a value val to a precision with val::numeric(1000, 2) and so on, but this is already done by ROUND, so it's still a matter of whether a given tool will pay attention.