40

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?

Improve this question

7 Answers 7

Reset to default
76

Try this one:

select (array['Yes', 'No', 'Maybe'])[floor(random() * 3 + 1)];
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Would this work the same in case there is more than 3 items in the array? Also would you please know how to get 2 random items rather than 1? Thanks!
It would be good to clarify what the magic numbers represent. If I wanted to try this on an array with a different number of items, what magic numbers would I use? I'd imagine the 3 here represents the length of the array, then regardless of the array length we should always + 1?
Yes, the '3' should be the number of elements in the array. The '1' is for making the indexing 1-based so it shouldn't be changed.
21 
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 - or declare the array index to start with 0 instead. Slightly faster, yet. See:

PARALLEL SAFE for Postgres 9.6 or later. See:

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];
Improve this answer

Comments

12

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.

Improve this answer

1 Comment

this is the simplest answer of all and does not use a fixed value for the index, instead it uses the array length.. for my humble point of view it might be the most correct answer! Thanks for sharing! :D
8

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

Improve this answer

2 Comments

Why not use array_length?
This causes an infinite loop on arrays that contain only NULL values. Might want to do something like a := ARRAY(SELECT x FROM unnest(a) AS rel(x) WHERE x NOT NULL).
3 
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:

Improve this answer

1 Comment

I definitely like this one as it uses the array as an argument and then array_length instead of the hard-coded number. I recently became a fan of width_bucket, so my version has RETURN vals[width_bucket(random(), 0, 1, array_length(vals, 1))]; but otherwise it's the same.
2

In Postgres 16+

You can use the array_sample function with a parameter of 1, then take the first value.

select (array_sample(ARRAY[1, 2, 5], 1))[1];

db<>fiddle

Improve this answer

Comments

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

1 Comment

This no longer seems to work in PostgreSQL 9.6: stackoverflow.com/questions/42179012/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.