Thread: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour
Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour
From
magog002@web.de
Date:
Hi,
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
toTEXT 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
belowwith examples.
This does not remove the decimal places separator if the complete scale digits are zero (60.000).
The current behaviour might be intentional but it 'smells like a bug' to me.
I've solved it with a workaround (scroll down to the end to find the REGEXP_REPLACE solution) but maybe I'm missing
somethingor this is a bug.
For the examples below I tested them on PostgreSQL 12 (Windows) and PostgreSQL 12, 13, and 14 (Linux) and get the same
result.
Examples using TRIM_SCALE():
============================
60.100 --> SELECT TRIM_SCALE(60.100); --> 60.1 (expected)
60.000 --> SELECT TRIM_SCALE(60.000); --> 60 (expected)
Examples using to_char(TRUNC(...), 'FM....'):
=============================================
60.100 --> SELECT TO_CHAR(TRUNC(60.100, 3), 'FM99999999.999'); --> '60.1' (expected)
60.000 --> SELECT TO_CHAR(TRUNC(60.000, 3), 'FM99999999.999'); --> '60.' (NOT expected)
For 60.000 I expected the result to be '60' and not '60.'!
Another try with a small change in the formatting string ('D' as locale specific decimal places separator --> which is
','in this case).
60.100 --> SELECT TO_CHAR(TRUNC(60.100, 3), 'FM99999999D999'); --> '60,1' (expected)
60.000 --> SELECT TO_CHAR(TRUNC(60.000, 3), 'FM99999999D999'); --> '60,' (NOT expected)
For 60.000 I expected the result to be '60' and not '60,'!
Additional notes:
=================
lc_numeric is set to 'German_Germany.1252' on my local Windows 10 installation and 'de_DE.utf8' on all the Linux
machines(PROD/DEV).
I also changed the lc_numeric to 'C' to validate the behaviour. Beside that my ',' with 'FM99999999D999' is then
changedto '.' the result stays the same. Again this was expected.
The local Windows installation is running PostgreSQL 12 and 14.
The Linux installations (Ubuntu-LTS) are running PostgreSQL 12 (PROD/DEV), 13 (DEV) and 14 (DEV).
The final questions:
========================
1.) Is this really the 'expected behaviour' to keep the decimal places separator if there are no following digits due
tothe usage of a formatting string fill mode ('FM....')?
2.) Is there an option for the TO_CHAR formatting to make the decimal places separator 'optional'?
I've not seen anything like that in the documentation
(https://www.postgresql.org/docs/current/functions-formatting.html).
3.) Beside some ugly check to replace rightmost character (if not a number/if it is '.') is there some other elegant
optionI do not see?
My most elegant option currently looks like this (using REGEXP_REPLACE):
Include everything but a '.' at the end of the character and return everything but that):
SELECT REGEXP_REPLACE('60.', '^(.*)\.$', '\1');
or in it's full glory:
SELECT REGEXP_REPLACE(TO_CHAR(TRUNC(60.100, 3), 'FM99999999.999'), '^(.*)\.$', '\1'); --> '60.1'
SELECT REGEXP_REPLACE(TO_CHAR(TRUNC(60.000, 3), 'FM99999999.999'), '^(.*)\.$', '\1'); --> '60'
SELECT REGEXP_REPLACE(TO_CHAR(TRUNC(60.012, 3), 'FM99999999.999'), '^(.*)\.$', '\1'); --> '60.012'
Many thanks in advance!
Kind regards
Juergen
Re: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour
From
Erik Wienhold
Date:
> 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
Aw: Re: Removing trailing zeros (decimal places) from a numeric (pre trim_scale()) with unexpected behaviour
From
magog002@web.de
Date:
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@ewie.name>
> An: magog002@web.de, pgsql-general@lists.postgresql.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@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
>
>
>