Postgresql decimal point using to_char

Question

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

That makes no sense. 1234 has no digits after the decimal point. It would work for 12.34. — Laurenz Albe, May 20 '21 at 14:35

Jim Jones · Answer 1 · 2021-05-20T14:06:31.187

1

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

edited May 20 '21 at 14:06

answered May 20 '21 at 13:53

Jim Jones

18,404
3
35
44

I tried your select, and it says "12NBSP34" or just "12 34" with a space in between. If you mean numeric locale, then I use Slovak_Slovakia.1250 – Marvin158 May 20 '21 at 14:20
1

I see, this one I couldn't predict :D But as I can see, the post from JGH already answers your question. Cheers :) – Jim Jones May 20 '21 at 14:29

score 1 · Accepted Answer · answered May 20 '21 at 14:16

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

Postgresql decimal point using to_char

2 Answers2