PostgreSQL- Round REAL data type (yes, I know numeric exist)-CodePudding

I know REAL data type is not accurate and normally for currency I should use numeric data type. But, I'm asked to do some stuff and one of the conditions is that the data type is real. When I try to do round((....),2) for example, I get that round function does not exist for this data type. My question is, without converting, is there any function that can return a REAL value rounded to 0?

Many thanks!1

CodePudding user response：

As you can see here it's no way to round without any type cast. It's only two kinds of function exists:

round(dp or numeric) - round to nearest integer

round(v numeric, s int) - round to s decimal places

Real = double precision. So you need to use convert anyway if you want to get some decimal places:

select round('123.456789'::real::numeric,2)

upd. Keep care about rounding cast at big real numbers:

select round('12122156.567'::real::numeric, 2); --< rounding up to 6 digits, result = 12122200
select round('12122156.567'::real::DOUBLE PRECISION::numeric,2); --<< rounding result = 12122157

Or you can use round without decimal places:

select round('123.456789'::real)

CodePudding user response：

round a numeric value to 0 after the dot?

ROUND(numeric_value, 0)

CodePudding user response：

After investigation, converting to ::numeric is the only way around