How can I generate a series of repeating numbers in PostgreSQL?
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.
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)
SELECT * FROM (SELECT generate_series(1, 10)) A
JOIN (
SELECT generate_series(1, 3)
) B ON (TRUE)
http://www.sqlfiddle.com/#!12/d41d8
You could try integer division like this:
SELECT generate_series(3, 100) / 3