PostgreSQL offers a variety of built-in functions to handle the TEXT data, such as SUBSTRING(), RPAD(), TRIM(), etc. One such function is LPAD() or “left padding” which fills a string of a specific length with a substring. It fills/pads the given string from the left side.

This post presents a comprehensive overview of the Postgres LPAD() function using suitable examples.

How to Use LPAD() Function in Postgres?

The LPAD() function accepts three arguments: “main_string”, “length”, and “fill_string”:

LPAD(main_string, length, fill_string);

- The “main_string” argument represents a string that will be filled by the “fill_string”.
- The length represents the string’s total length.
- The “fill_string” argument represents a substring that will fill up the main string.

How Does the LPAD() Function Work in Postgres?

When we use LPAD(), we have three options:

• The substring’s length is equal to the main string’s remaining length. In such a case, the substring fills up the remaining length of the main string completely/appropriately.
• The substring’s length is less than the main string’s remaining length. In such a case, the substring will be repeated until-unless it fills up the remaining length of the string completely.
• The substring’s length is greater than the main string’s remaining length. In such a case, the substring will be trimmed from the right side.

Example 1: Substring With Appropriate Length

Let’s learn how the LPAD() function work in Postgres using the following example:

SELECT LPAD('Hello World', 15, '123-');

In this code, the “Hello World” is a main string of length 10, “15” represents the total string length, and “123-” represents a substring of length 4. Therefore the substring “123-” will pad the main string “Hello World” appropriately:

The output shows that the substring fills the remaining length of the string appropriately.

Example 2: Substring With Less Length

In the following example, the substring’s length is less than the remaining length of the main string:

SELECT LPAD('Hello World', 15, '1-');

In this code, the “Hello World” is a main string of length 10, “15” represents the total string length, and “1-” represents a substring of length 2. Here, the substring “1-” is less than the main string’s remaining length. As a result, the substring will be repeated, as follows:

The output shows that the substring repeats until the remaining length of the string fills completely.

Example 3: Substring With More Length

In the following code, the substring’s length is greater than the remaining length of the main string:

SELECT LPAD('Hello World', 15, '12345-');

In this code, the main string is of length 10, the total length is “15”, and the substring is of length 6. Here, the substring “12345-” is greater than the main string’s remaining length. As a result, the substring will be trimmed from the right side, as follows:

The output shows that the extra characters from the substring have been trimmed from the right side.

Conclusion

LPAD() or “left padding” is a built-in function in Postgres which fills a string of a specific length with a substring. It fills/pads the given string from the left side. The LPAD() function accepts three arguments: “main_string”, “length”, and “fill_string”. The “main_string” argument represents a string that will be filled by a substring “fill_string”. The length represents the string’s total length. And the “fill_string” argument represents a substring that will fill up the main string. This post explained how to use the LPAD() function in PostgreSQL using suitable examples.