Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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 that works for any type, not just fortext. 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 the comment by @korda, 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 tiny bit faster.

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]
share|improve this answer
    
ceil(0) == floor(0) + 1? –  korda Dec 20 '13 at 23:50
    
@korda: I added more, addressing that. –  Erwin Brandstetter Dec 21 '13 at 3:30
add comment

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

(ARRAY['Foo','Bar','Poo'])[floor(random()*3)+1]
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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