CODEWITHSUNDEEP
sqlpostgresqlgenerate-series
oshongo
oshongo

Reputation: 443

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

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.

Upvotes: 39

Views: 44384

Answers (8)

Shiva
Shiva

Reputation: 12592

In my case, I was looking for a table with a column with data like following to inner join with other tables with a similar column.

with
  hours(hour) AS (
    select concat(extract(hour from n), ' of ', n::date) as hour
    from
    generate_series(timestamp '2004-03-07', '2004-08-16', '1 hour')
    as a(n)
  )
  
  select * from hours

Result

Link to the fiddle https://www.db-fiddle.com/f/7VagpVQwio9e6DVjSwMsTL/1 enter image description here

Actual Implementation

enter image description here

I hope this is helpful.

Upvotes: 0

frlan
frlan

Reputation: 7270

You could try integer division like this: 

SELECT generate_series(3, 100) / 3

Upvotes: 21

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

For such small numbers CROSS JOIN two VALUES expressions:

SELECT n
FROM  (VALUES (1),(2),(3)) x(r)  -- repetitions (values are not used)
     ,(VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) y(n); -- numbers

SQL Fiddle.

This works for any sequence of numbers (including repeated or irregular patterns).
For anything bigger and with regular sequential pattern use generate_series() as has been suggested.

Upvotes: 8

Bohemian
Bohemian

Reputation: 425448

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)

Upvotes: 55

Tomas Greif
Tomas Greif

Reputation: 22671

Just another options:

select generate_series(1, 3) from generate_series(1, 10)

select generate_series(1, 30) % 10 + 1

Upvotes: 2

dnoeth
dnoeth

Reputation: 60513

I don't know if you can do use generate_series like that in PostgreSQL, but i would try a cross join:

SELECT x FROM
  (SELECT generate_series(1, 10) AS x) t1, 
  (SELECT generate_series(1, 3) as y) t2

Edit:

As generate_series already returns a table there's no need for SELECT in a Derived Table:

SELECT x FROM
  generate_series(1, 10) AS x, 
  generate_series(1, 3) as y

Upvotes: 4

I A Khan
I A Khan

Reputation: 8879

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

Upvotes: 2

Armon
Armon

Reputation: 218

SELECT * FROM (SELECT generate_series(1, 10)) A
JOIN (
  SELECT generate_series(1, 3)
) B ON (TRUE)

http://www.sqlfiddle.com/#!12/d41d8

Upvotes: 4

Related Questions