to_char(number) function in postgres

Question

i want to display/convert a number to character (of it's same length) using to_char() function .

In oracle i can write like

SELECT to_char(1234) FROM DUAL

But in postgres SELECT to_char(1234) is not working.

score 54 · Answer 1 · 2013-01-04T11:43:54.803

54

You need to supply a format mask. In PostgreSQL there is no default:

select to_char(1234, 'FM9999');

If you don't know how many digits there are, just estimate the maximum:

select to_char(1234, 'FM999999999999999999');

If the number has less digits, this won't have any side effects.

If you don't need any formatting (like decimal point, thousands separator) you can also simply cast the value to text:

select 1234::text

edited Jan 04 '13 at 11:43

answered Jan 04 '13 at 10:53

How can i find out second argument(value with the specified number of digits) for different values(of different length). – jobi88 Jan 04 '13 at 11:01
@jobi88: it's all documented in the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html – Jan 04 '13 at 11:02
1

I couldn't find out exact thing for me. Is there any way to get, how many 9s i need to add for to get full number. For eg in oracle i can write like. SELECT to_char(ac_no) from account. (here ac_no values may have different lengths). In this case how can i find out second argument?? – jobi88 Jan 04 '13 at 11:15

score 8 · Answer 2 · edited Jan 04 '13 at 10:54

8

you have to specify a numeric format, ie:

to_char(1234, '9999')

edited Jan 04 '13 at 10:54

answered Jan 04 '13 at 10:53

Lorenzo L

score 2 · Answer 3 · answered Jan 25 '19 at 00:55

2

CAST function worked for me.

SELECT CAST(integerv AS text) AS textv
FROM (
       SELECT 1234 AS integerv
     ) x

OR

SELECT integerv::text AS textv
FROM (
       SELECT 1234 AS integerv
     ) x

answered Jan 25 '19 at 00:55

changed

score -1 · Answer 4 · answered Sep 21 '17 at 19:53

-1

You can use:

1234||''

It works on most databases.

answered Sep 21 '17 at 19:53

Eduardo

I simply cannot understand why using concatenate to force an implicit type conversion is better than an explicit cast. – Merlin Feb 20 '19 at 20:24
2

@Merlin This is more compatible. So you can use the same SQL for multiple databases. – Eduardo Feb 22 '19 at 13:22

4 Answers4