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
From a731699566537544652cd09d5ac58ea99bceb2a3 Mon Sep 17 00:00:00 2001
From: damien <damien.cloch...@dalibo.com>
Date: Thu, 3 Jul 2025 13:13:30 +0000
Subject: [PATCH v2] Generate random dates/times in a specified range
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 one returns a random date/timestamp value t in the range min <= t <= max.
These functions all rely on the pg_prng_int64_range function developed in
PG 17 for the random(bigint,bigint) function.
---
doc/src/sgml/func.sgml | 20 ++++
src/backend/utils/adt/pseudorandomfuncs.c | 106 ++++++++++++++++++++--
src/include/catalog/pg_proc.dat | 12 +++
src/test/regress/expected/random.out | 87 ++++++++++++++++++
src/test/regress/sql/random.sql | 26 ++++++
5 files changed, 243 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index de5b5929ee0..9677ffa619d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1926,6 +1926,18 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<function>random</function> ( <parameter>min</parameter> <type>numeric</type>, <parameter>max</parameter> <type>numeric</type> )
<returnvalue>numeric</returnvalue>
</para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>date</type>, <parameter>max</parameter> <type>date</type> )
+ <returnvalue>date</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>timestamp</type>, <parameter>max</parameter> <type>timestamp</type> )
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>random</function> ( <parameter>min</parameter> <type>timestamptz</type>, <parameter>max</parameter> <type>timestamptz</type> )
+ <returnvalue>timestamptz</returnvalue>
+ </para>
<para>
Returns a random value in the range
<parameter>min</parameter> <= x <= <parameter>max</parameter>.
@@ -1940,6 +1952,14 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<para>
<literal>random(-0.499, 0.499)</literal>
<returnvalue>0.347</returnvalue>
+ </para>
+ <para>
+ <literal>random('1979-02-08'::date,'2025-07-03'::date)</literal>
+ <returnvalue>1983-04-21</returnvalue>
+ </para>
+ <para>
+ <literal>random('2000-01-01'::timestamptz, now())</literal>
+ <returnvalue>2015-09-27 09:11:33.732707+00</returnvalue>
</para></entry>
</row>
diff --git a/src/backend/utils/adt/pseudorandomfuncs.c b/src/backend/utils/adt/pseudorandomfuncs.c
index e7b8045f925..c8801d22f8d 100644
--- a/src/backend/utils/adt/pseudorandomfuncs.c
+++ b/src/backend/utils/adt/pseudorandomfuncs.c
@@ -17,6 +17,9 @@
#include "common/pg_prng.h"
#include "miscadmin.h"
+#include "utils/builtins.h"
+#include "utils/date.h"
+#include "utils/datetime.h"
#include "utils/fmgrprotos.h"
#include "utils/numeric.h"
#include "utils/timestamp.h"
@@ -25,6 +28,20 @@
static pg_prng_state prng_state;
static bool prng_seed_set = false;
+/*
+ * check_range_boundaries() -
+ *
+ * throw an error if the range boundaries are inverted
+ */
+static void
+check_range_boundaries(int64 rmin, int64 rmax)
+{
+ if (rmin > rmax)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower bound must be less than or equal to upper bound"));
+}
+
/*
* initialize_prng() -
*
@@ -129,10 +146,7 @@ int4random(PG_FUNCTION_ARGS)
int32 rmax = PG_GETARG_INT32(1);
int32 result;
- if (rmin > rmax)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("lower bound must be less than or equal to upper bound"));
+ check_range_boundaries(rmin, rmax);
initialize_prng();
@@ -153,10 +167,7 @@ int8random(PG_FUNCTION_ARGS)
int64 rmax = PG_GETARG_INT64(1);
int64 result;
- if (rmin > rmax)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("lower bound must be less than or equal to upper bound"));
+ check_range_boundaries(rmin, rmax);
initialize_prng();
@@ -183,3 +194,82 @@ numeric_random(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(result);
}
+
+
+/*
+ * daterandom() -
+ *
+ * Returns a random date chosen uniformly in the specified range.
+ */
+Datum
+daterandom(PG_FUNCTION_ARGS)
+{
+ int32 rmin = (int32) PG_GETARG_DATEADT(0);
+ int32 rmax = (int32) PG_GETARG_DATEADT(1);
+ DateADT result;
+
+ check_range_boundaries(rmin, rmax);
+
+ if (rmin == DATEVAL_NOBEGIN || rmax == DATEVAL_NOEND)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower and upper bound cannot be infinite"));
+
+ initialize_prng();
+
+ result = (DateADT) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_DATEADT(result);
+}
+
+/*
+ * timestamprandom() -
+ *
+ * Generate random timestamp chosen uniformly in the specified range.
+ */
+Datum
+timestamprandom(PG_FUNCTION_ARGS)
+{
+ int64 rmin = (int64) PG_GETARG_TIMESTAMP(0);
+ int64 rmax = (int64) PG_GETARG_TIMESTAMP(1);
+ Timestamp result;
+
+ check_range_boundaries(rmin, rmax);
+
+ if (rmin == TIMESTAMP_MINUS_INFINITY || rmax == TIMESTAMP_INFINITY)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower and upper bound cannot be infinite"));
+
+ initialize_prng();
+
+ result = (Timestamp) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_TIMESTAMP(result);
+}
+
+/*
+ * timestamptzrandom() -
+ *
+ * Generate random timestamptz chosen uniformly in the specified range.
+ */
+Datum
+timestamptzrandom(PG_FUNCTION_ARGS)
+{
+ int64 rmin = (int64) PG_GETARG_TIMESTAMPTZ(0);
+ int64 rmax = (int64) PG_GETARG_TIMESTAMPTZ(1);
+ TimestampTz result;
+
+ check_range_boundaries(rmin, rmax);
+
+ if (rmin == TIMESTAMP_MINUS_INFINITY || rmax == TIMESTAMP_INFINITY)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("lower and upper bound cannot be infinite"));
+
+ initialize_prng();
+
+ result = (TimestampTz) pg_prng_int64_range(&prng_state, rmin, rmax);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3ee8fed7e53..ce2e1fd6371 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3503,6 +3503,18 @@
proname => 'random', provolatile => 'v', proparallel => 'r',
prorettype => 'numeric', proargtypes => 'numeric numeric',
proargnames => '{min,max}', prosrc => 'numeric_random' },
+{ oid => '6431', descr => 'random date in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'date', proargtypes => 'date date',
+ proargnames => '{min,max}', prosrc => 'daterandom' },
+{ oid => '6432', descr => 'random timestamp in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'timestamp', proargtypes => 'timestamp timestamp',
+ proargnames => '{min,max}', prosrc => 'timestamprandom' },
+{ oid => '6433', descr => 'random timestamptz in range',
+ proname => 'random', provolatile => 'v', proparallel => 'r',
+ prorettype => 'timestamptz', proargtypes => 'timestamptz timestamptz',
+ proargnames => '{min,max}', prosrc => 'timestamptzrandom' },
{ oid => '1599', descr => 'set random seed',
proname => 'setseed', provolatile => 'v', proparallel => 'r',
prorettype => 'void', proargtypes => 'float8', prosrc => 'setseed' },
diff --git a/src/test/regress/expected/random.out b/src/test/regress/expected/random.out
index 43cf88a3634..fab9e471762 100644
--- a/src/test/regress/expected/random.out
+++ b/src/test/regress/expected/random.out
@@ -536,3 +536,90 @@ SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20
20 | 0.60795101234744211935
(41 rows)
+-- random dates
+SELECT random('1979-02-08'::date,'2025-07-03'::date) AS random_date_multiple_years;
+ random_date_multiple_years
+----------------------------
+ 04-09-1986
+(1 row)
+
+SELECT random('4714-11-24 BC'::date,'5874897-12-31 AD'::date) AS random_date_maximum_range;
+ random_date_maximum_range
+---------------------------
+ 10-02-2898131
+(1 row)
+
+SELECT random('1979-02-08'::date,'1979-02-08'::date) AS random_date_empty_range;
+ random_date_empty_range
+-------------------------
+ 02-08-1979
+(1 row)
+
+SELECT random('2024-12-31'::date, '2024-01-01'::date); -- Should error
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('-infinity'::date, '2024-01-01'::date); -- Should error
+ERROR: lower and upper bound cannot be infinite
+SELECT random('2024-12-31'::date, 'infinity'::date); -- Should error
+ERROR: lower and upper bound cannot be infinite
+-- random timestamps
+SELECT random('1979-02-08'::timestamp,'2025-07-03'::timestamp) AS random_timestamp_multiple_years;
+ random_timestamp_multiple_years
+---------------------------------
+ Fri Jan 27 18:52:05.366009 2017
+(1 row)
+
+SELECT random('4714-11-24 BC'::timestamp,'294276-12-31 23:59:59.999999'::timestamp) AS random_timestamp_maximum_range;
+ random_timestamp_maximum_range
+-----------------------------------
+ Wed Mar 28 00:45:36.180395 226694
+(1 row)
+
+SELECT random('2024-07-01 12:00:00.000001'::timestamp, '2024-07-01 12:00:00.999999'::timestamp) AS random_narrow_range;
+ random_narrow_range
+---------------------------------
+ Mon Jul 01 12:00:00.999286 2024
+(1 row)
+
+SELECT random('1979-02-08'::timestamp,'1979-02-08'::timestamp) AS random_timestamp_empty_range;
+ random_timestamp_empty_range
+------------------------------
+ Thu Feb 08 00:00:00 1979
+(1 row)
+
+SELECT random('2024-12-31'::timestamp, '2024-01-01'::timestamp); -- Should error
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('-infinity'::timestamp, '2024-01-01'::timestamp); -- Should error
+ERROR: lower and upper bound cannot be infinite
+SELECT random('2024-12-31'::timestamp, 'infinity'::timestamp); -- Should error
+ERROR: lower and upper bound cannot be infinite
+-- random timestamps with timezone
+SELECT random('1979-02-08 +01'::timestamptz,'2025-07-03 +02'::timestamptz) AS random_timestamptz_multiple_years;
+ random_timestamptz_multiple_years
+-------------------------------------
+ Tue Jun 14 04:41:16.652896 2016 PDT
+(1 row)
+
+SELECT random('4714-11-24 BC +00'::timestamptz,'294276-12-31 23:59:59.999999 +00'::timestamptz) AS random_timestamptz_maximum_range;
+ random_timestamptz_maximum_range
+--------------------------------------
+ Wed Mar 26 14:07:16.980265 31603 PDT
+(1 row)
+
+SELECT random('2024-07-01 12:00:00.000001 +04'::timestamptz, '2024-07-01 12:00:00.999999 +04'::timestamptz) AS random_timestamptz_narrow_range;
+ random_timestamptz_narrow_range
+-------------------------------------
+ Mon Jul 01 01:00:00.835808 2024 PDT
+(1 row)
+
+SELECT random('1979-02-08 +05'::timestamptz,'1979-02-08 +05'::timestamptz) AS random_timestamptz_empty_range;
+ random_timestamptz_empty_range
+--------------------------------
+ Wed Feb 07 11:00:00 1979 PST
+(1 row)
+
+SELECT random('2024-01-01 +06'::timestamptz, '2024-01-01 +07'::timestamptz); -- Should error
+ERROR: lower bound must be less than or equal to upper bound
+SELECT random('-infinity'::timestamptz, '2024-01-01 +07'::timestamptz); -- Should error
+ERROR: lower and upper bound cannot be infinite
+SELECT random('2024-01-01 +06'::timestamptz, 'infinity'::timestamptz); -- Should error
+ERROR: lower and upper bound cannot be infinite
diff --git a/src/test/regress/sql/random.sql b/src/test/regress/sql/random.sql
index ebfa7539ede..e6edce8159b 100644
--- a/src/test/regress/sql/random.sql
+++ b/src/test/regress/sql/random.sql
@@ -277,3 +277,29 @@ SELECT random(-1e30, 1e30) FROM generate_series(1, 10);
SELECT random(-0.4, 0.4) FROM generate_series(1, 10);
SELECT random(0, 1 - 1e-30) FROM generate_series(1, 10);
SELECT n, random(0, trim_scale(abs(1 - 10.0^(-n)))) FROM generate_series(-20, 20) n;
+
+-- random dates
+SELECT random('1979-02-08'::date,'2025-07-03'::date) AS random_date_multiple_years;
+SELECT random('4714-11-24 BC'::date,'5874897-12-31 AD'::date) AS random_date_maximum_range;
+SELECT random('1979-02-08'::date,'1979-02-08'::date) AS random_date_empty_range;
+SELECT random('2024-12-31'::date, '2024-01-01'::date); -- Should error
+SELECT random('-infinity'::date, '2024-01-01'::date); -- Should error
+SELECT random('2024-12-31'::date, 'infinity'::date); -- Should error
+
+-- random timestamps
+SELECT random('1979-02-08'::timestamp,'2025-07-03'::timestamp) AS random_timestamp_multiple_years;
+SELECT random('4714-11-24 BC'::timestamp,'294276-12-31 23:59:59.999999'::timestamp) AS random_timestamp_maximum_range;
+SELECT random('2024-07-01 12:00:00.000001'::timestamp, '2024-07-01 12:00:00.999999'::timestamp) AS random_narrow_range;
+SELECT random('1979-02-08'::timestamp,'1979-02-08'::timestamp) AS random_timestamp_empty_range;
+SELECT random('2024-12-31'::timestamp, '2024-01-01'::timestamp); -- Should error
+SELECT random('-infinity'::timestamp, '2024-01-01'::timestamp); -- Should error
+SELECT random('2024-12-31'::timestamp, 'infinity'::timestamp); -- Should error
+
+-- random timestamps with timezone
+SELECT random('1979-02-08 +01'::timestamptz,'2025-07-03 +02'::timestamptz) AS random_timestamptz_multiple_years;
+SELECT random('4714-11-24 BC +00'::timestamptz,'294276-12-31 23:59:59.999999 +00'::timestamptz) AS random_timestamptz_maximum_range;
+SELECT random('2024-07-01 12:00:00.000001 +04'::timestamptz, '2024-07-01 12:00:00.999999 +04'::timestamptz) AS random_timestamptz_narrow_range;
+SELECT random('1979-02-08 +05'::timestamptz,'1979-02-08 +05'::timestamptz) AS random_timestamptz_empty_range;
+SELECT random('2024-01-01 +06'::timestamptz, '2024-01-01 +07'::timestamptz); -- Should error
+SELECT random('-infinity'::timestamptz, '2024-01-01 +07'::timestamptz); -- Should error
+SELECT random('2024-01-01 +06'::timestamptz, 'infinity'::timestamptz); -- Should error
--
2.39.5
