Reputation: 443
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
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
Link to the fiddle https://www.db-fiddle.com/f/7VagpVQwio9e6DVjSwMsTL/1
I hope this is helpful.
Upvotes: 0
Reputation: 7270
You could try integer division like this:
SELECT generate_series(3, 100) / 3
Upvotes: 21
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
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
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
Reputation: 22671
Just another options:
select generate_series(1, 3) from generate_series(1, 10)
select generate_series(1, 30) % 10 + 1
Upvotes: 2
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
Reputation: 8879
SELECT a.x from generate_series(0, 100) as a(x), generate_series(1, 3)
Upvotes: 2
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