13

Given two numbers n and m, I want to generate a series of the form

1, 2, ..., (n-1), n, n, (n-1), ... 2, 1

and repeat it m times.

For instance, for n = 3 and m = 4, I want a sequence of the following 24 numbers:

1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------

I know how to achieve this result in PostgreSQL by either of two methods:

Using the following query, which uses the generate_series function, and a few tricks to guarantee that the order is the right one:

WITH parameters (n, m) AS
(
    VALUES (3, 5)
)
SELECT 
    xi
FROM
(
    SELECT
        i, i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
    UNION ALL
    SELECT
        i + parameters.n, parameters.n + 1 - i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
) AS s0 
CROSS JOIN 
    generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
    j, i ;

... or use a function for the same purpose, with adjoint and nested loops:

CREATE FUNCTION generate_up_down_series(
    _elements    /* n */ integer,
    _repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
    j INTEGER ;
    i INTEGER ;
begin
    for j in 1 .. _repetitions loop
        for i in         1 .. _elements loop
              return next i ;
        end loop ;
        for i in reverse _elements .. 1 loop
              return next i ;
        end loop ;
    end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;

How could I possibly do the equivalent in either standard SQL or in Transact-SQL / SQL Server?

0

11 Answers 11

6

In Postgres, it's easy using the generate_series() function:

WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p, 
    generate_series(1, p.n) AS gn (i),
    generate_series(1, 2)   AS g2 (i),
    generate_series(1, p.m) AS gm (i)
ORDER BY
    gm.i, g2.i, gn.i ;

In standard SQL - and assuming that there is a reasonable limit on the size of the parameters n, m, i.e. less than a million - you can use a Numbers table:

CREATE TABLE numbers 
( n int not null primary key ) ;

fill it with the preferred method of your DBMS:

INSERT INTO numbers (n)
VALUES (1), (2), .., (1000000) ;  -- some mildly complex SQL here
                                  -- no need to type a million numbers

and then use it, instead of generate_series():

WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p
  JOIN numbers AS gn (i) ON gn.i <= p.n
  JOIN numbers AS g2 (i) ON g2.i <= 2
  JOIN numbers AS gm (i) ON gm.i <= p.m 
ORDER BY
    gm.i, g2.i, gn.i ;
1
  • In practice, I don't expect those numbers to be bigger than 100; but in theory they could be anything.
    – joanolo
    Jan 8 '17 at 23:55
12

Postgres

You can make it work with a single generate_series() and basic math (see mathematical functions).

Wrapped into a simple SQL function:

CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
   SELECT n2m, n2m % (n*2) AS n2
   FROM   generate_series(0, n*2*m - 1) n2m
   ) sub
ORDER  BY n2m
$func$;

Call:

SELECT * FROM generate_up_down_series(3, 4);

Generates the desired result. n and m can be any integer where n*2*m does not overflow int4.

How?

In the subquery:

  • Generate the desired total number of rows (n*2*m), with a simple ascending number. I name it n2m. 0 to N-1 (not 1 to N) to simplify the following modulo operation.

  • Take it % n*2 (% is the modulo operator) to get a series of n ascending numbers, m times. I name it n2.

In the outer query:

  • Add 1 to lower half (n2 < n).

  • For the upper half (n2 >= n) mirror of the lower half with n*2 - n2.

  • I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

Unfortunately, generate_series() is Postgres specific and not standard SQL, as has been commented. But we can reuse the same logic:

Standard SQL

You can generate the serial numbers with a recursive CTE instead of generate_series(), or, more efficiently for repeated use, create a table with serial integer numbers once. Anyone can read, noone can write to it!

CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
   SELECT 0
   UNION ALL
   SELECT i+1 FROM cte
   WHERE  i < 20000  -- or as many you might need!
   )
INSERT INTO int_seq
SELECT i FROM cte;

Then, the above SELECT becomes even simpler:

SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
   SELECT i, i % (n*2) AS n2
   FROM   int_seq
   WHERE  i < n*2*m  -- remember: 0 to N-1
   ) sub
ORDER  BY i;
0
6

If you need plain SQL. Theoretically it should to work on the most DBMSs (tested on PostgreSQL and SQLite):

