18

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.

3 Answers 3

34

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:

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

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]
5
  • ceil(0) == floor(0) + 1?
    – korda
    Dec 20, 2013 at 23:50
  • @korda: I added more, addressing that. Dec 21, 2013 at 3:30
  • @ErwinBrandstetter don't you think that ceil(random())::int will always give you 1 so you won't be able check if it will ever return 0?
    – aki92
    Jan 17, 2018 at 10:23
  • @aki92: ceil(0.0) wouldn't, that's the point. OTOH: for the purpose of this test we might simplify: WHERE random() = 0.0. Jan 17, 2018 at 11:59
  • @ErwinBrandstetter oh true, sorry just missed that thing.
    – aki92
    Jan 17, 2018 at 12:16
9

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

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

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;

1
  • You can morph this function into a function with variadic parameter which I personally find more user friendly.
    – Sahap Asci
    Jun 13, 2019 at 8:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.