Generate a series of monotonically increasing and decreasing integer values

Question

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?

Answer 1

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

Produces your desired result.

Answer 2

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?