Re: How to generate random bigint

Thank you for all the suggestions! I ended up using pgcrypto's
pg_random_bytes() to build the random int. I haven't fully tested the
function yet, but it looks like this works.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION gen_random_int() RETURNS INT8 AS $$
DECLARE
bytes bytea;
BEGIN
bytes := gen_random_bytes(8);
RETURN
(get_byte(bytes,0)::int8 << 8*0) |
(get_byte(bytes,1)::int8 << 8*1) |
(get_byte(bytes,2)::int8 << 8*2) |
(get_byte(bytes,3)::int8 << 8*3) |
(get_byte(bytes,4)::int8 << 8*4) |
(get_byte(bytes,5)::int8 << 8*5) |
(get_byte(bytes,6)::int8 << 8*6) |
(get_byte(bytes,7)::int8 << 8*7);
END;
$$ LANGUAGE plpgsql;

On Thu, Dec 21, 2023 at 6:14 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>
> 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(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"