A Quick Look at the REPEAT() Function in DuckDB

DuckDB has a repeat() function that enables us to output a repeating value easily and concisely. The way it works is that we pass the value to the function, followed by how many times we want it to be repeated. It returns that value repeated the specified number of times.

Syntax

The basic syntax goes like this:

repeat(value, count)

Where value is the value to repeat and count is the number of times we want to repeat it.

A more detailed syntax goes like this:

repeat(VARCHAR, BIGINT) -> VARCHAR
repeat(BLOB, BIGINT) -> BLOB
repeat(ANY[], BIGINT) -> ANY[]

So we can pass strings, blobs, or lists.

Example

Here’s a simple example that repeats a string value:

SELECT repeat('Run', 5);

Output:

RunRunRunRunRun

We repeated the string Run five times.

Repeating List Elements

We can also use repeat() to repeat list elements:

SELECT repeat(['Run','Now'], 5);

Output:

[Run, Now, Run, Now, Run, Now, Run, Now, Run, Now]

So in this case we pass the list, and it will produce a list with all elements in that list repeated for the specified number of times.

Numbers

If the first argument is a number, an error is returned:

SELECT repeat(1, 5);

Output:

Binder Error:
No function matches the given name and argument types 'repeat(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	repeat(VARCHAR, BIGINT) -> VARCHAR
	repeat(BLOB, BIGINT) -> BLOB
	repeat(ANY[], BIGINT) -> ANY[]


LINE 1: SELECT repeat(1, 5);
               ^

But we can pass numbers in a list:

SELECT repeat([1], 5);

Output:

[1, 1, 1, 1, 1]

This of course, returns the result in a list.

Nested Lists

We can repeat nested lists too:

SELECT repeat([['Cat','Dog'], ['Ant', 'Bird']], 2);

Output:

[[Cat, Dog], [Ant, Bird], [Cat, Dog], [Ant, Bird]]

We can get different results when using functions such as unnest():

SELECT repeat(
    unnest([
        ['Cat','Dog'], 
        ['Ant', 'Bird']
        ]), 
    2
    ) AS unnested;

Output:

+------------------------+
|        unnested        |
+------------------------+
| [Cat, Dog, Cat, Dog]   |
| [Ant, Bird, Ant, Bird] |
+------------------------+

And the output will also depend on where each function is placed:

SELECT unnest(
    repeat([
        ['Cat','Dog'], 
        ['Ant', 'Bird']
        ], 2)
    ) AS unnested;

Output:

+-------------+
|  unnested   |
+-------------+
| [Cat, Dog]  |
| [Ant, Bird] |
| [Cat, Dog]  |
| [Ant, Bird] |
+-------------+

We can also use the flatten() function to unflatten the resulting list:

SELECT flatten(
    repeat([
        ['Cat','Dog'], 
        ['Ant', 'Bird']
        ], 2)
    ) AS flattened;

Output:

+--------------------------------------------+
|                 flattened                  |
+--------------------------------------------+
| [Cat, Dog, Ant, Bird, Cat, Dog, Ant, Bird] |
+--------------------------------------------+