LPAD() Function in PostgreSQL

In PostgreSQL, LPAD()is a function that enables us to add padding to the left part of a string.

Syntax

The syntax goes like this:

lpad ( string text, length integer [, fill text ] ) 

Where:

  • string text is the string to apply padding to
  • length integer is the total length that you want the string to be after padding has been applied
  • fill text is an optional argument that allows you to specify one or more characters to use as the padding (the default is a space).

Example

Here’s an example to demonstrate:

SELECT LPAD('7', 3, '0');

Result:

007

In this case, I padded the string with zeros.

Padding with Spaces

As mentioned, the last argument is optional, and the default padding character is a space.

Here’s an example of padding a string with spaces:

SELECT LPAD('Cat', 10);

Result:

        Cat

We can see that the string has been padded with spaces to its left.

This is the equivalent of doing the following:

SELECT LPAD('Cat', 10, ' ');

Result:

        Cat

Padding with Multiple Characters

The third argument can contain more than one character:

SELECT LPAD('Cat', 10, 'Dog');

Result:

DogDogDCat