with recursive 
  s(i,n,z) as (
    select * from (values(1,1,1),(3*2,1,2)) as v  -- Here 3 is n
    union all
    select
      case z when 1 then i+1 when 2 then i-1 end, 
      n+1,
      z 
    from s 
    where n < 3), -- And here 3 is n
  m(m) as (select 1 union all select m+1 from m where m < 2) -- Here 2 is m

select n from s, m order by m, i;

Explanation

  1. Generate series 1..n

    Assuming that n=3

    with recursive s(n) as (
      select 1
      union all
      select n+1 from s where n<3
    )
    select * from s;
    

    It is quite simple and could be found in the almost any docs about recursive CTEs. However wee need two instances of each values so

  2. Generate series 1,1,..,n,n

    with recursive s(n) as (
      select * from (values(1),(1)) as v
      union all
      select n+1 from s where n<3
    )
    select * from s;
    

    Here we just doubling the initial value, which has two rows, but the second bunch we need in the reverse order, so we'll introduce the order in a bit.

  3. Before we introduce the order observe that this is also a thing. We can have two rows in the starting condition with three columns each, our n<3 is still a single column conditional. And, we're still just increasing the value of n.

    with recursive s(i,n,z) as (
      select * from (values(1,1,1),(1,1,1)) as v
      union all
      select
        i,
        n+1,
        z 
      from s where n<3
    )
    select * from s;
    
  4. Likewise, we can mix them up a bit, watch our starting condition change here: here we have a (6,2), (1,1)

    with recursive s(i,n,z) as (
      select * from (values(1,1,1),(6,1,2)) as v
      union all
      select
        i,
        n+1,
        z 
      from s where n<3
    )
    select * from s;
    
  5. Generate series 1..n,n..1

    The trick here is to generate the series, (1..n) twice, and then simply change the ordering on the second set.

    with recursive s(i,n,z) as (
      select * from (values(1,1,1),(3*2,1,2)) as v
      union all
      select
        case z when 1 then i+1 when 2 then i-1 end, 
        n+1,
        z 
      from s where n<3
    )
    select * from s order by i;
    

    Here i is order and z is number of the sequence (or half of sequence if you want). So for sequence 1 we are increasing order from 1 to 3 and for sequence 2 we are decreasing the order from 6 to 4. And finally

  6. Multiply the series to m

    (see the first query in the answer)

0
3

If you want a portable solution you need to realize that this is basically a mathematical problem.

Given @n as the highest number of the sequence and @x as the position of the number in that sequence (starting with zero), the following function would work in SQL Server:

CREATE FUNCTION UpDownSequence
(
    @n int, -- Highest number of the sequence
    @x int  -- Position of the number we need
)
RETURNS int
AS
BEGIN
    RETURN  @n - 0.5 * (ABS((2*((@x % (@n+@n))-@n)) +1) -1)
END
GO

You can check it with this CTE:

DECLARE @n int=3;--change the value as needed
DECLARE @m int=4;--change the value as needed

WITH numbers(num) AS (SELECT 0 
                      UNION ALL
                      SELECT num+1 FROM numbers WHERE num+1<2*@n*@m) 
SELECT num AS Position, 
       dbo.UpDownSequence(@n,num) AS number
FROM numbers
OPTION(MAXRECURSION 0)

(Quick explanation: the function uses MODULO() to create a sequence of repeating numbers and ABS() to turn it into a zig-zag wave. The other operations transform that wave to match the desired result.)

0
2

In PostgreSQL, this is easy,

CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
RETURNS setof int AS $$
SELECT x FROM (
  SELECT 1, ordinality AS o, x FROM generate_series(1,n) WITH ORDINALITY AS t(x)
  UNION ALL
  SELECT 2, ordinality AS o, x FROM generate_series(n,1,-1) WITH ORDINALITY AS t(x)
) AS t(o1,o2,x)
CROSS JOIN (
  SELECT * FROM generate_series(1,m)
) AS g(y)
ORDER BY y,o1,o2
$$ LANGUAGE SQL;
0
2

This works in MS-SQL and I think can be modified for any SQL flavor.

declare @max int, @repeat int, @rid int

select @max = 3, @repeat = 4

-- create a temporary table
create table #temp (row int)

