How to Use REPEAT() Function in PostgreSQL

PostgreSQL provides numerous string functions to work with the strings data, such as the RIGHT() function, CONCAT() function, REPLACE() function, etc. The REPEAT() is an inbuilt function in postgres that repeats a string a specific number of times. A frequent use case of the REPEAT() function is repeating a special character in a string, such as *, -, #, etc. This function is useful for creating test data or generating repeated string patterns.

This postgres blog demonstrates how to use the REPEAT() function in Postgres using various practical examples.

How to Use the REPEAT() Function in Postgres?

The REPEAT() function in Postgres is a string function that retrieves a string consisting of the given string repeated an ‘n’ number of times:

REPEAT(input_string, number);

It takes two arguments: an “input_string” and the “number_of_times” to repeat the string. For instance, REPEAT('Postgres, 2) would return the string 'PostgresPostgres'.

Let’s implement it practically.

Example 1: How Does the REPEAT() Function Works in Postgres?

A string and a number are passed to the REPEAT() function; as a result, it will repeat the given string based on the specified number:

SELECT REPEAT('Welcome ', 5);
img

The output shows that the stated function repeats the input string “n” times.

Example 2: Repeat a Special Character

In the following example, a special character and an integer are passed to the REPEAT() function. Consequently, the REPEAT() function will repeat the given string based on the specified number:

SELECT REPEAT('# ', 5);
img

The REPEAT() function repeats the input symbol five times.

Example 3: REPEAT() Function With CONCAT() Function

In the following code, the REPEAT() function is used with the CONCAT() function to repeat a special character five times and concatenates it with a string:

SELECT CONCAT('Welcome ', REPEAT('=', 5),'> Geeks');
img

In the above snippet, the “=” is repeated five times and is concatenated with “Welcome” and “> Geeks”.

Example 4: How to Use the REPEAT() Function Table’s Columns?

We have already created a sample table named “staff_info”, whose data is enlisted in the following snippet:

SELECT * FROM staff_info;
img

Let’s use the REPEAT() function with the CONCAT() function to repeat a special symbol and concatenate it with multiple columns:

SELECT CONCAT(staff_name, REPEAT('=', 3), '> ', staff_designation)
FROM staff_info;
img

This is how the REPEAT() function works in Postgres.

Conclusion

Postgres offers an inbuilt string function named REPEAT() that repeats a string a specific times. The REPEAT() function in Postgres is a string function that retrieves a string consisting of the given string repeated an ‘n’ number of times. It takes two arguments: the “input_string” and the “number_of_times” to repeat the targeted string. The REPEAT() function is used with different string functions, such as the CONCAT() function, to maximize the functionality of the REPEAT() function. This blog post has presented an in-depth overview of how to use the REPEAT() function in Postgres.