Re: [SQL] round() and ||

"Graeme Merrall" <gmerrall(at)email(dot)com> writes:
> SELECT round(services_cost,2) as test_field FROM services
> works fine but
> SELECT round(services_cost,2)::text as test_field FROM services
> as various other variations does not.

round() is actually a NUMERIC function --- implicitly, your float data
value is being converted to numeric, and then round() is applied.
The problem here is that there's no direct conversion from type numeric
to type text. There should be one :-(

In the meantime, it seems to work to coerce the numeric result back
to float:

select float8(round(33.337,2))::text;
33.34

select float8(round(33.337,2)) || ' more';
33.34 more

This depends on the reconversion to float not introducing an
unreasonable amount of error --- but if you were willing to store the
data as float in the first place, I hope that will be acceptable.

regards, tom lane