Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

doc: Improve description of new random(min, max) functions.
- 9c24111c4dad 19 (unreleased) landed
Add date and timestamp variants of random(min, max).
- faf071b55383 19 (unreleased) landed

[PATCH] Generate random dates/times in a specified range

Damien Clochard <damien@dalibo.info> — 2025-07-09T21:14:46Z

Hello,

As I am involved in the PostgreSQL Anonymizer extension, I found that 
the random(min,max) functions introduced recently are very useful to 
generate synthetic data or define a masking policy.

I decided to submit a similar set of functions for random dates and 
times.

So this adds 5 new variants of the random() function:

     random(min date, max date) returns date
     random(min time, max time) returns time
     random(min time, max time, zone text) returns timetz
     random(min timestamp, max timestamp) returns timestamp
     random(min timestamptz, max timestamptz) returns timestamptz

Each one returns a random date/time value t in the range min <= t <= 
max.

For the timetz function, a third parameter is required to define the 
timezone.
However if the value is an empty string, the session timezone is used.

These functions all rely on the pg_prng_int64_range function developped 
in
PG 17 for the random(bigint,bigint) function.

Regards,

-- 
Damien Clochard

Re: [PATCH] Generate random dates/times in a specified range

Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-09T22:14:42Z

Damien Clochard <damien@dalibo.info> writes:
> So this adds 5 new variants of the random() function:

>      random(min date, max date) returns date
>      random(min time, max time) returns time
>      random(min time, max time, zone text) returns timetz
>      random(min timestamp, max timestamp) returns timestamp
>      random(min timestamptz, max timestamptz) returns timestamptz

I'm a little uncomfortable with this proposal, mainly because it
overloads the random() function name to the point where I'm afraid
of "ambiguous function" failures in SQL code that used to be fine.

The traditional way of achieving these results would be something like

    select now() + random() * interval '10 days';

and I'm not convinced that the use-case is so large as to justify
adding built-in forms of that.

			regards, tom lane

Re: [PATCH] Generate random dates/times in a specified range

Damien Clochard <damien@dalibo.info> — 2025-07-11T10:09:09Z

Le 10.07.2025 00:14, Tom Lane a écrit :
> Damien Clochard <damien@dalibo.info> writes:
>> So this adds 5 new variants of the random() function:
> 
>>      random(min date, max date) returns date
>>      random(min time, max time) returns time
>>      random(min time, max time, zone text) returns timetz
>>      random(min timestamp, max timestamp) returns timestamp
>>      random(min timestamptz, max timestamptz) returns timestamptz
> 
> I'm a little uncomfortable with this proposal, mainly because it
> overloads the random() function name to the point where I'm afraid
> of "ambiguous function" failures in SQL code that used to be fine.
> 

Hi

Thanks for the feedback !

I agree with this, I overloaded the random() function because this is 
what was done previously with `random(int,int)` and I did the same like 
the good sheep that I am :)
but i'm fine with renaming this functions to daterandom, timerandom or 
whatever....

> The traditional way of achieving these results would be something like
> 
>     select now() + random() * interval '10 days';
> 
> and I'm not convinced that the use-case is so large as to justify
> adding built-in forms of that.
> 

 From my experience, when users are writing a set of masking rules, they 
tend to anonymize the dates with "a random date between start_date and 
end_date"

Which can be trasnlated like this

SELECT start_date+(random()*(end_date-start_date))::interval;

But when you have hundreds of masking rules, the meaning of this one is 
not so clear.

Now with PostgreSQL 18, we can write

SELECT random(start_date::int, end_date::int)::date;

Which is more explicit, but we could extend that logic to:

SELECT daterandom(start_date,end_date);

I agree this is merely syntactic sugar for the developers, but I don't 
see why it is ok to provide random(int,int) or random(numeric,numeric) 
and why random(date,date) is not.

Regards,

-- 
Damien Clochard

Re: [PATCH] Generate random dates/times in a specified range

Greg Sabino Mullane <htamfids@gmail.com> — 2025-07-12T15:14:43Z

Patch review

OVERALL:

I like the idea, especially the date variant. Unlike Tom, I'm not
particularly concerned about breakage of existing scripts, as
most already are working just fine with raw numbers and I don't see this
patch breaking them.