--create seed rows
while (select count(*) from #temp) < @max * @repeat * 2
begin
    insert into #temp
    select 0
    from (values ('a'),('a'),('a'),('a'),('a')) as a(col1)
    cross join (values ('a'),('a'),('a'),('a'),('a')) as b(col2)
end

-- set row number can also use identity
set @rid = -1

update #temp
set     @rid = row = @rid + 1

-- if the (row/max) is odd, reverse the order
select  case when (row/@max) % 2 = 1 then @max - (row%@max) else (row%@max) + 1 end
from    #temp
where   row < @max * @repeat * 2
order by row
2

A way to do it in SQL Server using a recursive cte.

1) Generate the required number of members in the series (for n=3 and m=4 it would be 24 which is 2*n*m)

2) After that using logic in a case expression, you can generate the required series.

Sample Demo

declare @n int=3;--change the value as needed
declare @m int=4;--change the value as needed

with numbers(num) as (select 1 
                      union all
                      select num+1 from numbers where num<2*@n*@m) 
select case when (num/@n)%2=0 and num%@n<>0 then num%@n 
            when (num/@n)%2=0 and num%@n=0 then 1  
            when (num/@n)%2=1 and num%@n<>0 then @n+1-(num%@n)  
            when (num/@n)%2=1 and num%@n=0 then @n
       end as num
from numbers
OPTION(MAXRECURSION 0)

As suggested by @AndriyM .. the case expression can be simplified to

with numbers(num) as (select 0
                      union all
                      select num+1 from numbers where num<2*@n*@m-1) 
select case when (num/@n)%2=0 then num%@n + 1
            when (num/@n)%2=1 then @n - num%@n
       end as num
from numbers
OPTION(MAXRECURSION 0)

Demo

0
2

A basic function using iterators.

T-SQL

create function generate_up_down_series(@max int, @rep int)
returns @serie table
(
    num int
)
as
begin

    DECLARE @X INT, @Y INT;
    SET @Y = 0;

    WHILE @Y < @REP
    BEGIN

        SET @X = 1;
        WHILE (@X <= @MAX)
        BEGIN
            INSERT @SERIE
            SELECT @X;
            SET @X = @X + 1;
        END

        SET @X = @MAX;
        WHILE (@X > 0)
        BEGIN
            INSERT @SERIE
            SELECT @X;
            SET @X = @X -1;
        END

        SET @Y = @Y + 1;
    END

    RETURN;
end
GO

Postgres

create or replace function generate_up_down_series(maxNum int, rep int)
returns table (serie int) as
$body$
declare
    x int;
    y int;
    z int;
BEGIN

    x := 0;
    while x < rep loop

        y := 1;
        while y <= maxNum loop
            serie := y;
            return next;
            y := y + 1;
        end loop;

        z := maxNum;
        while z > 0 loop
            serie := z;
            return next;
            z := z - 1;
        end loop;

        x := x + 1;
    end loop;

END;
$body$ LANGUAGE plpgsql;
0
2

Using only basic Math + - * / and Modulo:

SELECT x
    , s = x % (2*@n) +
         (1-2*(x % @n)) * ( ((x-1) / @n) % 2)
FROM (SELECT TOP(2*@n*@m) x FROM numbers) v(x)
ORDER BY x;

This doesn't require a specific RDBMS.

With numbers being a number table:

...; 
WITH numbers(x) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n0(x)
    CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n1(x)
    CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n2(x)
)
...

This generate a number table (1-1000) without using a recursive CTE. See Sample. 2nm must be smaller than the number of row in numbers.

Output with n=3 and m=4:

x   s
1   1
2   2
3   3
4   3
5   2
6   1
7   1
8   2
... ...

This version requires a smaller number table (v >= n and v >= m):

WITH numbers(v) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM (VALUES(1), (2), (3), (4), (5), (6), ...) AS n(x)
)
SELECT ord = @n*(v+2*m) + n
    , n*(1-v) + ABS(-@n-1+n)*v
FROM (SELECT TOP(@n) v FROM numbers ORDER BY v ASC) n(n)
CROSS JOIN (VALUES(0), (1)) AS s(v)
CROSS JOIN (SELECT TOP(@m) v-1 FROM numbers ORDER BY v ASC) m(m)
ORDER BY ord;

See Sample.

1
declare @n int = 5;
declare @m int = 3;
declare @t table (i int, pk int identity);
WITH  cte1 (i) 
AS ( SELECT 1
     UNION ALL
     SELECT i+1 FROM cte1
     WHERE  i < 100  -- or as many you might need!
   )
