# Generate a series of monotonically increasing and decreasing integer values

I need to generate monotonically increasing and decreasing integer numbers of a given interval, for example (10) in one column of a table.

The desired output from top to bottom should look like:

``````1,2,3,4,5,6,7,8,9,10,10,9,8,7,6,5,4,3,2,1,1,2,3,4,5,6,7,8,9,10,...
``````

Maybe using the function `generate_series(1,100)` or similar?

``````SELECT n
FROM   generate_series(1, 10) n  -- number range
, generate_series(1, 10) x  -- how man times?
ORDER  BY x, ((x%2 * 2) - 1) * n;
``````

fiddle

• Thanks for the quick and accurate reply :-)..... Commented Feb 27 at 17:12

Several approaches to answering this (interesting) question were discussed here. You might be interested in exploring these answers in order to gain a better understanding of the topic (and PostgreSQL in general).

There was also discussion of using a `param` table (as a CTE) for the start and stop numbers in your series (i.e. (3, 5) will give 3, 4, 5, 5, 4, 3,...).

The best approach then was given as (fiddle):

``````SELECT UNNEST(arr)
FROM
(
SELECT arr, GENERATE_SERIES(1, 3) AS gs  -- no. of repeats
FROM
(
SELECT
ARRAY
(
SELECT x
FROM GENERATE_SERIES(1, 10) x  -- no. to count to
UNION ALL
SELECT x
FROM GENERATE_SERIES(10, 1, -1) x
) AS arr
) AS t
) AS s;
``````

A bit round-about compared to @ErwinBrandstetter's solution, but might be worth a look, if only for the learning experience?

• Thank you for your reply and for pointing me to a similar question and answers previously published on this wonderful site. You are well aware that the evaluation of an answer usually depends on the level of knowledge of the asker, and is subjective, but this is not the main thing for the development of science :-).... Commented Feb 28 at 17:11
• Ah, there it is! I vaguely remembered a similar question in the past. Writing a new solution was faster for me than searching for the old one. :) Commented Feb 28 at 19:49