How to Use RPAD() Function in PostgreSQL

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

This post demonstrates a complete overview of the Postgres RPAD() function using practical examples.

How to Use RPAD() Function in Postgres?

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

RPAD(main_string, length, fill_string);

Here, the “main_string” argument represents a string that will be filled by the “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.

How Does the RPAD() Function Work in Postgres?

When we use RPAD(), 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 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

The below snippet demonstrates how the RPAD() function work in Postgres:

SELECT RPAD('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:

img

The output demonstrates that the substring has filled the remaining length of the main 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 RPAD('Hello World', 15, '-1');

In this code, “-1” is a substring of length 2 that will be repeated, as follows:

img

The output indicates 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 RPAD('Hello World', 15, '-12345');

In this code, the substring “12345-” is greater than the main string’s remaining length. As a result, the substring will be trimmed from the left side, as follows:

img

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

Conclusion

RPAD() or “right 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 right side. The RPAD() 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 write-up explained the usage of the RPAD() function in Postgres using practical examples.