(PostgreSQL) random number range 1 - 10

Question

Hi all,

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 wayto tell pg's random() function to getme only numbers between 1 and 10?

thx for your time

K

Answer 1

(trunc(random() * 10) % 10) + 1

Answer 2

If by numbers between 1 and 10 you mean any float that is >= 1 and < 10, than, 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, than 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)