Most significant digit number formatting

From: Berend Tober <btober(at)seaworthysys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Most significant digit number formatting
Date: 2005-11-15 15:07:03
Message-ID: 4379F997.6050703@seaworthysys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Say I want to format calculated numeric output to uniformly have a
specific number of decimal places, say 3 digits right of the decimal
point. My current understanding is that the TO_CHAR function is the
appropriate way to accomplish this kind of numeric formatting. So for
instance I might write

SELECT
project_number,
TO_CHAR(SUM(labor_hours), '999.999') AS total_hours
FROM labor_data

This is great as far as accomplishing the decimal part formatting, but
it is only good provided I've included enough place holders for the
integer part, i.e., in this example for numbers less than 1000, e.g.,

project_number total_hours
05-100 ###.### (actual value is 10810.5)
05-125 285.000
05-150 404.500
05-200 44.000
05-54 66.000
05-59 ###.### (actual value is 2245.75)

So what I'm asking for is advice on how to create the least-significant
digit formatting specifically, but without having to worry about
exceeding the most-significant digit formatting specification. I don't
see that explained in the documentation on TO_CHAR.

I suppose on approach might to guess what the biggest number might be,
and then include an order of magintude larger, e.g.
TO_CHAR(SUM(labor_hours), '999999.999') . But you know, good old Dr.
Murphy, will undoubtly intervene and inevitably create a situation in
which whatever reasonable provisions were made originally, the limits
will at some point be exceeded, causing the world as we know it to come
to an end.

Regards,
Berend Tober

Attachment Content-Type Size
btober.vcf text/x-vcard 233 bytes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2005-11-15 15:39:27 Re: A good postgresql book
Previous Message Zet 2005-11-15 14:56:27 Get count of records from SELECT