patternsqlModerate

Why is to_char left padding with spaces?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

leftwhypaddingwithspacesto_char

Problem

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

SELECT '>' || to_char(1, '099') || ' 001<
(1 row)

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

You can see that in a simpler test case here

SELECT '>' || to_char(1, '0') || ' 1<
(1 row)

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

SELECT '>' || to_char(-1, '0') || '-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

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

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

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

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

StackExchange Database Administrators Q#175811, answer score: 17

No revisions yet.