Waiting for PostgreSQL 19 – Add date and timestamp variants of random(min, max).

On 9th of September 2025, Dean Rasheed committed patch:

Add date and timestamp variants of random(min, max).
 
This adds 3 new variants of the random() function:
 
    random(min date, max date) returns date
    random(min timestamp, max timestamp) returns timestamp
    random(min timestamptz, max timestamptz) returns timestamptz
 
Each returns a random value x in the range min <= x <= max.
 
Author: Damien Clochard <damien@dalibo.info>
Reviewed-by: Greg Sabino Mullane <htamfids@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Reviewed-by: Vik Fearing <vik@postgresfriends.org>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/f524d8cab5914613d9e624d9ce177d3d@dalibo.info

random() is amazing when it comes to generating test data. And I usually use the basic version, with no arguments that generates float8 value from 0.0 to, but excluding, 1.0. So I can change it to any value/datatype using relatively simple math, like, for example:

select now() - '1 year'::interval * random();

Will generate me a timestamptz that is somewhere within last year.

Anyway – now we got 3 new versions, so that we now have, in total 6 different versions of random(with, range), and one, normal, without arguments.

We can, of course test it:

=$ with test_data as (
    select random('2000-01-01'::date, '2000-04-30'::date) as r
    from generate_series(1,100000)
)
select min(r), max(r), count(*)
from test_data;
    min     |    max     | count  
------------+------------+--------
 2000-01-01 | 2000-04-30 | 100000
(1 row)
 
 
=$ with test_data as (
    select random('2000-01-01 01:23:45'::timestamp, '2000-04-30 01:23:45'::timestamp) as r
    from generate_series(1,100000)
)
select min(r), max(r), count(*)
from test_data;
            min             |            max             | count  
----------------------------+----------------------------+--------
 2000-01-01 01:24:10.154934 | 2000-04-30 01:19:23.423265 | 100000
(1 row)
 
=$ with test_data as (
    select random(now() - '2 months'::interval, now()) as r
    from generate_series(1,100000)
)
select min(r), max(r), count(*)
from test_data;
              min              |              max              | count  
-------------------------------+-------------------------------+--------
 2025-07-17 17:11:52.219076+02 | 2025-09-17 17:09:55.847463+02 | 100000
(1 row)

So, works as expected. And will make writing test data definitely easier. Thanks a lot to everyone involved.

=$
|

Waiting for PostgreSQL 19 – Add date and timestamp variants of random(min, max).

Leave a Reply