Aw: Re: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour

Hello Erik,

many thanks for the feedback (Oracle) and the second option to get rid of the decimal separator character.
The case is closed.

Kind regards
Juergen

> Gesendet: Mittwoch, 15. März 2023 um 17:50 Uhr
> Von: "Erik Wienhold" <ewie(at)ewie(dot)name>
> An: magog002(at)web(dot)de, pgsql-general(at)lists(dot)postgresql(dot)org
> Betreff: Re: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour
>
> > On 15/03/2023 14:51 CET magog002(at)web(dot)de wrote:
> >
> > I want to remove not needed decimal places / trailing zeros from a numeric.
> > I know this can be done starting PG >=13 with TRIM_SCALE(numeric) which would
> > solve my issue (with an additional CAST to TEXT at the end). Unfortunately
> > the production database is still running with PostgreSQL 12.x and this is
> > something I currently can't change.
> >
> > So to get rid of the not needed decimal places I tried TO_CHAR(..., 'FM....')
> > in combination with TRUNC() as shown below with examples. This does not remove
> > the decimal places separator if the complete scale digits are zero (60.000).
>
> Cast the to_char result to numeric and then to text. This will also remove
> trailing zeros.
>
> select
> to_char('60.000'::numeric, 'FM999.999')::numeric::text,
> to_char('60.100'::numeric, 'FM999.999')::numeric::text;
>
> to_char | to_char
> ---------+---------
> 60 | 60.1
> (1 row)
>
> > The current behaviour might be intentional but it 'smells like a bug' to me.
>
> It follows Oracle's to_char behavior:
>
> select to_char('60.000', 'FM999.999') from dual;
>
> TO_CHAR('60.000','FM999.999')
> -----------------------------
> 60.
>
> --
> Erik
>
>
>