PostgreSQL RPAD Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL RPAD function to pad a string with specified characters to a certain length.

Introduction to the PostgreSQL RPAD function #

The RPAD() function pads a string on the right with specified characters to a defined length.

Here’s the syntax of the RPAD() function:

RPAD (string, target_length [, fill])Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• string: The input string you want to pad.
• target_length: The length of the result string after padding.
• fill: is a string for padding. It defaults to a space.

The RPAD() function returns a new string with the length is the target length and is padded with the fill characters on the right.

If the length of the input string is less than the target_length, the RPAD() function truncates the input string to the target_length.

If the length of the input string is equal to the target_length, the RPAD() function doesn’t pad the string and returns the original string.

Basic PostgreSQL RPAD function examples #

The following statement uses the RPAD() function to pad the character (#) on the right of a string:

SELECT RPAD('A',5,'#') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 A####Code language: plaintext (plaintext)

The following statement uses the RPAD() function to truncate string 'PostgreSQL Tutorial' to a string with the length of 10:

SELECT
  RPAD('PostgreSQL Tutorial', 10);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    rpad
------------
 PostgreSQLCode language: plaintext (plaintext)

The following example doesn’t pad string because the length of the input string is the same as the target length:

SELECT
  RPAD('PostgreSQL', 10);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    rpad
------------
 PostgreSQLCode language: plaintext (plaintext)

Using PostgreSQL RPAD function with table data #

We’ll use the products table from the inventory database:

The following query retrieves product name from the products table and pads them with the character (.) on the right:

SELECT
  RPAD(product_name, 35, '.') product_name
FROM
  products
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            product_name
-------------------------------------
 Apple AirPods Pro 3................
 Apple iMac 24".....................
 Apple iPad Pro 12.9................
 Apple iPhone 15 Pro Max............
 Apple iPhone 15....................Code language: plaintext (plaintext)

Summary #

• Use the RPAD() function to pad specified characters on the right of a string to a certain length.