On Mon, Mar 14, 2016 at 8:22 AM, David G. Johnston < [email protected]> wrote:
> On Mon, Mar 14, 2016 at 3:31 AM, Francisco Olarte <[email protected]> > wrote: > >> Hi; >> >> On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston >> <[email protected]> wrote: >> > On Sunday, March 13, 2016, Ken Tanzer <[email protected]> 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/ <http://agency-software.org/>* *https://agency-software.org/demo/client <https://agency-software.org/demo/client>* [email protected] (253) 245-3801 Subscribe to the mailing list <[email protected]?body=subscribe> to learn more about AGENCY or follow the discussion.
