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