Re: [GENERAL] Suppress decimal point like digits in to_char?

[Francisco Olarte]

HI:

On Mon, Mar 14, 2016 at 8:02 PM, Ken Tanzer  wrote:
>> price:
>> --
>> xx5.45
>> xx1.20
>> 99
>> xx2.40
...
> I appreciate the comment and explanation.  But your example shows numbers 
> where the trailing 0s are not suppressed.

Yeah, my fault, but 5.45, 1.25, 99.00, 2.45 will create a that kind of
alignment which stronly suggest its .99. Of course it's not printed as
such, but visually it can trick you, that's why decimal points are
never supressed anc softwar has options to align coluns to the decimal
point.

> It seems to me that if you're requesting suppression of trailing 0s, then 
> you're accepting that your numbers aren't going to align in the first place. 
> And so it's hard for me to see how, for example "99." is ever going to be 
> desirable output if suppression is what you're after.

Never desirable for me, but I never use d9, I always do d0, but you are right.

> And just as context on my end, the times I use to_char are generally to merge 
> numbers into a document or some fragment of English text.

For the grouping ',' I see your point, I normally just use defaut
conversion for these as I dislike the grouping.

As I said, I could see a legitimitate case for Dd similar to the 09
stuff, but having so many replace options ( I think you can even do
, which is
easier on the eye but fails on locales, as the regexp does ( as a
note, in Spain they are inverted, dot for grouping comma for decimals
) ) I do not think it's a big deal, uglier things are coded by me
continuously nearly via muscle memory.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Suppress decimal point like digits in to_char?

[Ken Tanzer]

>
>
> Well, this may be a good enhancement request, add something like
> d=decimal point, supressed if alone.
>
> Yeah. Maybe that's all that need to be said. :)


> > In particular, one might reasonably choose a format string like
> 'FM999,999D99' and not realize it will fail on whole numbers.  Is there any
> particular reason the D is not suppressible in this case, either by default
> or as an option?  It seems to me if the trailing 0s are suppressed, the
> decimal should follow suit for whole numbers.
>
> It does not fail, it just works in a diffrent way of what you would
> like. Regarding supression, IMO it's a bad thing, it can lead to
> misleading results. Imagine it is, and you do a right aligned print (
> usual for numbers ) of prices 5.45, 1.20, 99.00, 2.40, and you end up
> with ( using x for align )
> price:
> --
> xx5.45
> xx1.20
> 99
> xx2.40
>
> It would be misleading, I prefer to have xxx99., ugly but clearer IMO
> ( of course one never supress decimals in prices, so I would use
> 990D00, but anyway ).
>
> Francisco Olarte.
>

I appreciate the comment and explanation.  But your example shows numbers
where the trailing 0s are not suppressed.   It seems to me that if you're
requesting suppression of trailing 0s, then you're accepting that your
numbers aren't going to align in the first place. And so it's hard for me
to see how, for example "99." is ever going to be desirable output if
suppression is what you're after.

And just as context on my end, the times I use to_char are generally to
merge numbers into a document or some fragment of English text.  For
anything going into a table, I'd usually just leave it as a number and case
it to the desired (fixed) number of decimals.  And as we've touched on, it
may just be different use cases colliding! :)

Cheers,
Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Suppress decimal point like digits in to_char?

[Francisco Olarte]

Hi Ken:

On Mon, Mar 14, 2016 at 7:33 PM, Ken Tanzer  wrote:

> Thanks for all the info and suggestions.  I'll just observe that sure, you 
> can do it with a regex, but I'm still surprised that this can't be done with 
> to_char.

Well, this may be a good enhancement request, add something like
d=decimal point, supressed if alone.

> In particular, one might reasonably choose a format string like 
> 'FM999,999D99' and not realize it will fail on whole numbers.  Is there any 
> particular reason the D is not suppressible in this case, either by default 
> or as an option?  It seems to me if the trailing 0s are suppressed, the 
> decimal should follow suit for whole numbers.

It does not fail, it just works in a diffrent way of what you would
like. Regarding supression, IMO it's a bad thing, it can lead to
misleading results. Imagine it is, and you do a right aligned print (
usual for numbers ) of prices 5.45, 1.20, 99.00, 2.40, and you end up
with ( using x for align )
price:
--
xx5.45
xx1.20
99
xx2.40

It would be misleading, I prefer to have xxx99., ugly but clearer IMO
( of course one never supress decimals in prices, so I would use
990D00, but anyway ).

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Suppress decimal point like digits in to_char?

[Ken Tanzer]

