Re: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour - Mailing list pgsql-general

> On 15/03/2023 14:51 CET magog002@web.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