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 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).
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 something or 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 changed to '.' 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 to the 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 option I 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
