How to create Array with range in PostgreSQL

Question

In PostgreSQL, it is possible to create an array with elements (https://www.postgresql.org/docs/current/functions-array.html):

SELECT ARRAY[1,2,3,4] AS indexes;

Is there a function to generate an array by specifying begin and end? like

SELECT array_from_to(1, 4) AS indexes

Tim Biegeleisen · Accepted Answer · 2019-04-09 05:08:06Z

12

We can use a combination of the ARRAY function with generate_series, e.g.

SELECT ARRAY(
    SELECT a.n
    FROM generate_series(1, 4) AS a(n)
);

Demo

answered Apr 9, 2019 at 5:08

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Could be even shorter: ARRAY(SELECT * FROM generate_series(1, 4))

Laurenz Albe · Accepted Answer · 2019-04-09 05:56:19Z

9

You can use the ARRAY_AGG function in conjunction with the GENERATE_SERIES function.

For example:

SELECT ARRAY_AGG(index) AS indexes
FROM GENERATE_SERIES(1,4) AS index;

Output:

  indexes  
-----------
 {1,2,3,4}

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Apr 9, 2019 at 5:07

Henry

15.9k7 gold badges51 silver badges69 bronze badges