Обсуждение: round() and ||
Hi.
I'm having a lot of trouble concatenating some fields along witha rounded number. services_cost is a float field field and I want to convert it to 2 decimal places so it appears as money. I could store it as money but mathematical operations are performed on the same field further down the line so I don't want it 'pre-formatted'
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.
The reason I need to do this is so join this field and a text field together
SELECT services_type || '|' || round(services_cost,2) AS services_list FROM services;
but pgsql complains that it can't join a numeric plus a text field obviously. I'm pretty stuck on this one.
Cheers,
Graeme
"Graeme Merrall" <gmerrall@email.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