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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 9 down vote accepted

Nice idea. I suggest two minor simplifications:

  • 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:


Details under this related question on SO.

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

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

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

share|improve this answer

Your Answer


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.