1

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?

2 Answers 2

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.

1
  • Thanks for the quick and accurate reply :-)..... Feb 27 at 17:12
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?

2
  • 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 :-).... Feb 28 at 17:11
  • 2
    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. :) Feb 28 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.