[SOLVED] Postgresql decimal point using to

Postgresql decimal point using to_char

I want make a select that adds decimal point into integers, but when I do it, it shows me bunch of # instead of those numbers.

SELECT to_char(1234, '99.99');

What I expected was table with a value of 12.34 but I got ##.## in my select. However, if I did

SELECT to_char(1234, '99,99');

it showed be 12,34 as expected. Problem is, that I want to have a decimal point and not a comma. I am using PostgreSQL 13.2

Solution

It seems you want to take the last 2 digits and pretend they were decimals. You can't use the predefined . or D formats because they apply to true decimals.

Instead, you can print the dot character (like any other string), between double quotes, before the last 2 digits:

 SELECT to_char(1234, '999"."99');
 to_char
---------
   12.34

PS: on a side note, you are getting the masked output in your 1st query because there isn't enough digit positions on the format:

 SELECT to_char(1234, '9999.99');
 to_char
----------
  1234.00