Categories:

Data Generation Functions

RANDOM

Each call returns a pseudo-random 64-bit integer.

Syntax

RANDOM([seed])

Arguments

seed

Seed is an integer. Different seeds will cause RANDOM to produce different output values.

Usage Notes

  • Calling RANDOM repeatedly with no seed will produce different values. However, calling RANDOM repeatedly with the same seed will produce the same value each time; if you want to call RANDOM more than once with seeds, you probably should pass a different seed each time.

  • Calling RANDOM more than once inside the same SQL statement will cause each call within that execution of the statement to return the same value (essentially, the function is called once and the result is re-used for the remainder of the statement execution). For an example, see the Examples section below.

  • The output is only pseudo-random; the output can be predicted given enough information (including the algorithm and the seed).

  • RANDOM implements a 64-bit Mersenne twister algorithm known as MT19937-64.

  • Because the output is a finite integer, it is possible to “wrap around” and start repeating sequences of values. However, the “period” (number of calls before wrapping) is very large: 2^19937 - 1.

  • The optional seed argument must be an integer constant. If no value for seed is provided, a random seed is chosen in a platform-specific manner. Even if a value for seed is provided, the generated sequence is only fully deterministic if execution is serial (single-threaded).

  • Generating pseudo-random numbers is somewhat expensive computationally; large numbers of calls to this function can consume significant resources.

  • Random values are not unique values. If you need unique values, consider using a sequence (SEQ1 / SEQ2 / SEQ4 / SEQ8) rather than a call to RANDOM(). Choose a sequence with enough bits that it is unlikely to wrap around.

Examples

Here is a simple example:

SELECT random(4711) FROM table(generator(rowCount => 5));

Output:

+----------------------+
|     random(4711)     |
|----------------------|
| -3581185414942383166 |
|  1570543588041465562 |
| -6684111782596764647 |
| -3878722496384921389 |
| -1136601514902083621 |
+----------------------+

This shows that if you call RANDOM more than once inside the same execution of the same statement, you’ll get the same value each time:

SELECT random(), random();

Output:

+----------------------+----------------------+
|             RANDOM() |             RANDOM() |
|----------------------+----------------------|
| -1155356907212095383 | -1155356907212095383 |
+----------------------+----------------------+