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.

 employee_id | first_name  |  last_name  |  email   |    phone_number    | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id
-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
         100 | Steven      | King        | SKING    | 515.123.4567       | 1987-06-17 | AD_PRES    | 24000.00 |           0.00 |          0 |            90
         101 | Neena       | Kochhar     | NKOCHHAR | 515.123.4568       | 1987-06-18 | AD_VP      | 17000.00 |           0.00 |        100 |            90
         102 | Lex         | De Haan     | LDEHAAN  | 515.123.4569       | 1987-06-19 | AD_VP      | 17000.00 |           0.00 |        100 |            90
         103 | Alexander   | Hunold      | AHUNOLD  | 590.423.4567       | 1987-06-20 | IT_PROG    |  9000.00 |           0.00 |        102 |            60
         104 | Bruce       | Ernst       | BERNST   | 590.423.4568       | 1987-06-21 | IT_PROG    |  6000.00 |           0.00 |        103 |            60
         105 | David       | Austin      | DAUSTIN  | 590.423.4569       | 1987-06-22 | IT_PROG    |  4800.00 |           0.00 |        103 |            60
         106 | Valli       | Pataballa   | VPATABAL | 590.423.4560       | 1987-06-23 | IT_PROG    |  4800.00 |           0.00 |        103 |            60
         107 | Diana       | Lorentz     | DLORENTZ | 590.423.5567       | 1987-06-24 | IT_PROG    |  4200.00 |           0.00 |        103 |            60
         108 | Nancy       | Greenberg   | NGREENBE | 515.124.4569       | 1987-06-25 | FI_MGR     | 12000.00 |           0.00 |        101 |           100
         109 | Daniel      | Faviet      | DFAVIET  | 515.124.4169       | 1987-06-26 | FI_ACCOUNT |  9000.00 |           0.00 |        108 |           100
		 ..................
         206 | William     | Gietz       | WGIETZ   | 515.123.8181       | 1987-10-01 | AC_ACCOUNT |  8300.00 |           0.00 |        205 |           110

View the table

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)

PREV : QUOTE_IDENT function
NEXT : REPLACE function