PostgreSQL REPEAT() function

REPEAT() function

The PostgreSQL repeat function is used to repeat a specified string to a specified number of times.

This function is useful for generating repetitive patterns or formatting output with repeated characters.

Uses of REPEAT() Function

• Generate Repetitive Patterns: Create strings with repeated patterns for various formatting needs.


• Format Output: Add repeated characters or symbols for visual formatting in query results or reports.


• Dynamic String Construction: Build strings with repeated content dynamically based on input values or conditions.


• Create Test Data: Generate test data with repetitive patterns to simulate or test various scenarios.

Syntax:

repeat(<string>,<repeating_number>)

PostgreSQL Version: 9.3

Visual Presentation of PostgreSQL REPEAT() function

Example: PostgreSQL REPEAT() function:

In the example below, the specified string 'test__' and '*--*' have repeated 5 times each.

SQL Code:

SELECT repeat('test___', 5),repeat('*--*', 5);

Output:

             repeat             |        repeat
--------------------------------+----------------------
 test__test__test__test__test__ | *--**--**--**--**--*
(1 row)

Example of PostgreSQL REPEAT() function using column :

Sample Table: employees

The example below returns a format using repeat function. Here in the example below the first_name and last_name have concatenated and a string has been concatenated after repeating a specific time from employees table for department_id 100.

SQL Code:

SELECT concat(first_name,' ',last_name) "Name",
concat(repeat('-',3),'>>') " ",job_id "Designation"
FROM employees
WHERE department_id=100;

Output:

       Name        |       | Designation
-------------------+-------+-------------
 Nancy Greenberg   | --->> | FI_MGR
 Daniel Faviet     | --->> | FI_ACCOUNT
 John Chen         | --->> | FI_ACCOUNT
 Ismael Sciarra    | --->> | FI_ACCOUNT
 Jose Manuel Urman | --->> | FI_ACCOUNT
 Luis Popp         | --->> | FI_ACCOUNT
(6 rows)

Previous: QUOTE_IDENT function
Next: REPLACE function