In a selfish vein, I would use the "date" and timestamp variants a lot. I
would use the "time" ones seldom to never.

HOUSEKEEPING:

* Needs a pgindent run

* Commit message typo: developped

DOCS:

* Suggest adding an example of a random(date, date) call at the end of the
<row> section

* Typo: speficy

* Suggest: "current session's timezone"

* Suggest: remove "instead"

CODE:

* Code comments should be consistent with "Returns" not "Generate"

* result = (DateADT) (int32) pg_prng_int64_range(&prng_state, rmin, rmax);

Is the int32 cast needed here? My compiler seems fine without it

*  This behaviour is based on generate_series_timestamptz_internal

Is it? I'm not seeing much overlap between the two

The addition of a timezone arg does complicate things. Is it really needed?

If not (just relying on session), timetzrandom becomes much much simpler
(e.g. time_timetz)

RUNNING:

* Found a bug:

greg=# select random('12:00'::time, '13:00'::time);
     random
-----------------
 12:41:35.612385

greg=# select random('12:00'::time, '13:00'::time, ''::text);
       random
--------------------
 12:20:58.308452-04

greg=# select random('12:00'::time, '13:00'::time, 'FOO'::text);
ERROR:  time zone "FOO" not recognized

greg=# select random('12:00'::time, '13:00'::time, 'IST-9'::text);
       random
--------------------
 12:42:46.384039+09

greg=# select random('12:00'::time, '13:00'::time, 'IST'::text);
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

At the very least, add a simple TLA timezone to the sql tests once fixed.


* Infinity

Fun, but is it useful? Perhaps disallow infinity. No strong opinion on this.

greg=# select random(now(), 'infinity'::date);
             random
--------------------------------
 86609-03-21 15:23:43.291271-04

greg=# select random('-infinity'::date, 'infinity'::date);
    random
---------------
 6126327-09-13



Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: [PATCH] Generate random dates/times in a specified range

Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-07-14T07:21:19Z

On Sat, 12 Jul 2025 at 16:15, Greg Sabino Mullane <htamfids@gmail.com> wrote:
>
> I like the idea, especially the date variant. Unlike Tom, I'm not particularly concerned about breakage of existing scripts, as
> most already are working just fine with raw numbers and I don't see this patch breaking them.
>
> In a selfish vein, I would use the "date" and timestamp variants a lot. I would use the "time" ones seldom to never.

But it's completely trivial to emulate random(min_date, max_date), just by doing

  min_date + random(0, max_date - min_date)

Is it really worth adding a core function for that?

Regards,
Dean

Re: [PATCH] Generate random dates/times in a specified range

Robert Treat <rob@xzilla.net> — 2025-07-15T03:48:50Z

On Mon, Jul 14, 2025 at 3:21 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> On Sat, 12 Jul 2025 at 16:15, Greg Sabino Mullane <htamfids@gmail.com> wrote:
> >
> > I like the idea, especially the date variant. Unlike Tom, I'm not particularly concerned about breakage of existing scripts, as
> > most already are working just fine with raw numbers and I don't see this patch breaking them.
> >
> > In a selfish vein, I would use the "date" and timestamp variants a lot. I would use the "time" ones seldom to never.
>
> But it's completely trivial to emulate random(min_date, max_date), just by doing
>
>   min_date + random(0, max_date - min_date)
>
> Is it really worth adding a core function for that?
>

I feel like this is a very similar argument against what was
ultimately the addition of timestamp based generate_series functions,
and similarly I think adding these in would be a rather useful
improvement for users, though like generate_series, we don't need to
hit every different data type (no one should ever generate a random
timetz for instance).

Robert Treat
https://xzilla.net

Re: [PATCH] Generate random dates/times in a specified range

Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-07-15T07:04:32Z

On Tue, 15 Jul 2025 at 04:49, Robert Treat <rob@xzilla.net> wrote:
>
> On Mon, Jul 14, 2025 at 3:21 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> >
> > But it's completely trivial to emulate random(min_date, max_date), just by doing
> >
> >   min_date + random(0, max_date - min_date)
> >
> > Is it really worth adding a core function for that?
>
> I feel like this is a very similar argument against what was
> ultimately the addition of timestamp based generate_series functions,
> and similarly I think adding these in would be a rather useful
> improvement for users, though like generate_series, we don't need to
> hit every different data type (no one should ever generate a random
> timetz for instance).

