pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
round() and ||
2+ messages / 2 participants
[nested] [flat]

* round() and ||
@ 2000-01-20 02:42  Graeme Merrall <gmerrall@email.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Graeme Merrall @ 2000-01-20 02:42 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] round() and ||
@ 2000-01-20 06:34  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Graeme Merrall <gmerrall@email.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-01-20 06:34 UTC (permalink / raw)
  To: Graeme Merrall <gmerrall@email.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-01-20 06:34 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-20 02:42 round() and || Graeme Merrall <gmerrall@email.com>
2000-01-20 06:34 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox