PostgreSQL provides us with a couple of functions that allow us to pad strings. We can use these functions to put one or more space characters or other characters on either the right side, left side, or both sides of the string.
Pad the Left Side
We can use the lpad() function to pad the left side of a string with one or more spaces or other characters.
Here’s an example:
SELECT lpad('Stonk', 10);Result:
lpad
------------
Stonk
Here I padded the string with space characters on the left for a length of ten. This means that the resulting string (consisting of the original string plus any padding that we added) is ten characters long. We know it was a space because that’s the default character – if we don’t provide a third argument to specify the character then it defaults at a space.
We can provide one or more characters as a third argument if needed:
SELECT lpad('Stonk', 10, '=+');Result:
lpad
------------
=+=+=Stonk
Again, the final string is ten characters long.
Pad the Right Side
We can use the rpad() function to pad the right side of a string with one or more spaces or other characters.
Here’s an example:
SELECT rpad('Stonk', 10, '=+');Result:
rpad
------------
Stonk=+=+=
Similar to the lpad() function, except that it pads the right side instead.
Pad Both Sides
We can use rpad() and lpad() together to pad both sides:
SELECT rpad(lpad('Stonk', 10, '=+'), 15, '=+');Result:
rpad
-----------------
=+=+=Stonk=+=+=
Note that I needed to increase the length in the outer function (in this case the rpad() function) so that the resulting string is longer than the one produced by the inner function (the lpad() function). If I didn’t do that, we wouldn’t see any padding on the right.
Here’s an example of what I mean:
SELECT rpad(lpad('Stonk', 10, '=+'), 10, '=+');Result:
rpad
------------
=+=+=Stonk
This is because the lpad() function already made the string ten characters long, and so there was no more room to pad the string when we specified ten characters for the rpad() function.