Right, and for generate_series() we didn't add a variant for type
date. In fact, we explicitly documented how a sequence of dates can be
generated using the date-plus-integer operator.

In fact, I think generate_series() sets a good precedent, and I could
get behind a proposal that adds new random() functions with the same
signatures as the timestamp-based generate_series() functions. In
particular, I think that it's quite likely that if I wanted a random
timestamp in some range, I would want some specified precision, like
say 'hour' or 'day', and not a timestamp with some random number of
microseconds.

(In a similar vein, it might be useful to add an optional "step"
parameter to the random integer/numeric functions, but that's really a
separate proposal.)

Regards,
Dean

Re: [PATCH] Generate random dates/times in a specified range

Greg Sabino Mullane <htamfids@gmail.com> — 2025-07-15T14:59:38Z

On Mon, Jul 14, 2025 at 3:21 AM Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

> But it's completely trivial to emulate random(min_date, max_date), just by
> doing
>
>   min_date + random(0, max_date - min_date)
>
> Is it really worth adding a core function for that?
>

Yes, I think it is. It is also trivial to get a random int from 50 to 100
with

50 + floor(random() * 51)

but random(50,100) is so much nicer.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: [PATCH] Generate random dates/times in a specified range

Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-15T15:10:42Z

Greg Sabino Mullane <htamfids@gmail.com> writes:
> On Mon, Jul 14, 2025 at 3:21 AM Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
>> Is it really worth adding a core function for that?

> Yes, I think it is. It is also trivial to get a random int from 50 to 100
> with
> 50 + floor(random() * 51)
> but random(50,100) is so much nicer.

I won't object to adding this for date/timestamp/timestamptz, but
I don't really believe that the time/timetz variants are worth the
code space.  I think the latter two would also have definitional
issues because of the modulo behavior of those types' underlying
arithmetic.

			regards, tom lane

Re: [PATCH] Generate random dates/times in a specified range

Greg Sabino Mullane <htamfids@gmail.com> — 2025-07-21T19:06:31Z

Damien, maybe we can let the time ones go? Tom and I are not big fans of
those, and nobody else has stepped up to defend them.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: [PATCH] Generate random dates/times in a specified range

Damien Clochard <damien@dalibo.info> — 2025-07-24T15:20:12Z

Le 21.07.2025 21:06, Greg Sabino Mullane a écrit :
> Damien, maybe we can let the time ones go? Tom and I are not big fans
> of those, and nobody else has stepped up to defend them.
> 

Sure !

Here's a second version with the following changes

- remove time and timetz variants
- disallow infinity boundaries
- remove unnecessary int32 cast
- add examples in the documentation
- run pgindent
- update tests
- fix typos

Thanks for the feedback and insights

-- 
Damien Clochard

Re: [PATCH] Generate random dates/times in a specified range

Vik Fearing <vik@postgresfriends.org> — 2025-07-24T18:24:29Z

On 24/07/2025 17:20, Damien Clochard wrote:
> Le 21.07.2025 21:06, Greg Sabino Mullane a écrit :
>> Damien, maybe we can let the time ones go? Tom and I are not big fans
>> of those, and nobody else has stepped up to defend them.
>>
>
> Here's a second version with the following changes
>
> - update tests 

Just a small cosmetic gripe, the regression test comments are usually 
"-- ok" and "--fail", and not "--  Should error".

No comment on the rest of the patch.

-- 

Vik Fearing

Re: [PATCH] Generate random dates/times in a specified range

Damien Clochard <damien@dalibo.info> — 2025-08-25T11:42:35Z

Le 24.07.2025 20:24, Vik Fearing a écrit :
>> [...]
> 
> Just a small cosmetic gripe, the regression test comments are usually
> "-- ok" and "--fail", and not "--  Should error".
> 

Thanks Vik!

Here's a third version with the following changes:

* Rebase from master
* Replace 'Should error' by 'fail' in the test file
* move documentation from func.sgml to func/func-datetime.sgml

