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

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