Shuffle an array in postgres
Somtimes an array is just too sequential
select ARRAY[1,2,3,4,5];
chriserin=# select ARRAY[1,2,3,4,5];
array
-------------
{1,2,3,4,5}
(1 row)
I want to shuffle this array into an unpredictable mess of numbers, and one way to do this in PostgreSQL is to turn these array elements into rows, order them randomly and then squash the rows back into an array like so:
chriserin=# select array_agg(foo.x) from (select unnest(ARRAY[1,2,3,4,5]) x order by random()) foo;
array_agg
-------------
{2,5,4,3,1}
(1 row)
The key here is the order by random()
clause, which is a special postgres case. order by 0.284325
which is a number that random() could return, is not valid sql. There are dragons in order by, read here for more info
http://postgresql.nabble.com/select-random-order-by-random-td1891064.html