If you’re trying to use either lpad() or rpad() to pad a string in PostgreSQL but it’s just not working, maybe it’s because of the reasons listed below.

Fortunately this issue has a simple fix, and it can be resolved within seconds.

Example of Issue

Here’s an example of code that results in this perceived issue:

SELECT lpad('Rich', 4, '-');

Result:

Rich

Here I tried to left-pad a string with a hyphen (-), but nothing happened. That’s because I specified a length of 4, which means that the final string will be four characters long. Given the original string is four characters long, there’s no room for any padding, which results in no padding being added.

Here’s another example:

SELECT lpad('Rich ', 5, '-');

Result:

Rich 

This time I increased the second argument to 5 but again, nothing happened. In this case it was due to the string having a space character on the right side of the string. It actually has five characters already (including the space), and so, again there was no room for the hyphen.

Solution

In both of the examples above, the issue arises because the original string is already the length specified in the lpad() function.

Just to be clear, the second argument specifies how long the resulting string will be after the padding has been added. It doesn’t specify how long the padding alone will be. Therefore, this value needs to cater for the desired padding length as well as the original string’s length.

Here’s an example of how to fix the first example:

SELECT lpad('Rich', 5, '-');

Result:

-Rich 

Perhaps an even better solution is to trim the string first. That way we can remove any leading and/or trailing spaces from the string before we apply the padding. Doing this will automatically address our second example too:

SELECT lpad(trim('Rich '), 5, '-');

Result:

-Rich