We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Generate a Random Integer Within a Range — Vertica Forum

Generate a Random Integer Within a Range

Jim_KnicelyJim_Knicely - Select Field - Administrator
edited February 2019 in Tips from the Team

The RANDOMINT functions returns one of the n integers from 0 through n – 1.

If you need to generated a random integer that falls within a range of integers between X and Y, inclusive, use this simple formula:

FLOOR(RANDOM() * (Y - X + 1) + X)

Example:

dbadmin=> SELECT FLOOR(RANDOM() * (20 - 10 + 1) + 10)     AS random_integer_between_10_and_20,
dbadmin->        FLOOR(RANDOM() * (100 - 50 + 1) + 50)    AS random_integer_between_50_and_100,
dbadmin->        FLOOR(RANDOM() * (1000 – 100 + 1) + 100) AS random_integer_between_100_and_1000;
random_integer_between_10_and_20 | random_integer_between_50_and_100 | random_integer_between_100_and_1000
---------------------------------+-----------------------------------+-------------------------------------
                              17 |                                95 |                                 920
(1 row)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOM.htm

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm

Sign In or Register to comment.