Re: Functions to return random numbers in a given range

On Fri, Dec 22, 2023 at 1:07 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> Attached is a patch that adds 3 SQL-callable functions to return
> random integer/numeric values chosen uniformly from a given range:
>
> random(min int, max int) returns int
> random(min bigint, max bigint) returns bigint
> random(min numeric, max numeric) returns numeric
>
> The return value is in the range [min, max], and in the numeric case,
> the result scale equals Max(scale(min), scale(max)), so it can be used
> to generate large random integers, as well as decimals.
>
> The goal is to provide simple, easy-to-use functions that operate
> correctly over arbitrary ranges, which is trickier than it might seem
> using the existing random() function. The main advantages are:
>
> 1. Support for arbitrary bounds (provided that max >= min). A SQL or
> PL/pgSQL implementation based on the existing random() function can
> suffer from integer overflow if the difference max-min is too large.
>

Your patch works.
performance is the best amount for other options in [0].
I don't have deep knowledge about which one is more random.

Currently we have to explicitly mention the lower and upper bound.
but can we do this:
just give me an int, int means the int data type can be represented.
or just give me a random bigint.
but for numeric, the full numeric values that can be represented are very big.

Maybe we can use the special value null to achieve this
like use
select random(NULL::int,null)
to represent a random int in the full range of integers values can be
represented.

Do you think it makes sense?

[0] https://www.postgresql.org/message-id/CAEG8a3LcYXjNU1f2bxMm9c6ThQsPoTcvYO_kOnifx3aGXkbgPw%40mail.gmail.com