Note that former random function variants are documented in 
func/func-math.sgml but it felt more logical to document the new 
variants in func-datetime.sgml. As a result the random variants would be 
documented in 2 separate sections of chapter 9.

-- 
Damien Clochard

Re: [PATCH] Generate random dates/times in a specified range

Greg Sabino Mullane <htamfids@gmail.com> — 2025-08-25T13:33:17Z
```
Your v3 did not get attached to the previous email.
```

Re: [PATCH] Generate random dates/times in a specified range

Damien Clochard <damien@dalibo.info> — 2025-08-26T14:17:03Z

Le 25.08.2025 15:33, Greg Sabino Mullane a écrit :
> Your v3 did not get attached to the previous email.

My bad, here it is

-- 
Damien Clochard

Re: [PATCH] Generate random dates/times in a specified range

Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-08-27T11:35:26Z

On Mon, 25 Aug 2025 at 12:42, Damien Clochard <damien@dalibo.info> wrote:
>
> Note that former random function variants are documented in
> func/func-math.sgml but it felt more logical to document the new
> variants in func-datetime.sgml. As a result the random variants would be
> documented in 2 separate sections of chapter 9.

Note the paragraph below the table listing the current random
functions, explaining the PRNG and the interaction with setseed(). If
you document the new functions in a separate section, that text would
need updating to refer to 2 separate locations (and possibly more in
the future), which seems a little messy. So I think it would be better
to keep documenting all the random functions together in the same
table.

Regards,
Dean

Re: [PATCH] Generate random dates/times in a specified range

Greg Sabino Mullane <htamfids@gmail.com> — 2025-08-28T17:22:12Z
```
Patch looks good
```

Re: [PATCH] Generate random dates/times in a specified range

Chao Li <li.evan.chao@gmail.com> — 2025-08-29T09:12:52Z

> On Aug 26, 2025, at 22:17, Damien Clochard <damien@dalibo.info> wrote:
> 
> Le 25.08.2025 15:33, Greg Sabino Mullane a écrit :
>> Your v3 did not get attached to the previous email.
> 
> My bad, here it is
> 
> -- 
> Damien Clochard<v3-0001-Generate-random-dates-times-in-a-specified-range.patch>

Code change looks good to me. Maybe we can make check_range_boundaries() “inline” as well.

But a major problem is, I think we should bump CATALOG_VERSION_NO. Otherwise, running your code with an existing database, the new functions won’t work.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Re: [PATCH] Generate random dates/times in a specified range

Vik Fearing <vik@postgresfriends.org> — 2025-08-29T14:14:19Z

On 29/08/2025 11:12, Chao Li wrote:
>
>
>> On Aug 26, 2025, at 22:17, Damien Clochard <damien@dalibo.info> wrote:
>>
>> Le 25.08.2025 15:33, Greg Sabino Mullane a écrit :
>>> Your v3 did not get attached to the previous email.
>>
>> My bad, here it is
>>
>> -- 
>> Damien 
>> Clochard<v3-0001-Generate-random-dates-times-in-a-specified-range.patch>
>
> Code change looks good to me. Maybe we can make 
> check_range_boundaries() “inline” as well.
>
> But a major problem is, I think we should bump CATALOG_VERSION_NO. 
> Otherwise, running your code with an existing database, the new 
> functions won’t work.


Traditionally, the patch committer bumps the catversion, not the patch 
author.

-- 

Vik Fearing

Re: [PATCH] Generate random dates/times in a specified range

Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-29T14:57:08Z

Vik Fearing <vik@postgresfriends.org> writes:
> On 29/08/2025 11:12, Chao Li wrote:
>> But a major problem is, I think we should bump CATALOG_VERSION_NO. 

> Traditionally, the patch committer bumps the catversion, not the patch 
> author.

Yeah.  If you include a catversion bump in a submitted patch, you can
expect the patch to break repeatedly while it's sitting in the queue,
due to unrelated patches changing catversion.  So we prefer to have
the committer add that at the last moment.

If you're worried that the committer might forget that, you can
add a comment about it in the patch's draft commit message.

			regards, tom lane

Re: [PATCH] Generate random dates/times in a specified range

Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-09-08T12:40:57Z

