Set random value from set

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 border 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)]

Details under this related question on SO.

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

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

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

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