Generate a random number in the range 1 - 10

sql postgresql random
KB22 picture KB22 · Sep 9, 2009 · Viewed 134.5k times · Source

Since my approach for a test query which I worked on in this question did not work out, I'm trying something else now. Is there a way to tell pg's random() function to get me only numbers between 1 and 10?

Answer

user80168 picture user80168 · Sep 9, 2009

If by numbers between 1 and 10 you mean any float that is >= 1 and < 10, then it's easy:

select random() * 9 + 1

This can be easily tested with:

# select min(i), max(i) from (
    select random() * 9 + 1 as i from generate_series(1,1000000)
) q;
       min       |       max
-----------------+------------------
 1.0000083274208 | 9.99999571684748
(1 row)

If you want integers, that are >= 1 and < 10, then it's simple:

select trunc(random() * 9 + 1)

And again, simple test:

# select min(i), max(i) from (
    select trunc(random() * 9 + 1) as i from generate_series(1,1000000)
) q;
 min | max
-----+-----
   1 |   9
(1 row)

Related questions

Best way to select random rows PostgreSQL

I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a very large table with 500 Million rows, I …

Read More
Simple Random Samples from a Sql database

How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000. The "obvious" answer is to: SELECT * FROM …

Read More
Select random row for each group

I have a table like this ID ATTRIBUTE 1 A 1 A 1 B 1 C 2 B 2 C 2 C 3 A 3 B 3 C I'd like to select just one random attribute for each ID. The result therefore could look like this (although this is just …

Read More