insert into @t(i) select i from cte1 where i <= @m  order by i
insert into @t(i) select i from @t order by i desc
select t.i --, t.pk, r.pk 
from @t as t 
cross join (select pk from @t where pk <= @n) as r
order by r.pk, t.pk
0

Introduction:

This question intrigued me (+1) and I pondered

  • a) how to answer it and

  • b) how could the answer be generalised?

All of the code below can be found on the fiddle here.

Interestingly, nobody who's answered so far (10 answers - and some very good quality SQL/programming to boot!) has made use of ARRAYs (tutorial), which I believe are very helpful in this case.

In a general sense, my approach has been to generate the first series as an ARRAY ({1, 2,.. n, n,..2, 1}) and then generate m of these for the complete task.

I took three approaches:

  • the first is PostgreSQL specific, making use of GENERATE_SERIES() (tutorial) and ARRAYs. There's also a function call that can be replaced with a RECURSIVE CTE ("RCTE" - see tutorial).

  • the second (also PostgreSQL specific) combines an RCTE with a call to GENERATE_SERIES() and ARRAYs. The GENERATE_SERIES() can be replaced with an RCTE - see solution 3.

  • the third solution is "Pure SQL" and should work on any RDBMS that supports RCTEs (SQL Server for example).

Preparation step:

I used a table called param to store the values (3 & 5) as per the question - these can obviously be changed. Also, for the benchmarking steps (see below), I used this param table for all queries tested so that there would be an even playing field.

--
-- Setup of parameters...
--

CREATE TABLE param(n INT, m INT);
INSERT INTO param (VALUES(3, 5));
SELECT * FROM param;

The first method is as follows:

Method 1 - GENERATE_SERIES (fiddle):

Step 1:

--
-- Step 1
--
SELECT
  GENERATE_SERIES(1, (SELECT n FROM param)) AS the_first_series
UNION ALL
SELECT 
  GENERATE_SERIES((SELECT n FROM param), 1, -1);

Result:

the_first_series
               1
               2
               3
               3
               2
               1

Step 2:

--
--  Two possible Step 2s using a PL/pgSQL function or an RCTE
--
CREATE OR REPLACE FUNCTION fill_array_with_seq(the_array anyarray, seq_num INT)
RETURNS ANYARRAY LANGUAGE PLpgSQL AS $$
DECLARE
BEGIN
  FOR i IN 1..seq_num LOOP
    the_array[i] := i;
    i = i + 1;
  END LOOP;
  RETURN the_array;
end $$;

SELECT fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param));

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param);

Results (same):

fill_array_with_seq
      {1,2,3,4,5,6}
              f_arr
      {1,2,3,4,5,6}

Step 3:

