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
Phillip Diffley <phillip6402@gmail.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().
regards, tom lane
On Thu, Dec 21, 2023 at 7:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Phillip Diffley <phillip6402@gmail.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...
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!"
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;
Show quoted text
On Thu, Dec 21, 2023 at 6:14 AM Peter J. Holzer <hjp-pgsql@hjp.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@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
On Sat, Dec 23, 2023 at 8:36 AM Phillip Diffley <phillip6402@gmail.com> 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, 1000000);
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, 1000000);
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, 1000000);
Time: 18947.639 ms (00:18.948)
postgres=# explain analyze select (random() * 2147483648)::int8 *
4294967296 + (random() * 4294967296)::int8 from generate_series(1,
1000000);
Time: 728.368 ms
Peter's way has the best performance.
On Thu, Dec 21, 2023 at 6:14 AM Peter J. Holzer <hjp-pgsql@hjp.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@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
--
Regards
Junwang Zhao