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
– Laurenz Albe, Commented May 20, 2021 at 14:35

JGH · Accepted Answer · 2021-05-20 14:16:47Z

1

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

answered May 20, 2021 at 14:16

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jim Jones · Accepted Answer · 2021-05-20 14:06:31Z

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, 2021 at 14:06

answered May 20, 2021 at 13:53

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

2 Comments

Marvin158 Over a year ago

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

Jim Jones Over a year ago

I see, this one I couldn't predict :D But as I can see, the post from JGH already answers your question. Cheers :)

Collectives™ on Stack Overflow

Postgresql decimal point using to_char

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related