Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA42126 for ; Thu, 20 Jan 2000 01:34:45 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id BAA20315; Thu, 20 Jan 2000 01:34:41 -0500 (EST) To: "Graeme Merrall" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] round() and || In-reply-to: <000f01bf62f0$0ebfff00$899d60cb@inspire.net.nz> References: <000f01bf62f0$0ebfff00$899d60cb@inspire.net.nz> Comments: In-reply-to "Graeme Merrall" message dated "Thu, 20 Jan 2000 15:42:56 +1300" Date: Thu, 20 Jan 2000 01:34:40 -0500 Message-ID: <20312.948350080@sss.pgh.pa.us> From: Tom Lane "Graeme Merrall" 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