Re: How to generate random bigint
On Sat, Dec 23, 2023 at 8:36 AM Phillip Diffley wrote:
>
> 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;
>
postgres=# explain analyze select gen_random_int() from
generate_series(1, 100);
Time: 4794.352 ms (00:04.794)
postgres=# explain analyze select ('x'||encode(gen_random_bytes(8),
'hex'))::bit(64)::int8 from generate_series(1, 100);
Time: 2816.014 ms (00:02.816)
postgres=# explain analyze select
('x'||encode(pg_read_binary_file('/dev/urandom', 0, 8),
'hex'))::bit(64)::bigint from generate_series(1, 100);
Time: 18947.639 ms (00:18.948)
postgres=# explain analyze select (random() * 2147483648)::int8 *
4294967296 + (random() * 4294967296)::int8 from generate_series(1,
100);
Time: 728.368 ms
Peter's way has the best performance.
>
> On Thu, Dec 21, 2023 at 6:14 AM Peter J. Holzer 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.
> > |_|_) ||
> > | | | h...@hjp.at |-- Charles Stross, "Creative writing
> > __/ | http://www.hjp.at/ | challenge!"
>
>
--
Regards
Junwang Zhao
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 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. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!"
Re: How to generate random bigint
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. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: How to generate random bigint
On Thu, Dec 21, 2023 at 7:21 PM Tom Lane wrote:
> Phillip Diffley 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...
Re: How to generate random bigint
Phillip Diffley 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(). regards, tom lane
How to generate random bigint
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? Thanks, Phillip
