Repeat Function in Postgres SQL
In this tutorial to use the Repeat function to achieve various text- and integer- related tasks in Postgres SQL, including:
- What? What’s the syntax of the
REPEATfunction and what does the function do? How do we use the Repeat() function in our PostgreSQL queries? What parameters does it use?
- Why? When do we need this function in our queries?
- Extras We’ll get exposure to various SQL commands including
WHERE. We’ll work some with the
ARRAYvariable type, the
RAISE NOTICE, and how to cast an integer as text.
- Knowledge of the use of basic SQL statements, including
- Understanding of what the following terms mean: text types, strings, and integers.
- Optional: Tutorial on naming conventions that explains why we prefix our variables, column names, table names, etc. (all objects, really) as you see us doing here. For example, naming “tphrase_original” with the “t” you see at the beginning in order to delineate it as a “text” object.
What does the REPEAT function do in Postgres SQL?
We use PostgreSQL’s
REPEAT function to duplicate a string a specific number of times. As we’ll see in an example below, sometimes we want to pad text or numbers with zeros in order to obtain visual or other kinds of uniformity.
How to use the Repeat function with SQL and parameters
Syntax of the REPEAT() function
We give the REPEAT function two parameters:
t_what_to_repeat: The string we feed to the function.
i_repetitions: Tells REPEAT() how many characters we want the resulting string to be in length.
Let’s use the syntax above and take a step in the direction of more complexity and more understanding. The mini-project is to take four words and “pad” all of them on their left so that they are all the same length, appearing as if they are right justified.
i_word_length INTEGER := 0;
i_result_length INTEGER := 10;
t_what_to_repeat TEXT := " ";
i_current_array_spot INTEGER := 0;
i_size_of_array INTEGER := ARRAY_LENGTH(a_t_technologies,1); -- this calculates to be 4
-- loop through our four phrases
WHILE i_current_array_spot < i_size_of_array
i_current_array_spot := i_current_array_spot + 1;
t_word := a_t_technologies[i_current_array_spot]
i_word_length := LENGTH(t_word);
t_result := REPEAT(t_what_to_repeat, i_word_length-i_result_length) || t_word;
RAISE NOTICE t_result;
Analysis of the above code
WHILE: Here we are setting up our loop to cycle from 0 to 3. You will see below how we add 1 to the value just before accessing which word in the array we want to modify.
LOOP: On this line we increase “i_current_array_spot” by 1, so that the actual array item being pulled is one higher than 0 to 3; becoming 1 to 4.
RAISE NOTICE: Here we return “t_result” to see the following output.
See now how each word was “padded” to a maximum of 10 characters, pushing every word to have a uniform right margin?
NOTE: The LPAD() function is another way to accomplish this same task; usually with more efficiency.
Let’s explore another potential usage of the REPEAT() function in PostgreSQL. The scenario: A data table with integers ranging in the number of decimal places and we need to pad these numbers with zeros so they have a uniform appearance.
Let’s begin with the following Postgres table named “technologies”.
, i_rating::text AS t_word
, LENGTH(t_word) AS i_word_length
, REPEAT("0", i_word_length-i_result_length) || t_word AS t_rating_padded
SELECT t_name_tech: For reference, we are displaying columns named “t_name_tech” and “t_category_tech”.
i_rating::text AS t_word: Here we are recasting “i_rating”, an integer, to be a string (text object) called “t_word” so we can later use the LENGTH and REPEAT functions on it.
LENGTH(t_word) AS i_word_length: We need to know the current length of our word, as you will see on the next line. Note: We could calculate that on the fly, within the formula below, but we’re doing it explicitly here to increase readability and ease of understanding.
REPEAT(t_what_to_repeat, i_result_length - i_word_length) || t_word AS t_rating_padded: Seed the REPEAT() function with two parameters: “twhat_to_repeat”, which is a zero, and i_word_length – i_result_length, which tells Postgres, “Take the length of our word and tell us how close to 10 characters it is. If our word is 8 characters long, for example, we know 2 zeros need to be added in order to get to 10. _Then we use the concatenation symbol of “||” to add our “t_word” to the right side of those zeros and name the whole resulting string
AS“t_rating_padded”. You might ask, “Why not recast “t_rating_padded” back to an integer?” That’s because we would lose the zeros we just added if we went that route.
FROM: tells the Postgres database engine which table to use as the source for our SQL data.
The following results are returned when we execute the SQL:
In this tutorial we learned how to use the
REPEAT() function in our Postgres SQL, including working through two detailed real world examples. We also practiced the use of various PostgreSQL query commands including
WHERE. We worked with an
ARRAY type, the
ARRAY_LENGTH function, the
WHILE loop construct, the
LENGTH function, the
RAISE NOTICE command, and how to “cast” an integer into a text string using the “::” symbol.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started