4

When ever I use 099 indicating 3 digits 0-padded, I'm getting spaces on the left side.

SELECT '>' || to_char(1, '099') || '<';
 ?column? 
----------
 > 001<
(1 row)

Why is to_char left padding here? Why are there leading spaces?

7

You can see that in a simpler test case here

SELECT '>' || to_char(1, '0') || '<';
 ?column? 
----------
 > 1<
(1 row)

This is because, as @Abelisto said, the space is reserved for the sign glyph,

SELECT '>' || to_char(-1, '0') || '<';
 ?column? 
----------
 >-1<
(1 row)

You can suppress the sign using FM, from the docs

Modifier Description Example
FM       prefix      fill mode (suppress leading zeroes and padding blanks) FM9999

So what you want is

SELECT '>' || to_char(1, 'FM099') || '<';

The zeros aren't suppressed when you demand them with FM0

  • 2
    In addition: it is place for sign, for example SELECT '>' || to_char(-1, '0') || '<'; does not output space. – Abelisto Jun 8 '17 at 20:27
  • @Abelisto updated with that note – Evan Carroll Jun 8 '17 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.