Home » SQL » PostgreSQL: Select a Random Number in a Range (Between Two Numbers)

PostgreSQL: Select a Random Number in a Range (Between Two Numbers)

Updated by JR on May 26, 2010 in SQL | 0 comments

Do you like this?

This is quick tip howto select a random number in a range. Example random number between 1-100 (>= 1 and < 100). This is actually very easy job with PostgreSQL own random() function, which returns random value between 0-1. Following example selects 5 random values using generate_series() function:

SELECT random() FROM generate_series(1,5);
      random       
-------------------
 0.595817462075502
 0.350072300527245
 0.989073566626757
 0.700899163261056
 0.940509075298905
(5 ROWS)

Next thing to todo is convert random function values to real float values in a range 1-100:

SELECT random() * 99 + 1 FROM generate_series(1,5);
     ?COLUMN?     
------------------
 45.0829070857726
 96.3123500789516
 30.1867960244417
 63.0484761050902
 44.2645903658122
(5 ROWS)

And finally truncate floats to integers with trunc function:

SELECT trunc(random() * 99 + 1) FROM generate_series(1,5);
 trunc 
-------
    21
     6
     2
    57
    58
(5 ROWS)

Then this select is easy to convert simple function to get random numbers between two values:

CREATE OR REPLACE FUNCTION get_random_number(INTEGER, INTEGER) RETURNS INTEGER AS $$
DECLARE
    start_int ALIAS FOR $1;
    end_int ALIAS FOR $2;
BEGIN
    RETURN trunc(random() * (end_int-start_int) + start_int);
END;
$$ LANGUAGE 'plpgsql' STRICT;

Function usage:

SELECT get_random_number(1, 100); 
get_random_number 
-------------------
                11
(1 ROW)
 
SELECT get_random_number(45, 46);
 get_random_number 
-------------------
                45
(1 ROW)
 
SELECT get_random_number(300, 400);
 get_random_number 
-------------------
               372
(1 ROW)
 
SELECT MIN(get_random_number(100, 1000)), MAX(get_random_number(100, 1000)) FROM generate_series(1,100000);
 MIN | MAX 
-----+-----
 100 | 999
(1 ROW)