Re: How to generate random bigint

On Thu, Dec 21, 2023 at 7:21 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Phillip Diffley <phillip6402(at)gmail(dot)com> writes:
> > 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?
>
> Doesn't look like there's anything directly exposed for that.
> Since PG v13 you could use gen_random_uuid, if you're careful
> to extract only bits that aren't fixed by the v4 UUID spec.
> pgcrypto's pg_random_bytes() function offers another
> some-assembly-required solution that'd work considerably
> further back. Or you could make a custom C function that
> leverages pg_strong_random().

Also pg_read_binary_file('/dev/urandom', 0, 8) could be useful
(assuming you're on Unix) if you can figure out how to cast it...