Generate a random number in the range 1 - 10

Question

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)

To summarize and a bit simplify, you can use:

-- 0 - 9
select floor(random() * 10);
-- 0 - 10
SELECT floor(random() * (10 + 1));
-- 1 - 10
SELECT ceil(random() * 10);

And you can test this like mentioned by @user80168

-- 0 - 9
SELECT min(i), max(i) FROM (SELECT floor(random() * 10) AS i FROM generate_series(0, 100000)) q;
-- 0 - 10
SELECT min(i), max(i) FROM (SELECT floor(random() * (10 + 1)) AS i FROM generate_series(0, 100000)) q;
-- 1 - 10
SELECT min(i), max(i) FROM (SELECT ceil(random() * 10) AS i FROM generate_series(0, 100000)) q;

If you are using SQL Server then correct way to get integer is

SELECT Cast(RAND()*(b-a)+a as int);

Where

• 'b' is the upper limit
• 'a' is lower limit

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

The correct version of hythlodayr's answer.

-- ERROR:  operator does not exist: double precision % integer
-- LINE 1: select (trunc(random() * 10) % 10) + 1

The output from trunc has to be converted to INTEGER. But it can be done without trunc. So it turns out to be simple.

select (random() * 9)::INTEGER + 1

Generates an INTEGER output in range [1, 10] i.e. both 1 & 10 inclusive.

For any number (floats), see user80168's answer. i.e just don't convert it to INTEGER.