From gmerrall@email.com Thu Jan 20 03:16:37 2000 Received: from merlin.inspire.net.nz (merlin.inspire.net.nz [203.96.157.130]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA70715 for ; Wed, 19 Jan 2000 22:16:37 -0500 (EST) (envelope-from gmerrall@email.com) Received: from home (james.inspire.net.nz [203.96.157.137]) by merlin.inspire.net.nz (8.9.3/8.9.3) with SMTP id QAA12708 for ; Thu, 20 Jan 2000 16:15:27 +1300 (NZDT) Message-ID: <000f01bf62f0$0ebfff00$899d60cb@inspire.net.nz> From: "Graeme Merrall" To: Subject: round() and || Date: Thu, 20 Jan 2000 15:42:56 +1300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000C_01BF635D.05A87E20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 This is a multi-part message in MIME format. ------=_NextPart_000_000C_01BF635D.05A87E20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=20 SELECT round(services_cost,2)::text as test_field FROM services as various other variations does not.=20 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 ------=_NextPart_000_000C_01BF635D.05A87E20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi.
I'm having a lot of trouble = concatenating some=20 fields along witha rounded number. services_cost is a float field field = and I=20 want to convert it to 2 decimal places so it appears as money. I could = store it=20 as money but mathematical operations are performed on the same field = further=20 down the line so I don't want it 'pre-formatted'
 
SELECT round(services_cost,2) as = test_field FROM=20 services
works fine but
SELECT round(services_cost,2)::text as = test_field=20 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=20 obviously. I'm pretty stuck on this one.
 
Cheers,
 Graeme
------=_NextPart_000_000C_01BF635D.05A87E20-- From tgl@sss.pgh.pa.us Thu Jan 20 06:34:45 2000 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