--
-- Step 3
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT 
  ROW_NUMBER() OVER () AS rn,
  (
    SELECT f_arr FROM cte_fill_arr
    WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
  ),
  
  -- could use
  --
  -- fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param))
  --
  
  ARRAY
  (
    SELECT 
      GENERATE_SERIES(1, (SELECT n FROM param))
    UNION ALL
    SELECT 
      GENERATE_SERIES((SELECT n FROM param), 1, -1)
  ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x;

Result:

rn         f_arr             arr
 1  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 2  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 3  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 4  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 5  {1,2,3,4,5,6}   {1,2,3,3,2,1}

Finally:

--
--  Steps 4 & 5 - separate subquery not shown
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;

Result:

the_series
         1
         2
         3
         3
         2
         1
         1
         2
...
... snipped for brevity
...

Method 2 - Recursive CTE (fiddle):

Here, I managed to "kill two birds with one stone" by constructing both the desired sequence and its numbering scheme in the same RCTE as follows:

--
-- Step 1
--
WITH RECURSIVE cte_fill_array AS -- (cnt, val_arr, cnt_arr) AS
(
  SELECT 1 AS i, 1 AS cnt, ARRAY[1] AS val_arr, ARRAY[1] AS cnt_arr
  UNION ALL
  SELECT i + 1, cnt + 1, 
  ARRAY_APPEND
  (
    val_arr,
    (
      SELECT 
        CASE
          WHEN cnt < (SELECT n FROM param) THEN (i + 1)
          WHEN cnt = (SELECT n FROM param) THEN cnt
          WHEN cnt > (SELECT n FROM param) THEN 6 - i
        END
    )
  ),
  ARRAY_APPEND(cnt_arr, cnt + 1)
  FROM cte_fill_array
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT i, cnt, val_arr, cnt_arr FROM cte_fill_array;

Result:

i   cnt val_arr cnt_arr
1   1   {1} {1}
2   2   {1,2}   {1,2}
3   3   {1,2,3} {1,2,3}
4   4   {1,2,3,3}   {1,2,3,4}
5   5   {1,2,3,3,2} {1,2,3,4,5}
6   6   {1,2,3,3,2,1}   {1,2,3,4,5,6}

We only want the last record, so we select this by using the CARDINALITY() function - where that is equal to (n * 2) is the last record (step not shown individually).

Final step - see fiddle for more detail

--
--  Steps 2 - end
--
WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT arr AS the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;

Result (same as others):

the_series
         1
         2
         3
         3
...
... snipped for brevity
...

A simpler, (and IMHO) more elegant solution exists - using the GENERATE_SUBSCRIPTS() function (tutorial) as follows:

WITH RECURSIVE cte (i) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT the_arr
FROM
(
  SELECT 
    x, 
    UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr, 
    GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss

  FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
) AS s
ORDER BY x, ss;

Result (same):

the_series
1
2
3
3
...
... snipped for brevity
...

Method 3 - Pure SQL (fiddle):

Final step:

Since all of the code below has been seen above in one form or another, I'm just including the final step. No PostgreSQL specific functionality has been used and it also works under SQL Server 2019 (Linux).

WITH RECURSIVE cte (i, cnt) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
),
seq (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < (SELECT m FROM param)
)
SELECT i
FROM
(
  SELECT 
    s.n, c.cnt, c.i 
  FROM cte c
  CROSS JOIN
  (SELECT * FROM seq) AS s
) AS tab
ORDER BY n, cnt;

Result (same):

i
1
2
3
3

Benchmarking:

I benchmarked all of the PostgreSQL solutions.

I have done my utmost to be fair in these benchmarks - I used a param table (3, 5) in my SQL, but for generating a number table, I used a RECURSIVE CTE - I have tried to level the playing field by slightly modifying some of the SQL so that I was comparning like with like. If you have any objections, please let me know and I'll rerun any test with which you are unhappy.

I used db<>fiddle for the tests and the usual caveats apply - I don't know what else is running on that server at any given moment in time - I took an average of several runs for each solution.

  • Vérace GENERATE_SERIES SQL method (fiddle): 0.195 ms

  • Vérace RECURSIVE CTE SQL method (fiddle): 0.200 ms

  • Vérace GENERATE_SUBSCRIPTS() SQL method (fiddle): 0.110 ms

  • Vérace "Pure SQL" method (

  • a_horse_with_no_name's SQL method (fiddle) 0.150 ms

  • OP's SQL method (fiddle): 12.50 ms

  • OP's PL/pgSQL function method (fiddle): 0.60 ms

  • Erwin Brandtstetter's SQL method (fiddle): 15.5 ms

  • Erwin Brandtstetter's function method (fiddle): 0.150 ms

  • Abelisto's SQL method (fiddle) 0.145 ms

  • Evan Carroll's SQL method (fiddle) 0.125 ms

  • McNet's PL/pgSQL method (fiddle) 0.075 ms

The source code of PostgreSQL is a bit above my pay grade, but I believe that operations with GENERATE_SERIES and ARRAYs preserve order - the WITH ORDINALITY implies this (again, I think) - the correct answers come up even without paying attention to ordering (although this not a guarantee)! @ErwinBrandstetter says that:

  • I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

My understanding is that ARRAYs are fast in PostgreSQL because much of the backend code is implemented through them - but as I said, I'm not really a C expert.

It appears that "pure" PL/pgSQL (OP, McNets, then Erwin Brandstetter) is the fastest in this context, followed by SQL using ARRAYs and GENERATE_SUBSCRIPS() (me). Next is Evan Carroll's solution using GENERATE_SERIES() with ORDINALITY. Next is a_horse_with_no_name's SQL also using GENERATE_SERIES() and Abelisto's SQL using an RCTE. The OP's and Erwin Brandstetter's SQL are in last place - strangely since the EB's PL/pgSQL functions calls the same SQL. The OP's SQL has no relation to the PL/pgSQL.

I found these posts on ARRAYs from Erwin Brandstetter (1) & a_horse_with_no_name (2) very helpful! Other's that I found helpful were as follows (1, 2).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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