Often in PostgreSQL you may want to generate a series of repeating numbers.
You can use the generate_series() function with the following syntax to do so:
SELECT generate_series(3, (3*5)+(3-1)) / 3 AS my_series;
This particular example generates a series of values from 1 to 5 in which each number is repeated 3 times.
Here is how this syntax works:
generate_series(n, (n*k)+(n-1) / n
where:
- n: Number of times to repeat each number
- k: Max number in series
The following example shows how to use this syntax in practice.
Example: Generate a Series of Repeating Numbers in PostgreSQL
Suppose that we would like to generate a series of values from 1 to 5 in which each number is repeated 3 times.
We can use the following syntax to do so:
SELECT generate_series(3, (3*5)+(3-1)) / 3 AS my_series;
my_series
-----------
1
1
1
2
2
2
3
3
3
4
4
4
5
5
5
Notice that the resulting series contains values from 1 to 5 in which each number is repeated 3 times.
We can change the numbers for n and k in the generate_series() function to any numbers that we would like to create a different series of repeating numbers.
For example, we can use the following syntax to instead create a series of values from 1 to 4 in which each number is repeated 2 times:
SELECT generate_series(2, (2*4)+(2-1)) / 2 AS my_series;
my_series
-----------
1
1
2
2
3
3
4
4
The resulting series contains values from 1 to 4 in which each number is repeated 2 times, just as we specified.
Feel free to use similar syntax to create your own custom series of repeating numbers in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: How to Round Numbers to Nearest 10
PostgreSQL: Count Occurrences of Each Value in Column
