PostgreSQL PL/pgSQL random value from array of values

Question

How can I declare an array like variable with two or three values and get them randomly during execution?

a := [1, 2, 5] -- sample sake
select random(a) -- returns random value

Any suggestion where to start?

Answer 1

Try this one:

select (array['Yes', 'No', 'Maybe'])[floor(random() * 3 + 1)];

Answer 2

Updated 2023-01-10 to fix the broken array literal. Made it several times faster while being at it:

CREATE OR REPLACE FUNCTION random_pick()
  RETURNS int
  LANGUAGE sql VOLATILE PARALLEL SAFE AS
$func$
SELECT ('[0:2]={1,2,5}'::int[])[trunc(random() * 3)::int];
$func$;

random() returns a value x where 0.0 <= x < 1.0. Multiply by 3 and truncate it with trunc() (slightly faster than floor()) to get 0, 1, or 2 with exactly equal chance.

Postgres indexes are 1-based by default (as per SQL standard). This would be off-by-1. We could increment by 1 every time, but for efficiency I declare the array index to start with 0 instead. Slightly faster, yet. See:

• Normalize array subscripts so they start with 1
• The manual on mathematical functions.

PARALLEL SAFE for Postgres 9.6 or later. See:

• PARALLEL label for a function with SELECT and INSERT
• When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

You can use the plain SELECT statement if you don't want to create a function:

SELECT ('[0:2]={1,2,5}'::int[])[trunc(random() * 3)::int];

Answer 3

Erwin Brandstetter answered the OP's question well enough. However, for others looking for understanding how to randomly pick elements from more complex arrays (like me some two months ago), I expanded his function:

CREATE OR REPLACE FUNCTION random_pick( a anyarray, OUT x anyelement )
  RETURNS anyelement AS
$func$
BEGIN
  IF a = '{}' THEN
    x := NULL::TEXT;
  ELSE
    WHILE x IS NULL LOOP
      x := a[floor(array_lower(a, 1) + (random()*( array_upper(a, 1) -  array_lower(a, 1)+1) ) )::int];
    END LOOP;
  END IF;
END
$func$ LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT;

Few assumptions:

• this is not only for integer arrays, but for arrays of any type

• we ignore NULL data; NULL is returned only if the array is empty or if NULL is inserted (values of other non-array types produce an error)

• the array don't need to be formatted as usual - the array index may start and end anywhere, may have gaps etc.

• this is for one-dimensional arrays

Other notes:

• without the first IF statement, empty array would lead to an endless loop

• without the loop, gaps and NULLs would make the function return NULL

• omit both array_lower calls if you know that your arrays start at zero

• with gaps in the index, you will need array_upper instead of array_length; without gaps, it's the same (not sure which is faster, but they shouldn't be much different)

• the +1 after second array_lower serves to get the last value in the array with the same probability as any other; otherwise it would need the random()'s output to be exactly 1, which never happens

• this is considerably slower than Erwin's solution, and likely to be an overkill for the your needs; in practice, most people would mix an ideal cocktail from the two

Answer 4

Here is another way to do the same thing

WITH arr AS (
    SELECT '{1, 2, 5}'::INT[] a
)
SELECT a[1 + floor((random() * array_length(a, 1)))::int] FROM arr;

You can change the array to any type you would like.

Answer 5

CREATE OR REPLACE FUNCTION pick_random( members anyarray )
RETURNS anyelement AS
$$
BEGIN
  RETURN members[trunc(random() * array_length(members, 1) + 1)];
END
$$ LANGUAGE plpgsql VOLATILE;

or

CREATE OR REPLACE FUNCTION pick_random( members anyarray )
RETURNS anyelement AS
$$
  SELECT (array_agg(m1 order by random()))[1]
  FROM unnest(members) m1;
$$ LANGUAGE SQL VOLATILE;

For bigger datasets, see:

• http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/
• http://www.depesz.com/2007/09/16/my-thoughts-on-getting-random-row/
• https://blog.2ndquadrant.com/tablesample-and-other-methods-for-getting-random-tuples/
• https://www.postgresql.org/docs/current/static/functions-math.html

Answer 6

CREATE FUNCTION random_pick(p_items anyarray)
RETURNS anyelement AS
$$
   SELECT unnest(p_items) ORDER BY RANDOM() LIMIT 1;
$$ LANGUAGE SQL;