Postgresql decimal point using to_char

user_13184210 0
Viewed 635

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

postgresql to-char sql
2 Answers

Welcome to SO. You were very close:) Which locale are you using? Check this example for en_US.UTF-8:

SELECT to_char(1234, '999G99');
 to_char 
---------
   12.34
(1 row)
  • G: Group separator that uses locale
  • D: Decimal point that uses locale

Check this tutorial

user_22753880

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
user_76355690
Related