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@xxxxxx> 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@xxxxxx | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!"
