Skip site navigation (1) Skip section navigation (2)

Re: [SQL] round() and ||

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Graeme Merrall" <gmerrall(at)email(dot)com>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] round() and ||
Date: 2000-01-20 06:34:40
Message-ID: 20312.948350080@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
"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

In response to

pgsql-sql by date

Next:From: T.I.P.Date: 2000-01-20 08:16:48
Subject: inserting values into arrays
Previous:From: Graeme MerrallDate: 2000-01-20 02:42:56
Subject: round() and ||

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group