xCloudx8 xCloudx8 - 2 months ago 6
SQL Question

Update table with random numbers

I need to update my table with random numbers, this is my table:

chr pos ref alt id_disease
chr1 123 A A NULL
chr2 456 A T NULL


I want to update the disease column with random numbers from 1 to 30000079.

To obtain this:

chr pos ref alt id_disease
chr1 123 A A 5024
chr2 456 A T 300045


Is there some random functions?

Answer

Use RANDOM():

UPDATE yourTable
SET id_disease = FLOOR(RANDOM() * 30000079) + 1

Explanation

Postgres' RANDOM() function returns a number in the range 0.0 <= x < 1.0. In the query above, this means that the smallest value would occur when RANDOM() returns 0, giving 1 as the value. The highest value would occur when RANDOM() returns something like 0.999999, which would give a value slightly below 30000079. The FLOOR function would take it down to 30000078, but then we add 1 to it to bring it back to 30000079, your highest value.

Comments