How can I generate a series of repeating numbers in PostgreSQL?

sql postgresql generate-series
oshongo picture oshongo · Apr 29, 2014 · Viewed 27.7k times · Source

In PostgreSQL, is it possible to generate a series of repeating numbers? For example, I want to generate the numbers 1 to 10, with each number repeated 3 times:

1
1
1
2
2
2
3
3
3
.. and so on.

Answer

Bohemian picture Bohemian · Apr 29, 2014

You could cross join it to a series of 3:

SELECT a.n
from generate_series(1, 100) as a(n), generate_series(1, 3)

Related questions

How to perform a select query in a DO block?

I want to port the below SQL code from MS SQL-Server to PostgreSQL. DECLARE @iStartYear integer DECLARE @iStartMonth integer DECLARE @iEndYear integer DECLARE @iEndMonth integer SET @iStartYear = 2012 SET @iStartMonth = 4 SET @iEndYear = 2016 SET @iEndMonth = 1 ;WITH CTE AS ( SELECT --@iStartYear AS …

Read More
Postgresql generate_series of months

I'm trying to generate a series in PostgreSQL with the generate_series function. I need a series of months starting from Jan 2008 until current month + 12 (a year out). I'm using and restricted to PostgreSQL 8.3.14 (so I don't have the timestamp …

Read More
Insert multiple rows in one table based on number in another table

I am creating a database for the first time using Postgres 9.3 on MacOSX. Let's say I have table A and B. A starts off as empty and B as filled. I would like the number of entries in column all_…

Read More