PostgreSQL REPEAT Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL REPEAT function to repeat a string a specified number of times.

What is the PostgreSQL REPEAT Function #

In PostgreSQL, the REPEAT function creates a new string by repeating a given string a specified number of times.

The REPEAT function can be helpful when you want to generate repeated patterns or padding in your text.

Here’s the basic syntax of the REPEAT function:

REPEAT(string, number)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The function takes two parameters:

• string: The string you want to repeat. It can be a literal string or a column.
• number: The number of times you want to repeat the string. It must be a non-negative integer.

The REPEAT() function returns a new string that consists of the input string repeated the specified number of times.

Basic REPEAT function example #

The following example uses the REPEAT() function to repeat the string "*" three times:

SELECT REPEAT('*', 3) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 ***
(1 row)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Masking emails #

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

SELECT
  email,
  REPEAT('*', LENGTH(email)) AS masked_name
FROM
  users;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            email             |         masked_name
------------------------------+------------------------------
 [email protected]      | ***********************
 [email protected]    | *************************
 [email protected]   | **************************
 [email protected]     | ************************
 [email protected] | ****************************
(5 rows)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Handling Edge Cases #

Case 1: Zero Repetitions

The REPEAT() function returns an empty string if the number parameter is 0:

SELECT
  REPEAT('abc', 0) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Case 2: Negative arguments

The funtion will also return an empty string if the number parameter is negative:

SELECT
  REPEAT('abc', -1) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Case 3: NULL arguments

The REPEAT function returns NULL if either the string or number parameter is NULL:

SELECT
  REPEAT(NULL, 3) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Performance Considerations #

If you are using the REPEAT function in a WHERE clause, ensure that you have a function-based index that uses the REPEAT function, or you’ll encounter slower queries.

Summary #

• Use PostgreSQL REPEAT function to generate repeated strings.