How to display numeric value 0.0 as 0.00 as the query result

Question

The value saved in the database is 0.0 as numeric data type.How to display the numeric value 0.0 as 0.00 as the query result?

Answer 1

SELECT to_char(0.0, '90.99');

See here for documentation.

Answer 2

Formatting a value for being rendered in a specific way in the frontend is not a concern of the database.

Instead, use String#% to control how the float should be formatted in the frontend. In your case, when you want to render the float with two digits, you can use this formatting rule:

float = 0.0
"%0.2f" % float
#=> "0.00"

Answer 3

The answer to this depends on your datastore. Some will support what you want, and some won't.

First comment by Error_2646 got it right, at least with postgres. Something like select cast(0.0 as decimal(18,2)) will indeed work. Or as numeric, as they're equivalent. The first number (18 in this case) is the scale, or the maximum number of digits left of the decimal point. The second (2) is the precision, or the number of digits returned to the right of the decimal point.

The arguments that it "shouldn't" be done strike me as sort of hollow. Sometimes there's more than one front end. Sometimes the database is just more efficient.

Alternatively, and perhaps more efficiently, the data could be stored with a numeric/decimal with the required precision in the first place. Then you wouldn't need to cast anything in your query.