On Tue, 26 Aug 2025 at 15:17, Damien Clochard <damien@dalibo.info> wrote:
>
> Le 25.08.2025 15:33, Greg Sabino Mullane a écrit :
> > Your v3 did not get attached to the previous email.
>
> My bad, here it is

It seems like we have reached a consensus on adding just the random
date and timestamp[tz] functions, so I took a more detailed look with
an aim to committing this.

I'm attaching v4 with a few minor updates:

1). Updated the paragraph of text below "Table 9.6. Random Functions"
to also refer to "Table 9.33. Date/Time Functions", so that it's clear
that all the comments that follow apply to the date/time random()
functions too -- in particular, the part about setseed(). Having
reflected on it, I think that's a slightly better option than putting
the new functions in Table 9.6, because that's part of a whole section
about mathematical functions and everything there refers to number
types, not dates/timestamps, so putting the new functions there feels
a little out-of-place.

2). Changed check_range_boundaries() to a macro CHECK_RANGE_BOUNDS().
This feels a little neater, since it's such a trivial check, and the
datatype is not always int64.

3). Changed the C function names, adding an underscore for better
readability and consistency with other date/timestamp functions.

4). Used the DATE/TIMESTAMP_IS_NOBEGIN/NOEND() macros for neatness.

5). I didn't like this error message:

ERROR:  lower and upper bound cannot be infinite

because it's not grammatically correct, so I changed it to this:

ERROR:  lower and upper bounds must be finite

which is an error already used elsewhere for similar checks. This is
not quite the same as the errors thrown by random_numeric() -- perhaps
that should be changed to match (making its errors the same as the
errors thrown by width_bucket_numeric()).

6). It's not necessary to include utils/builtins.h or utils/datetime.h.

I think this is now committable, so if there are no objections, I'll
push this shortly.

Regards,
Dean

Re: [PATCH] Generate random dates/times in a specified range

Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-09-09T10:00:26Z

On Mon, 8 Sept 2025 at 13:40, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> I think this is now committable, so if there are no objections, I'll
> push this shortly.

Committed.

Regards,
Dean

Re: [PATCH] Generate random dates/times in a specified range

Marcos Pegoraro <marcos@f10.com.br> — 2025-09-10T13:29:45Z

Em ter., 9 de set. de 2025 às 07:00, Dean Rasheed <dean.a.rasheed@gmail.com>
escreveu:

> On Mon, 8 Sept 2025 at 13:40, Dean Rasheed <dean.a.rasheed@gmail.com>
> wrote:
> Committed.
>

This patch adds functions func-datetime.sgml but those functions depend on
setseed function, which is not there.
Wouldn't it be good to add a note that setseed() will change the result of
those random functions ?

regards
Marcos

Re: [PATCH] Generate random dates/times in a specified range

Dean Rasheed <dean.a.rasheed@gmail.com> — 2025-09-10T16:59:08Z

On Wed, 10 Sept 2025 at 14:30, Marcos Pegoraro <marcos@f10.com.br> wrote:
>
> This patch adds functions func-datetime.sgml but those functions depend on setseed function, which is not there.
> Wouldn't it be good to add a note that setseed() will change the result of those random functions ?

Agreed. I think something like the attached ought to be sufficient.

Regards,
Dean

Re: [PATCH] Generate random dates/times in a specified range

Marcos Pegoraro <marcos@f10.com.br> — 2025-09-10T18:28:49Z

Em qua., 10 de set. de 2025 às 13:59, Dean Rasheed <dean.a.rasheed@gmail.com>
escreveu:

> Agreed. I think something like the attached ought to be sufficient.
>
> Yeap, this way is fine.

regards
Marcos

Re: [PATCH] Generate random dates/times in a specified range

Damien Clochard <damien@dalibo.info> — 2025-09-15T10:40:15Z

Le 09.09.2025 12:00, Dean Rasheed a écrit :
> On Mon, 8 Sept 2025 at 13:40, Dean Rasheed <dean.a.rasheed@gmail.com> 
> wrote:
>> 
>> I think this is now committable, so if there are no objections, I'll
>> push this shortly.
> 
> Committed.
> 

Thanks a lot for your help on this !

-- 
Damien Clochard