Re: How to generate random bigint - Mailing list pgsql-general

On 2023-12-21 00:06:39 -0600, Phillip Diffley wrote:
> Postgres's random() function generates a random double. That can be converted
> to a random int for smaller integers, but a double can't represent all of the
> values in a bigint. Is there a recommended way to generate a random bigint in
> Postgres?

Call random() twice and add the results?

Like this:

select (random() * 2147483648)::int8 * 4294967296
       + (random() * 4294967296)::int8;

(This assumes that random() actually returns at least 32 random bits.
If that's not the case you'll need more calls to random())

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"