v4-0006-Add-cross-type-comparisons-for-datetime-types.patch
text/x-patch
Filename: v4-0006-Add-cross-type-comparisons-for-datetime-types.patch
Type: text/x-patch
Part: 7
Patch
Format: format-patch
Series: patch v4-0006
Subject: Add cross-type comparisons for datetime types.
| File | + | − |
|---|---|---|
| contrib/btree_gin/btree_gin--1.3--1.4.sql | 48 | 0 |
| contrib/btree_gin/btree_gin.c | 90 | 9 |
| contrib/btree_gin/expected/date.out | 270 | 0 |
| contrib/btree_gin/expected/timestamp.out | 294 | 6 |
| contrib/btree_gin/expected/timestamptz.out | 105 | 6 |
| contrib/btree_gin/sql/date.sql | 46 | 0 |
| contrib/btree_gin/sql/timestamp.sql | 52 | 2 |
| contrib/btree_gin/sql/timestamptz.sql | 20 | 2 |
From fdaaef42846c01bb4b3252cb1f887ce2895dd8de Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 27 Mar 2025 21:12:27 -0400
Subject: [PATCH v4 6/8] Add cross-type comparisons for datetime types.
Discussion: https://postgr.es/m/262624.1738460652@sss.pgh.pa.us
---
contrib/btree_gin/btree_gin--1.3--1.4.sql | 48 ++++
contrib/btree_gin/btree_gin.c | 99 ++++++-
contrib/btree_gin/expected/date.out | 270 +++++++++++++++++++
contrib/btree_gin/expected/timestamp.out | 300 ++++++++++++++++++++-
contrib/btree_gin/expected/timestamptz.out | 111 +++++++-
contrib/btree_gin/sql/date.sql | 46 ++++
contrib/btree_gin/sql/timestamp.sql | 54 +++-
contrib/btree_gin/sql/timestamptz.sql | 22 +-
8 files changed, 925 insertions(+), 25 deletions(-)
diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
index 13c84ad6671..b8483c3b9ab 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -101,3 +101,51 @@ ADD
OPERATOR 12 >= (name, text),
OPERATOR 13 > (name, text)
;
+
+ALTER OPERATOR FAMILY date_ops USING gin
+ADD
+ -- Code 1: RHS is timestamp
+ OPERATOR 9 < (date, timestamp),
+ OPERATOR 10 <= (date, timestamp),
+ OPERATOR 11 = (date, timestamp),
+ OPERATOR 12 >= (date, timestamp),
+ OPERATOR 13 > (date, timestamp),
+ -- Code 2: RHS is timestamptz
+ OPERATOR 17 < (date, timestamptz),
+ OPERATOR 18 <= (date, timestamptz),
+ OPERATOR 19 = (date, timestamptz),
+ OPERATOR 20 >= (date, timestamptz),
+ OPERATOR 21 > (date, timestamptz)
+;
+
+ALTER OPERATOR FAMILY timestamp_ops USING gin
+ADD
+ -- Code 1: RHS is date
+ OPERATOR 9 < (timestamp, date),
+ OPERATOR 10 <= (timestamp, date),
+ OPERATOR 11 = (timestamp, date),
+ OPERATOR 12 >= (timestamp, date),
+ OPERATOR 13 > (timestamp, date),
+ -- Code 2: RHS is timestamptz
+ OPERATOR 17 < (timestamp, timestamptz),
+ OPERATOR 18 <= (timestamp, timestamptz),
+ OPERATOR 19 = (timestamp, timestamptz),
+ OPERATOR 20 >= (timestamp, timestamptz),
+ OPERATOR 21 > (timestamp, timestamptz)
+;
+
+ALTER OPERATOR FAMILY timestamptz_ops USING gin
+ADD
+ -- Code 1: RHS is date
+ OPERATOR 9 < (timestamptz, date),
+ OPERATOR 10 <= (timestamptz, date),
+ OPERATOR 11 = (timestamptz, date),
+ OPERATOR 12 >= (timestamptz, date),
+ OPERATOR 13 > (timestamptz, date),
+ -- Code 2: RHS is timestamp
+ OPERATOR 17 < (timestamptz, timestamp),
+ OPERATOR 18 <= (timestamptz, timestamp),
+ OPERATOR 19 = (timestamptz, timestamp),
+ OPERATOR 20 >= (timestamptz, timestamp),
+ OPERATOR 21 > (timestamptz, timestamp)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 375e4dc7e67..9bd765c898c 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -483,21 +483,75 @@ leftmostvalue_timestamp(void)
return TimestampGetDatum(DT_NOBEGIN);
}
+static Datum
+cvt_date_timestamp(Datum input)
+{
+ DateADT val = DatumGetDateADT(input);
+ Timestamp result;
+ int overflow;
+
+ result = date2timestamp_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampGetDatum(result);
+}
+
+static Datum
+cvt_timestamptz_timestamp(Datum input)
+{
+ TimestampTz val = DatumGetTimestampTz(input);
+ Timestamp result;
+ int overflow;
+
+ result = timestamptz2timestamp_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampGetDatum(result);
+}
+
static const bool timestamp_rhs_is_varlena[] =
-{false};
+{false, false, false};
+
+static const btree_gin_convert_function timestamp_cvt_fns[] =
+{NULL, cvt_date_timestamp, cvt_timestamptz_timestamp};
static const PGFunction timestamp_cmp_fns[] =
-{timestamp_cmp};
+{timestamp_cmp, date_cmp_timestamp, timestamptz_cmp_timestamp};
+
+GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, timestamp_cvt_fns, timestamp_cmp_fns)
+
+static Datum
+cvt_date_timestamptz(Datum input)
+{
+ DateADT val = DatumGetDateADT(input);
+ TimestampTz result;
+ int overflow;
-GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, NULL, timestamp_cmp_fns)
+ result = date2timestamptz_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampTzGetDatum(result);
+}
+
+static Datum
+cvt_timestamp_timestamptz(Datum input)
+{
+ Timestamp val = DatumGetTimestamp(input);
+ TimestampTz result;
+ int overflow;
+
+ result = timestamp2timestamptz_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return TimestampTzGetDatum(result);
+}
static const bool timestamptz_rhs_is_varlena[] =
-{false};
+{false, false, false};
+
+static const btree_gin_convert_function timestamptz_cvt_fns[] =
+{NULL, cvt_date_timestamptz, cvt_timestamp_timestamptz};
static const PGFunction timestamptz_cmp_fns[] =
-{timestamp_cmp};
+{timestamp_cmp, date_cmp_timestamptz, timestamp_cmp_timestamptz};
-GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, NULL, timestamptz_cmp_fns)
+GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, timestamptz_cvt_fns, timestamptz_cmp_fns)
static Datum
leftmostvalue_time(void)
@@ -538,13 +592,40 @@ leftmostvalue_date(void)
return DateADTGetDatum(DATEVAL_NOBEGIN);
}
+static Datum
+cvt_timestamp_date(Datum input)
+{
+ Timestamp val = DatumGetTimestamp(input);
+ DateADT result;
+ int overflow;
+
+ result = timestamp2date_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return DateADTGetDatum(result);
+}
+
+static Datum
+cvt_timestamptz_date(Datum input)
+{
+ TimestampTz val = DatumGetTimestampTz(input);
+ DateADT result;
+ int overflow;
+
+ result = timestamptz2date_opt_overflow(val, &overflow);
+ /* We can ignore the overflow result, since result is useful as-is */
+ return DateADTGetDatum(result);
+}
+
static const bool date_rhs_is_varlena[] =
-{false};
+{false, false, false};
+
+static const btree_gin_convert_function date_cvt_fns[] =
+{NULL, cvt_timestamp_date, cvt_timestamptz_date};
static const PGFunction date_cmp_fns[] =
-{date_cmp};
+{date_cmp, timestamp_cmp_date, timestamptz_cmp_date};
-GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, NULL, date_cmp_fns)
+GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, date_cvt_fns, date_cmp_fns)
static Datum
leftmostvalue_interval(void)
diff --git a/contrib/btree_gin/expected/date.out b/contrib/btree_gin/expected/date.out
index 40dfa308cf7..d638299b218 100644
--- a/contrib/btree_gin/expected/date.out
+++ b/contrib/btree_gin/expected/date.out
@@ -49,3 +49,273 @@ SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i;
10-28-2004
(2 rows)
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_date
+ Recheck Cond: (i < 'Tue Oct 26 00:00:00 2004'::timestamp without time zone)
+ -> Bitmap Index Scan on idx_date
+ Index Cond: (i < 'Tue Oct 26 00:00:00 2004'::timestamp without time zone)
+(6 rows)
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-26-2004
+(1 row)
+
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamp ORDER BY i;
+ i
+------------
+ 10-27-2004
+ 10-28-2004
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_date
+ Recheck Cond: (i < 'Tue Oct 26 00:00:00 2004 PDT'::timestamp with time zone)
+ -> Bitmap Index Scan on idx_date
+ Index Cond: (i < 'Tue Oct 26 00:00:00 2004 PDT'::timestamp with time zone)
+(6 rows)
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-26-2004
+(1 row)
+
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(3 rows)
+
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamptz ORDER BY i;
+ i
+------------
+ 10-27-2004
+ 10-28-2004
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_date VALUES ('-infinity'), ('infinity');
+SELECT * FROM test_date WHERE i<'-infinity'::timestamp ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i<='-infinity'::timestamp ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i='-infinity'::timestamp ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='-infinity'::timestamp ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i>'-infinity'::timestamp ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamp ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(7 rows)
+
+SELECT * FROM test_date WHERE i<='infinity'::timestamp ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i='infinity'::timestamp ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='infinity'::timestamp ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>'infinity'::timestamp ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i<'-infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i<='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='-infinity'::timestamptz ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i>'-infinity'::timestamptz ORDER BY i;
+ i
+------------
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamptz ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+(7 rows)
+
+SELECT * FROM test_date WHERE i<='infinity'::timestamptz ORDER BY i;
+ i
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_date WHERE i='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
diff --git a/contrib/btree_gin/expected/timestamp.out b/contrib/btree_gin/expected/timestamp.out
index a236cdc94a9..9f823a3c740 100644
--- a/contrib/btree_gin/expected/timestamp.out
+++ b/contrib/btree_gin/expected/timestamp.out
@@ -7,8 +7,8 @@ INSERT INTO test_timestamp VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamp ON test_timestamp USING gin (i);
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
@@ -38,14 +38,302 @@ SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY
i
--------------------------
Tue Oct 26 08:55:08 2004
- Tue Oct 26 09:55:08 2004
- Tue Oct 26 10:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
(3 rows)
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
i
--------------------------
- Tue Oct 26 09:55:08 2004
- Tue Oct 26 10:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
(2 rows)
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+ QUERY PLAN
+----------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamp
+ Recheck Cond: (i < '10-27-2004'::date)
+ -> Bitmap Index Scan on idx_timestamp
+ Index Cond: (i < '10-27-2004'::date)
+(6 rows)
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+(4 rows)
+
+SELECT * FROM test_timestamp WHERE i<='2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+(4 rows)
+
+SELECT * FROM test_timestamp WHERE i='2004-10-27'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i>='2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(2 rows)
+
+SELECT * FROM test_timestamp WHERE i>'2004-10-27'::date ORDER BY i;
+ i
+--------------------------
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamp
+ Recheck Cond: (i < 'Tue Oct 26 08:55:08 2004 PDT'::timestamp with time zone)
+ -> Bitmap Index Scan on idx_timestamp
+ Index Cond: (i < 'Tue Oct 26 08:55:08 2004 PDT'::timestamp with time zone)
+(6 rows)
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+(3 rows)
+
+SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+(4 rows)
+
+SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 08:55:08 2004
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(3 rows)
+
+SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_timestamp VALUES ('-infinity'), ('infinity');
+SELECT * FROM test_timestamp WHERE i<'-infinity'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i<='-infinity'::date ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i='-infinity'::date ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='-infinity'::date ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i>'-infinity'::date ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::date ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<='infinity'::date ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i='infinity'::date ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='infinity'::date ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>'infinity'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i<'-infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamp WHERE i<='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i='-infinity'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='-infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i>'-infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+(7 rows)
+
+SELECT * FROM test_timestamp WHERE i<='infinity'::timestamptz ORDER BY i;
+ i
+--------------------------
+ -infinity
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(8 rows)
+
+SELECT * FROM test_timestamp WHERE i='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>='infinity'::timestamptz ORDER BY i;
+ i
+----------
+ infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>'infinity'::timestamptz ORDER BY i;
+ i
+---
+(0 rows)
+
+-- This PST timestamptz will underflow if converted to timestamp
+SELECT * FROM test_timestamp WHERE i<='4714-11-23 17:00 BC'::timestamptz ORDER BY i;
+ i
+-----------
+ -infinity
+(1 row)
+
+SELECT * FROM test_timestamp WHERE i>'4714-11-23 17:00 BC'::timestamptz ORDER BY i;
+ i
+--------------------------
+ Tue Oct 26 03:55:08 2004
+ Tue Oct 26 04:55:08 2004
+ Tue Oct 26 05:55:08 2004
+ Tue Oct 26 08:55:08 2004
+ Wed Oct 27 09:55:08 2004
+ Wed Oct 27 10:55:08 2004
+ infinity
+(7 rows)
+
diff --git a/contrib/btree_gin/expected/timestamptz.out b/contrib/btree_gin/expected/timestamptz.out
index d53963d2a04..0dada0b662c 100644
--- a/contrib/btree_gin/expected/timestamptz.out
+++ b/contrib/btree_gin/expected/timestamptz.out
@@ -7,8 +7,8 @@ INSERT INTO test_timestamptz VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i);
SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
@@ -38,14 +38,113 @@ SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER
i
------------------------------
Tue Oct 26 08:55:08 2004 PDT
- Tue Oct 26 09:55:08 2004 PDT
- Tue Oct 26 10:55:08 2004 PDT
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
(3 rows)
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
i
------------------------------
- Tue Oct 26 09:55:08 2004 PDT
- Tue Oct 26 10:55:08 2004 PDT
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+ QUERY PLAN
+----------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamptz
+ Recheck Cond: (i < '10-27-2004'::date)
+ -> Bitmap Index Scan on idx_timestamptz
+ Index Cond: (i < '10-27-2004'::date)
+(6 rows)
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+ Tue Oct 26 08:55:08 2004 PDT
+(4 rows)
+
+SELECT * FROM test_timestamptz WHERE i<='2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+ Tue Oct 26 08:55:08 2004 PDT
+(4 rows)
+
+SELECT * FROM test_timestamptz WHERE i='2004-10-27'::date ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_timestamptz WHERE i>='2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(2 rows)
+
+SELECT * FROM test_timestamptz WHERE i>'2004-10-27'::date ORDER BY i;
+ i
+------------------------------
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_timestamptz
+ Recheck Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
+ -> Bitmap Index Scan on idx_timestamptz
+ Index Cond: (i < 'Tue Oct 26 08:55:08 2004'::timestamp without time zone)
+(6 rows)
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+(3 rows)
+
+SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 03:55:08 2004 PDT
+ Tue Oct 26 04:55:08 2004 PDT
+ Tue Oct 26 05:55:08 2004 PDT
+ Tue Oct 26 08:55:08 2004 PDT
+(4 rows)
+
+SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 08:55:08 2004 PDT
+(1 row)
+
+SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Tue Oct 26 08:55:08 2004 PDT
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
+(3 rows)
+
+SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
+ i
+------------------------------
+ Wed Oct 27 09:55:08 2004 PDT
+ Wed Oct 27 10:55:08 2004 PDT
(2 rows)
diff --git a/contrib/btree_gin/sql/date.sql b/contrib/btree_gin/sql/date.sql
index 35086f6b81b..6bd6a08da3b 100644
--- a/contrib/btree_gin/sql/date.sql
+++ b/contrib/btree_gin/sql/date.sql
@@ -20,3 +20,49 @@ SELECT * FROM test_date WHERE i<='2004-10-26'::date ORDER BY i;
SELECT * FROM test_date WHERE i='2004-10-26'::date ORDER BY i;
SELECT * FROM test_date WHERE i>='2004-10-26'::date ORDER BY i;
SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamp ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i<='2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i='2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>='2004-10-26'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>'2004-10-26'::timestamptz ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_date VALUES ('-infinity'), ('infinity');
+
+SELECT * FROM test_date WHERE i<'-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i<='-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i='-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>='-infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>'-infinity'::timestamp ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i<='infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i='infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>='infinity'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i>'infinity'::timestamp ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i<='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>'-infinity'::timestamptz ORDER BY i;
+
+SELECT * FROM test_date WHERE i<'infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i<='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i;
diff --git a/contrib/btree_gin/sql/timestamp.sql b/contrib/btree_gin/sql/timestamp.sql
index 56727e81c4a..9f830bcf69f 100644
--- a/contrib/btree_gin/sql/timestamp.sql
+++ b/contrib/btree_gin/sql/timestamp.sql
@@ -9,8 +9,8 @@ INSERT INTO test_timestamp VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamp ON test_timestamp USING gin (i);
@@ -20,3 +20,53 @@ SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY
SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'2004-10-27'::date ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_timestamp VALUES ('-infinity'), ('infinity');
+
+SELECT * FROM test_timestamp WHERE i<'-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='-infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'-infinity'::date ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='infinity'::date ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'infinity'::date ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='-infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'-infinity'::timestamptz ORDER BY i;
+
+SELECT * FROM test_timestamp WHERE i<'infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i<='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>='infinity'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'infinity'::timestamptz ORDER BY i;
+
+-- This PST timestamptz will underflow if converted to timestamp
+SELECT * FROM test_timestamp WHERE i<='4714-11-23 17:00 BC'::timestamptz ORDER BY i;
+SELECT * FROM test_timestamp WHERE i>'4714-11-23 17:00 BC'::timestamptz ORDER BY i;
diff --git a/contrib/btree_gin/sql/timestamptz.sql b/contrib/btree_gin/sql/timestamptz.sql
index e6cfdb1b074..40d2d7ed329 100644
--- a/contrib/btree_gin/sql/timestamptz.sql
+++ b/contrib/btree_gin/sql/timestamptz.sql
@@ -9,8 +9,8 @@ INSERT INTO test_timestamptz VALUES
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
- ( '2004-10-26 09:55:08' ),
- ( '2004-10-26 10:55:08' )
+ ( '2004-10-27 09:55:08' ),
+ ( '2004-10-27 10:55:08' )
;
CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i);
@@ -20,3 +20,21 @@ SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER
SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i;
SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i;
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i<='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>='2004-10-27'::date ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>'2004-10-27'::date ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+
+SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
+SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
--
2.43.0