covert number strings with leading zeros to numeric value with leading zero in postgres 12

Question

i am new to PostgreSQL.i want to convert string '0480' to number 0480 .i am using casting operator to do this,

db=# select '0480':: numeric;

 numeric 
---------
     480
(1 row)

but here I am expecting 0480 number. Then i tried to_number function in PostgreSQL,

db=# select to_number('0480','9999');

 to_number 
-----------
       480
(1 row)

so how is this possible? how can I convert number strings with leading zeros to numeric value with leading zero.

also some outputs of to_char function, little bit confusing?

db=# select to_char(0480,'9999');

 to_char 
---------
   480
(1 row)

db=# select to_char(0480,'09999');

 to_char 
---------
  00480
(1 row)

Using PostgreSQL 12.3 on Debian

Answer 1

Numbers don't have leading zeros. 0480 is exactly the same number as 480 - just like 00000001 is exactly the same number as 1

The only way to get leading zeros, is to convert the number to a string and add the desired number of leading zeros.

some outputs of to_char function, little bit confusing?

Not, not at all.

to_char(0480,'9999') converts the number 480 to a 4 character string. The 9 indicates that if there is no value for that digit, it can be "dropped".

Quote from the manual

9 - digit position (can be dropped if insignificant)
0 - digit position (will not be dropped, even if insignificant)

Leading zeros are always insignificant (from a mathematical perspective).

to_char(480,'0000') will do you what you expect: a four digit string with a leading zero.
Similarly to_char(1,'0000') will return 0001.

Note that by default, all output of to_char() is right-aligned with leading spaces. If you don't want that, you should use the FM modifier: e.g. to_char(1,'FM0000')