rand() and rand(integer) Functions
The rand() and rand(integer) functions return a pseudo random floating point value between 0 and 1. If one parameter is specified, it is used as random seed.
Note: The rand() and rand(integer) functions will create different numbers depending on the table structure, if it is x100, or otherwise, as demonstrated in the following example.
drop table if exists table1;
create table table1(col1 int) with structure=heap; \g
insert into table1 values(2);
insert into table1 values(3);
insert into table1 values(2); \g
select rand(2), rand(col1) from table1; \g
This will result in:
In the first case '2' is always used as a random seed and in the latter case, the respective column value is used. As a result, if we have the value '2' in the column, the created value will be '0.020'.
However, if we replace "heap" by "x100" in the above example, we obtain the following:
Here, 0.672 is the result of x100’s random number creation using ‘2’ as random seed. However, during the process of creating random numbers, x100 automatically modifies the random seed. As x100 processes the input in chunks rather than single tuples, the new random seed is used for the second selection, resulting in the output 0.008. Then, once again, the random seed is modified, which leads to the output 0.344. The difference for the case of rand(col1) comes from the fact that x100 will use the global random seed for the second and third number (and modify it appropriately).
Last modified date: 08/14/2024