Set random value from set

Question

I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.

Answer 1

Nice idea. I suggest two minor simplifications:

('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]

• Simpler syntax using an array literal ('{Foo,Bar,Poo}'::text[]) Shortens the string for longer lists. Additional benefit: explicit type declaration works for any type, not just for text. Your original idea happens to output text, because that's the default type for string literals.

• Use ceil() instead of floor() + 1. Same result.

OK, theoretically, the lower bound could be 0 precisely, as hinted in your comment, since random() produces (quoting the manual here):

random value in the range 0.0 <= x < 1.0

However, I have never seen that happen. Run a couple of million tests:

SELECT count(*)
FROM   generate_series(1,1000000)
WHERE  ceil(random())::int = 0;

-> SQLfiddle

To be perfectly safe, though, you can use Postgres custom array subscripts and still avoid the extra addition:

('[0:2]={Foo,Bar,Poo}'::text[])[floor(random()*3)]

See:

• Normalize array subscripts for 1-dimensional array so they start with 1
• Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

Better yet, use trunc(), that's a bit faster.

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]

Answer 2

I came up with idea to use Arrays to accomplish this:

(ARRAY['Foo','Bar','Poo'])[floor(random()*3)+1]

Answer 3

Based on this idea, I've created a function that was quite useful for me:

CREATE OR REPLACE FUNCTION random_choice(
    choices text[]
)
RETURNS text AS $$
DECLARE
    size_ int;
BEGIN
    size_ = array_length(choices, 1);
    RETURN (choices)[floor(random()*size_)+1];
END
$$ LANGUAGE plpgsql;

Usage examples:

• SELECT random_choice(array['h', 'i', 'j', 'k', 'l']) as random_char;

• SELECT random_choice((SELECT array_agg(name) FROM pets)) AS pet_name;