On Mon, Mar 14, 2016 at 8:22 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Mar 14, 2016 at 3:31 AM, Francisco Olarte 
> wrote:
>
>> Hi;
>>
>> On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston
>>  wrote:
>> > On Sunday, March 13, 2016, Ken Tanzer  wrote:
>> 
>> > Typically if I'm going to format any currency amount with pennies I
>> would
>> > format all values, even those with zero pennies, to the same precision.
>> > Typically when displaying such amounts I'd right-justify the values and
>> thus
>> > cause the decimals to line up.
>>
>> But a right-aligning string output routine needs to be used.
>>
>>
>> cdrs=> select val, tc, '"'||tc||'"' as quoted,
>> '"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
>> to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
>> (1234),(1.05),(0)) as v(val)) as w;
>>  val  |   tc   |  quoted  |  replaced
>> --++--+
>>  1234 | 1,234. | "1,234." | "1,234   "
>>  1.05 | 1.05   | "1.05"   | "1.05"
>> 0 | 0. | "0." | "0   "
>> (3 rows)
>>
>> Summarising, any combination can be easily done with a single round of
>> replace.
>>
>>
> ​See also:
>
> http://www.postgresql.org/docs/9.5/interactive/functions-string.html
>
> ​
> format(formatstr text [, formatarg "any" [, ...] ])
>
> ​David J.​
>
>
>
Thanks for all the info and suggestions.  I'll just observe that sure, you
can do it with a regex, but I'm still surprised that this can't be done
with to_char.

In particular, one might reasonably choose a format string
like 'FM999,999D99' and not realize it will fail on whole numbers.  Is
there any particular reason the D is not suppressible in this case, either
by default or as an option?  It seems to me if the trailing 0s are
suppressed, the decimal should follow suit for whole numbers.

Cheers,
Ken




-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Suppress decimal point like digits in to_char?

[Francisco Olarte]

Hi David:

On Mon, Mar 14, 2016 at 4:22 PM, David G. Johnston
 wrote:

>> But a right-aligning string output routine needs to be used.
...

>> Summarising, any combination can be easily done with a single round of
>> replace.

> See also:
> http://www.postgresql.org/docs/9.5/interactive/functions-string.html
> format(formatstr text [, formatarg "any" [, ...] ])

You mean to use it to right align the replaced string ( i.e.
format('%12s',replace(...)) ) or is there a code I do not know off
which can be used to achieve the global result ( I use it but it seems
to be like a restricted sprintf which can not do the supress the zero
stuff ) ?

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Suppress decimal point like digits in to_char?

[David G. Johnston]

On Mon, Mar 14, 2016 at 3:31 AM, Francisco Olarte 
wrote:

> Hi;
>
> On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston
>  wrote:
> > On Sunday, March 13, 2016, Ken Tanzer  wrote:
> 
> > Typically if I'm going to format any currency amount with pennies I would
> > format all values, even those with zero pennies, to the same precision.
> > Typically when displaying such amounts I'd right-justify the values and
> thus
> > cause the decimals to line up.
>
> But a right-aligning string output routine needs to be used.
>
>
> cdrs=> select val, tc, '"'||tc||'"' as quoted,
> '"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
> to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
> (1234),(1.05),(0)) as v(val)) as w;
>  val  |   tc   |  quoted  |  replaced
> --++--+
>  1234 | 1,234. | "1,234." | "1,234   "
>  1.05 | 1.05   | "1.05"   | "1.05"
> 0 | 0. | "0." | "0   "
> (3 rows)
>
> Summarising, any combination can be easily done with a single round of
> replace.
>
>
​See also:

http://www.postgresql.org/docs/9.5/interactive/functions-string.html

​
format(formatstr text [, formatarg "any" [, ...] ])

​David J.​

Re: [GENERAL] Suppress decimal point like digits in to_char?

[Francisco Olarte]

Hi;

On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston
 wrote:
> On Sunday, March 13, 2016, Ken Tanzer  wrote:

> Typically if I'm going to format any currency amount with pennies I would
> format all values, even those with zero pennies, to the same precision.
> Typically when displaying such amounts I'd right-justify the values and thus
> cause the decimals to line up.

I do format with the .00 too, just wanted to point that the lining up
is easy if you just substitute '.00$' or '\.$' with the correct amount
of space, something like:

