Overview
The REPEAT function has been a part of PostgreSQL for many years, although the exact version of its introduction is less documented. However, PostgreSQL has supported standard string functions, including REPEAT, since at least version 8.1, and has continued to do so over the iterations of the database system.
What Is Its Purpose?
The REPEAT function is used in PostgreSQL to repeat a string a specified number of times. This can be quite useful for generating patterns, extending strings to a certain length, or for testing and formatting purposes. The ability to programmatically repeat characters or strings is common in many programming languages, and in SQL, this capability forms an integral part of dynamic query generation and text manipulation.
Syntax, Parameters, and Return Value
The syntax for the REPEAT function in PostgreSQL is straightforward:
REPEAT(string text, number int)Where string is the string to be repeated, and number is the number of times the string should be repeated. The function returns a text string comprised of the input string repeated the specified number of times.
Code Examples
Example 1: Basic String Repetition
A common use case for the REPEAT function is creating a line of characters, for example, to use as a separator in the command-line display.
Repeating a single character to form a line separator:
-- Repeat the dash character 20 times
SELECT REPEAT('-', 20) AS separator_line;
Example 2: Data Masking
Partial data masking can be implemented by repeating a masking character, like an asterisk ‘*’, to obscure part of a value such as an email address.
Using REPEAT to mask part of a string, commonly used for hiding sensitive data:
-- Mask the local part of an email address
SELECT username || REPEAT('*', LENGTH(email) - POSITION('@' IN email)) || substr(email, POSITION('@' IN email)) AS masked_email
FROM (
SELECT '[email protected]' AS email,
'john.doe' AS username
) AS demo_user;
Example 3: Text Drawing
The REPEAT function can also be fun, for instance, by repeating characters to draw a simple shape like a square or triangle in text.
Generating a simple text-based shape:
-- Draw a text-based triangle
SELECT REPEAT('*', level) AS triangle
FROM generate_series(1, 5) AS level;
Example 4: Space Padding
Padding strings to a specific length often is necessary for aligning textual output in reports or console applications.
Using REPEAT to pad a string with spaces to align output text:
-- Pad the string 'PostgreSQL' to right align in a field of 20 characters
SELECT REPEAT(' ', 20 - LENGTH('PostgreSQL')) || 'PostgreSQL' AS padded_string;
Conclusion
The REPEAT function is a small but mighty tool in PostgreSQL’s set of string manipulation functions. It provides an easy way to duplicate strings and can be used in a variety of creative ways, such as formatting output, data masking, and even for generating simple text drawings. Understanding how to use the REPEAT function can enhance your ability to manipulate and work with text in PostgreSQL, and bring you more comfort in performing complex queries.
