Generating multiple rows of random arrays in PostgreSQL?

Question

For testing a database I'd like to generate a number of rows, each consisting of a set length array of random numbers. I can generate n rows of random numbers easily:

select round(random()*10) from generate_series(1,5);

and I can generate an array of random numbers:

select array(select round(random()*10) from generate_series(1,5));

but if I put them together the array() subquery only gets executed once:

select array(select round(random()*10) from generate_series(1,5)) from generate_series(1,5);

    array     
--------------
 {10,8,1,7,4}
 {10,8,1,7,4}
 {10,8,1,7,4}
 {10,8,1,7,4}
 {10,8,1,7,4}
(5 rows)

How do I get the subquery to run for each row?

klin · Accepted Answer · 2018-11-29 23:53:08Z

3

I use the function:

create or replace function random_int_array(int, int)
returns int[] language sql as
$$
    select array_agg(round(random()* $1)::int)
    from generate_series(1, $2)
$$;

select random_int_array(10, 5)
from generate_series(1, 5)

 random_int_array 
------------------
 {3,8,4,7,5}
 {0,10,3,5,4}
 {2,2,0,2,10}
 {8,1,4,3,9}
 {7,4,1,3,6}
(5 rows)

Note that the function should be volatile.

Update. Another approach, generate 25 random values and aggregate them in 5 groups:

select array_agg(round(random()* 10))
from generate_series(1, 25) i
group by i % 5

db<>fiddle.

edited Nov 29, 2018 at 23:53

answered Nov 29, 2018 at 1:54

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Asher Hoskins Over a year ago

Thank you! That worked, and I learnt about volatile functions too!

Collectives™ on Stack Overflow

Generating multiple rows of random arrays in PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related