s=> select val, tc, '"'||tc||'"' as quoted,
regexp_replace(tc,'\.00$','   ') as replaced from (select val,
to_char(val::decimal(6,2),'999,999D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  | tc  |quoted |  replaced
--+-+---+-
1 |1.00 | "   1.00" |1
 1.05 |1.05 | "   1.05" |1.05
0 | .00 | ".00" |
(3 rows)

Although I dislike 0 as space, so I normally use '0':

s=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.00$','   ')||'"' as replaced from (select
val, to_char(val::decimal(6,2),'999,990D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  | tc  |quoted |   replaced
--+-+---+---
1 |1.00 | "   1.00" | "   1   "
 1.05 |1.05 | "   1.05" | "   1.05"
0 |0.00 | "   0.00" | "   0   "
(3 rows)

And, if you want to use FM but make them line up on the right is doable too:

s=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  |  tc  | quoted | replaced
--+--++--
1 | 1.   | "1."   | "1   "
 1.05 | 1.05 | "1.05" | "1.05"
0 | 0.   | "0."   | "0   "
(3 rows)

But a right-aligning string output routine needs to be used.


cdrs=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
(1234),(1.05),(0)) as v(val)) as w;
 val  |   tc   |  quoted  |  replaced
--++--+
 1234 | 1,234. | "1,234." | "1,234   "
 1.05 | 1.05   | "1.05"   | "1.05"
0 | 0. | "0." | "0   "
(3 rows)

Summarising, any combination can be easily done with a single round of replace.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Suppress decimal point like digits in to_char?

[David G. Johnston]

On Sunday, March 13, 2016, Ken Tanzer  wrote:

>
> On Mar 13, 2016 6:29 PM, "David G. Johnston"  > wrote:
> >
> > On Sunday, March 13, 2016, Ken Tanzer  > wrote:
> >>
> >> Hi.  Is there a way with to_char to suppress a decimal point, like a
> leading or trailing 0, so that integers will not have them, but non-ints
> will?  I'm hoping I'm missing something easy.  Thanks.
> >>
> >> Ken
> >>
> >> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
> >> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
> >>
> >>  val  | to_char
> >> --+-
> >> 1 | 1.
> >>  1.05 | 1.05
> >>
> >>
> >
> > Not seeing a native way to do so - and I'd question doing so as a
> general rule - though you know your domain.  If you must have this you will
> want to utilize regexp_replace to identify the situation and replace it.  A
> simple "\.$" check and a substring would work also.
> >
> > David J.
>
> Thanks David. Just curious what part of this you would question.  The case
> for numbers, currency in particular, coming out with a decimal and pennies
> when present, and as whole dollars when not (and without a decimal place at
> the end) seems pretty common and clear cut.  What am I missing in your
> question?
>

Typically if I'm going to format any currency amount with pennies I would
format all values, even those with zero pennies, to the same precision.
Typically when displaying such amounts I'd right-justify the values and
thus cause the decimals to line up.

David J.

Re: [GENERAL] Suppress decimal point like digits in to_char?

[Ken Tanzer]

On Mar 13, 2016 6:29 PM, "David G. Johnston" 
wrote:
>
> On Sunday, March 13, 2016, Ken Tanzer  wrote:
>>
>> Hi.  Is there a way with to_char to suppress a decimal point, like a
leading or trailing 0, so that integers will not have them, but non-ints
will?  I'm hoping I'm missing something easy.  Thanks.
>>
>> Ken
>>
>> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
>> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
>>
>>  val  | to_char
>> --+-
>> 1 | 1.
>>  1.05 | 1.05
>>
>>
>
> Not seeing a native way to do so - and I'd question doing so as a general
rule - though you know your domain.  If you must have this you will want to
utilize regexp_replace to identify the situation and replace it.  A simple
"\.$" check and a substring would work also.
>
> David J.

Thanks David. Just curious what part of this you would question.  The case
for numbers, currency in particular, coming out with a decimal and pennies
when present, and as whole dollars when not (and without a decimal place at
the end) seems pretty common and clear cut.  What am I missing in your
question?

Cheers,
Ken

Re: [GENERAL] Suppress decimal point like digits in to_char?

[David G. Johnston]

On Sunday, March 13, 2016, Ken Tanzer  wrote:

> Hi.  Is there a way with to_char to suppress a decimal point, like a
> leading or trailing 0, so that integers will not have them, but non-ints
> will?  I'm hoping I'm missing something easy.  Thanks.
>
> Ken
>
> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
>
>  val  | to_char
> --+-
> 1 | 1.
>  1.05 | 1.05
>
>
>
Not seeing a native way to do so - and I'd question doing so as a general
rule - though you know your domain.  If you must have this you will want to
utilize regexp_replace to identify the situation and replace it.  A simple
"\.$" check and a substring would work also.

David J.

[GENERAL] Suppress decimal point like digits in to_char?

[Ken Tanzer]

Hi.  Is there a way with to_char to suppress a decimal point, like a
leading or trailing 0, so that integers will not have them, but non-ints
will?  I'm hoping I'm missing something easy.  Thanks.

Ken

SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;

 val  | to_char
--+-
1 | 1.
 1.05 | 1.05


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.