CODEWITHSUNDEEP
postgresql
Asher Hoskins
Asher Hoskins

Reputation: 33

Generating multiple rows of random arrays in PostgreSQL?

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?

Upvotes: 2

Views: 2398

Answers (1)

klin
klin

Reputation: 121624

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.

Upvotes: 3

Related Questions