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/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.