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 fortext
. Your original idea happens to outputtext
, because that's the default type for string literals.Use
ceil()
instead offloor() + 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]