From: | Erik Wienhold <ewie(at)ewie(dot)name> |
---|---|
To: | magog002(at)web(dot)de, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour |
Date: | 2023-03-15 16:50:40 |
Message-ID: | 1053334888.488227.1678899040820@office.mailbox.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-03-15 17:19:50 | Numeric Division - Result Scale Calculation Oddity |
Previous Message | Arthur Ramsey | 2023-03-15 16:24:25 | Re: Table scan on 15.2 |