Cross-type index comparison support in contrib/btree_gin - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Cross-type index comparison support in contrib/btree_gin |
| Date | |
| Msg-id | 262624.1738460652@sss.pgh.pa.us Whole thread Raw |
| List | pgsql-hackers |
We've had multiple requests for $SUBJECT over the years
([1][2][3][4][5], and I'm sure my archive search missed some).
I finally decided to look into what it'd take to make that happen.
It's not as bad as I feared, and attached is a draft patch.
The thing that makes this sticky is that GIN itself doesn't support
any such thing as cross-type comparisons: all the Datums that it deals
with directly have to be of the same type as the stored index keys.
However, for the cases that btree_gin deals with, we can make use of
the "partial match" feature because all the entries we need to find
will be consecutive in the index. And it turns out that the
comparePartial() method is only ever applied to compare the original
query value with an index entry, which means that internally to
comparePartial() we can apply the proper cross-type comparison
operator. Our GIN index documentation about comparePartial() doesn't
quite say that in so many words, but btree_gin was already relying on
it --- in a very confusing and ill-explained way, if you ask me, but
it was relying on it. (The 0001 patch below is mainly concerned with
making that reliance simpler and clearer.)
The other thing that has to be dealt with is that cross-type or not,
we need to somehow create a Datum of the index key type to perform
the initial index descent with. But I realized that this isn't
that tough after all. Aside from boring change-of-representation
work, there are these special cases:
* Query value is out of range for the index type. We can simply
clamp it to the index type's range, so that GIN descends to one
end of the index or the other and then searches normally. GIN
might falsely think that the endmost entry(s) of the index equal
the search datum, but it doesn't matter too much what GIN thinks
because comparePartial can filter away the false matches by
applying the correct comparison with the original query value.
* Query value falls between possible values of the index type
(possible in float8->float4 or timestamp->date cases, for example).
We can just use our usual conversion rules, though. The critical
observation here is that it does not matter whether the conversion
rounds to the next lower or next higher possible value. If we are
searching for equality, neither of those values will pass the
cross-type comparison so it doesn't matter. If we are searching for
inequality, for example "indcol <= value", then only index entries
strictly less than the query value can match. Rounding down clearly
doesn't hurt, while rounding up at worst makes the search include
some index entries just larger than the query value, which will be
correctly rejected by the cross-type comparison.
So basically all I had to do was write a bunch of non-error-throwing
conversion routines and set up some boilerplate infrastructure.
Patch series attached --- it's rather long, but a lot of it is
new test cases.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/58782480-ab75-4416-a177-ccf91be288a9%40app.fastmail.com
[2] https://www.postgresql.org/message-id/flat/17079-c5edf57c47debc2c%40postgresql.org
[3] https://www.postgresql.org/message-id/flat/20170207150420.1409.58748%40wrigleys.postgresql.org
[4] https://www.postgresql.org/message-id/flat/20160415185902.22924.77993%40wrigleys.postgresql.org
[5] https://www.postgresql.org/message-id/flat/VisenaEmail.42.91df4628bdf7755c.1537e96e852%40tc7-visena
From b6c502a8ed215b51dd26179194d9ddb6885f6e13 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 1 Feb 2025 17:59:54 -0500
Subject: [PATCH v1 1/5] Preliminary refactoring.
This step doesn't change any behavior. It cleans the code up
slightly and documents it better. In particular, the trick
being used by gin_btree_compare_prefix is better explained (IMO)
and there's now an Assert backing up the assumption it has to make.
---
contrib/btree_gin/btree_gin.c | 85 +++++++++++++++++++-------------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 53 insertions(+), 33 deletions(-)
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 533c55e9ea..d364e72226 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -16,14 +16,18 @@
PG_MODULE_MAGIC;
+/* extra data passed from gin_btree_extract_query to gin_btree_compare_prefix */
typedef struct QueryInfo
{
- StrategyNumber strategy;
- Datum datum;
- bool is_varlena;
- Datum (*typecmp) (FunctionCallInfo);
+ StrategyNumber strategy; /* operator strategy number */
+ Datum orig_datum; /* original query (comparison) datum */
+ Datum entry_datum; /* datum we reported as the entry value */
+ PGFunction typecmp; /* appropriate btree comparison function */
} QueryInfo;
+typedef Datum (*btree_gin_leftmost_function) (void);
+
+
/*** GIN support functions shared by all datatypes ***/
static Datum
@@ -33,6 +37,7 @@ gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena)
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
Datum *entries = (Datum *) palloc(sizeof(Datum));
+ /* Ensure that values stored in the index are not toasted */
if (is_varlena)
datum = PointerGetDatum(PG_DETOAST_DATUM(datum));
entries[0] = datum;
@@ -41,19 +46,11 @@ gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena)
PG_RETURN_POINTER(entries);
}
-/*
- * For BTGreaterEqualStrategyNumber, BTGreaterStrategyNumber, and
- * BTEqualStrategyNumber we want to start the index scan at the
- * supplied query datum, and work forward. For BTLessStrategyNumber
- * and BTLessEqualStrategyNumber, we need to start at the leftmost
- * key, and work forward until the supplied query datum (which must be
- * sent along inside the QueryInfo structure).
- */
static Datum
gin_btree_extract_query(FunctionCallInfo fcinfo,
bool is_varlena,
- Datum (*leftmostvalue) (void),
- Datum (*typecmp) (FunctionCallInfo))
+ btree_gin_leftmost_function leftmostvalue,
+ PGFunction typecmp)
{
Datum datum = PG_GETARG_DATUM(0);
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
@@ -62,20 +59,29 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
Pointer **extra_data = (Pointer **) PG_GETARG_POINTER(4);
Datum *entries = (Datum *) palloc(sizeof(Datum));
QueryInfo *data = (QueryInfo *) palloc(sizeof(QueryInfo));
- bool *ptr_partialmatch;
+ bool *ptr_partialmatch = (bool *) palloc(sizeof(bool));
- *nentries = 1;
- ptr_partialmatch = *partialmatch = (bool *) palloc(sizeof(bool));
- *ptr_partialmatch = false;
+ /*
+ * Detoast the comparison datum. This isn't necessary for correctness,
+ * but it can save repeat detoastings within the comparison function.
+ */
if (is_varlena)
datum = PointerGetDatum(PG_DETOAST_DATUM(datum));
- data->strategy = strategy;
- data->datum = datum;
- data->is_varlena = is_varlena;
- data->typecmp = typecmp;
- *extra_data = (Pointer *) palloc(sizeof(Pointer));
- **extra_data = (Pointer) data;
+ /* Prep single comparison key with possible partial-match flag */
+ *nentries = 1;
+ *partialmatch = ptr_partialmatch;
+ *ptr_partialmatch = false;
+
+ /*
+ * For BTGreaterEqualStrategyNumber, BTGreaterStrategyNumber, and
+ * BTEqualStrategyNumber we want to start the index scan at the supplied
+ * query datum, and work forward. For BTLessStrategyNumber and
+ * BTLessEqualStrategyNumber, we need to start at the leftmost key, and
+ * work forward until the supplied query datum (which we'll send along
+ * inside the QueryInfo structure). Use partial match rules except for
+ * BTEqualStrategyNumber.
+ */
switch (strategy)
{
case BTLessStrategyNumber:
@@ -94,14 +100,17 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
elog(ERROR, "unrecognized strategy number: %d", strategy);
}
+ /* Fill "extra" data */
+ data->strategy = strategy;
+ data->orig_datum = datum;
+ data->entry_datum = entries[0];
+ data->typecmp = typecmp;
+ *extra_data = (Pointer *) palloc(sizeof(Pointer));
+ **extra_data = (Pointer) data;
+
PG_RETURN_POINTER(entries);
}
-/*
- * Datum a is a value from extract_query method and for BTLess*
- * strategy it is a left-most value. So, use original datum from QueryInfo
- * to decide to stop scanning or not. Datum b is always from index.
- */
static Datum
gin_btree_compare_prefix(FunctionCallInfo fcinfo)
{
@@ -111,12 +120,22 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
int32 res,
cmp;
+ /*
+ * The core GIN code only calls this function to compare the original
+ * comparison datum ("a") to an index entry ("b"). However, what it
+ * thinks is the original comparison datum is the entry value reported by
+ * gin_btree_extract_query, which might be different. To get the right
+ * answers we must compare to the original datum from the query. But
+ * first, assert that "a" is indeed what gin_btree_extract_query reported,
+ * so that we'll notice if anyone ever changes the core code in a way that
+ * breaks this assumption.
+ */
+ Assert(a == data->entry_datum);
+
cmp = DatumGetInt32(CallerFInfoFunctionCall2(data->typecmp,
fcinfo->flinfo,
PG_GET_COLLATION(),
- (data->strategy == BTLessStrategyNumber ||
- data->strategy == BTLessEqualStrategyNumber)
- ? data->datum : a,
+ data->orig_datum,
b));
switch (data->strategy)
@@ -149,7 +168,7 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
res = 1;
break;
case BTGreaterStrategyNumber:
- /* If original datum <= indexed one then return match */
+ /* If original datum < indexed one then return match */
/* If original datum == indexed one then continue scan */
if (cmp < 0)
res = 0;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a2644a2e65..cb9388f61a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3377,6 +3377,7 @@ bloom_filter
boolKEY
brin_column_state
brin_serialize_callback_type
+btree_gin_leftmost_function
bytea
cached_re_str
canonicalize_state
--
2.43.5
From fbf3b6665edbc402e95562b7d39b987fd1d525f0 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 1 Feb 2025 18:25:51 -0500
Subject: [PATCH v1 2/5] Add cross-type comparisons for integer types.
Extend the infrastructure in btree_gin.c to permit cross-type
operators, and add the code to support them for the int2, int4,
and int8 opclasses. (To keep this patch digestible, I left
the other datatypes for later.)
---
contrib/btree_gin/Makefile | 2 +-
contrib/btree_gin/btree_gin--1.3--1.4.sql | 63 ++++
contrib/btree_gin/btree_gin.c | 388 +++++++++++++++++++---
contrib/btree_gin/btree_gin.control | 2 +-
contrib/btree_gin/expected/int2.out | 184 ++++++++++
contrib/btree_gin/expected/int4.out | 100 ++++++
contrib/btree_gin/expected/int8.out | 100 ++++++
contrib/btree_gin/meson.build | 1 +
contrib/btree_gin/sql/int2.sql | 34 ++
contrib/btree_gin/sql/int4.sql | 18 +
contrib/btree_gin/sql/int8.sql | 18 +
src/tools/pgindent/typedefs.list | 1 +
12 files changed, 860 insertions(+), 51 deletions(-)
create mode 100644 contrib/btree_gin/btree_gin--1.3--1.4.sql
diff --git a/contrib/btree_gin/Makefile b/contrib/btree_gin/Makefile
index 0a15811516..ad054598db 100644
--- a/contrib/btree_gin/Makefile
+++ b/contrib/btree_gin/Makefile
@@ -7,7 +7,7 @@ OBJS = \
EXTENSION = btree_gin
DATA = btree_gin--1.0.sql btree_gin--1.0--1.1.sql btree_gin--1.1--1.2.sql \
- btree_gin--1.2--1.3.sql
+ btree_gin--1.2--1.3.sql btree_gin--1.3--1.4.sql
PGFILEDESC = "btree_gin - B-tree equivalent GIN operator classes"
REGRESS = install_btree_gin int2 int4 int8 float4 float8 money oid \
diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
new file mode 100644
index 0000000000..71e577de2d
--- /dev/null
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -0,0 +1,63 @@
+/* contrib/btree_gin/btree_gin--1.3--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "ALTER EXTENSION btree_gin UPDATE TO '1.4'" to load this file. \quit
+
+--
+-- Cross-type operator support is new in 1.4. We only need to worry
+-- about this for cross-type operators that exist in core.
+--
+-- Because the opclass extractQuery and consistent methods don't directly
+-- get any information about the datatype of the RHS value, we have to
+-- encode that in the operator strategy numbers. The strategy numbers
+-- are the operator's normal btree strategy (1-5) plus 8 times a code
+-- for the RHS datatype.
+--
+
+ALTER OPERATOR FAMILY int2_ops USING gin
+ADD
+ -- Code 1: RHS is int4
+ OPERATOR 9 < (int2, int4),
+ OPERATOR 10 <= (int2, int4),
+ OPERATOR 11 = (int2, int4),
+ OPERATOR 12 >= (int2, int4),
+ OPERATOR 13 > (int2, int4),
+ -- Code 2: RHS is int8
+ OPERATOR 17 < (int2, int8),
+ OPERATOR 18 <= (int2, int8),
+ OPERATOR 19 = (int2, int8),
+ OPERATOR 20 >= (int2, int8),
+ OPERATOR 21 > (int2, int8)
+;
+
+ALTER OPERATOR FAMILY int4_ops USING gin
+ADD
+ -- Code 1: RHS is int2
+ OPERATOR 9 < (int4, int2),
+ OPERATOR 10 <= (int4, int2),
+ OPERATOR 11 = (int4, int2),
+ OPERATOR 12 >= (int4, int2),
+ OPERATOR 13 > (int4, int2),
+ -- Code 2: RHS is int8
+ OPERATOR 17 < (int4, int8),
+ OPERATOR 18 <= (int4, int8),
+ OPERATOR 19 = (int4, int8),
+ OPERATOR 20 >= (int4, int8),
+ OPERATOR 21 > (int4, int8)
+;
+
+ALTER OPERATOR FAMILY int8_ops USING gin
+ADD
+ -- Code 1: RHS is int2
+ OPERATOR 9 < (int8, int2),
+ OPERATOR 10 <= (int8, int2),
+ OPERATOR 11 = (int8, int2),
+ OPERATOR 12 >= (int8, int2),
+ OPERATOR 13 > (int8, int2),
+ -- Code 2: RHS is int4
+ OPERATOR 17 < (int8, int4),
+ OPERATOR 18 <= (int8, int4),
+ OPERATOR 19 = (int8, int4),
+ OPERATOR 20 >= (int8, int4),
+ OPERATOR 21 > (int8, int4)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index d364e72226..497c8c1946 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -25,6 +25,8 @@ typedef struct QueryInfo
PGFunction typecmp; /* appropriate btree comparison function */
} QueryInfo;
+typedef Datum (*btree_gin_convert_function) (Datum input);
+
typedef Datum (*btree_gin_leftmost_function) (void);
@@ -48,9 +50,10 @@ gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena)
static Datum
gin_btree_extract_query(FunctionCallInfo fcinfo,
- bool is_varlena,
btree_gin_leftmost_function leftmostvalue,
- PGFunction typecmp)
+ const bool *rhs_is_varlena,
+ const btree_gin_convert_function *cvt_fns,
+ const PGFunction *cmp_fns)
{
Datum datum = PG_GETARG_DATUM(0);
int32 *nentries = (int32 *) PG_GETARG_POINTER(1);
@@ -60,12 +63,23 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
Datum *entries = (Datum *) palloc(sizeof(Datum));
QueryInfo *data = (QueryInfo *) palloc(sizeof(QueryInfo));
bool *ptr_partialmatch = (bool *) palloc(sizeof(bool));
+ int btree_strat,
+ rhs_code;
+
+ /*
+ * "strategy & 7" extracts the basic btree strategy code from the given
+ * strategy number, while "strategy >> 3" extracts the code identifying
+ * the RHS data type, which we use as an index into the given function
+ * pointer arrays.
+ */
+ btree_strat = strategy & 7;
+ rhs_code = strategy >> 3;
/*
* Detoast the comparison datum. This isn't necessary for correctness,
* but it can save repeat detoastings within the comparison function.
*/
- if (is_varlena)
+ if (rhs_is_varlena[rhs_code])
datum = PointerGetDatum(PG_DETOAST_DATUM(datum));
/* Prep single comparison key with possible partial-match flag */
@@ -80,9 +94,10 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
* BTLessEqualStrategyNumber, we need to start at the leftmost key, and
* work forward until the supplied query datum (which we'll send along
* inside the QueryInfo structure). Use partial match rules except for
- * BTEqualStrategyNumber.
+ * BTEqualStrategyNumber without a conversion function. (If there is a
+ * conversion function, comparison to the entry value is not trustworthy.)
*/
- switch (strategy)
+ switch (btree_strat)
{
case BTLessStrategyNumber:
case BTLessEqualStrategyNumber:
@@ -94,7 +109,14 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
*ptr_partialmatch = true;
/* FALLTHROUGH */
case BTEqualStrategyNumber:
- entries[0] = datum;
+ /* If we have a conversion function, apply it */
+ if (cvt_fns && cvt_fns[rhs_code])
+ {
+ entries[0] = (*cvt_fns[rhs_code]) (datum);
+ *ptr_partialmatch = true;
+ }
+ else
+ entries[0] = datum;
break;
default:
elog(ERROR, "unrecognized strategy number: %d", strategy);
@@ -104,7 +126,7 @@ gin_btree_extract_query(FunctionCallInfo fcinfo,
data->strategy = strategy;
data->orig_datum = datum;
data->entry_datum = entries[0];
- data->typecmp = typecmp;
+ data->typecmp = cmp_fns[rhs_code];
*extra_data = (Pointer *) palloc(sizeof(Pointer));
**extra_data = (Pointer) data;
@@ -129,6 +151,10 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
* first, assert that "a" is indeed what gin_btree_extract_query reported,
* so that we'll notice if anyone ever changes the core code in a way that
* breaks this assumption.
+ *
+ * Note that "a" and "b" are of the indexed datatype while orig_datum is
+ * of the query operator's RHS datatype. So this trick is essential in
+ * order to be able to use the correct cross-type comparison function.
*/
Assert(a == data->entry_datum);
@@ -138,44 +164,52 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo)
data->orig_datum,
b));
- switch (data->strategy)
+ /*
+ * Convert the comparison result to the correct thing for the search
+ * operator strategy. When dealing with cross-type comparisons, an
+ * imprecise entry datum could lead GIN to start the scan just before the
+ * first possible match, so we must continue the scan if the current index
+ * entry doesn't satisfy the search condition for =, >= and > cases.
+ *
+ * As above, we want just the low 3 bits of the strategy code here.
+ */
+ switch (data->strategy & 7)
{
case BTLessStrategyNumber:
/* If original datum > indexed one then return match */
if (cmp > 0)
res = 0;
else
- res = 1;
+ res = 1; /* end scan */
break;
case BTLessEqualStrategyNumber:
/* The same except equality */
if (cmp >= 0)
res = 0;
else
- res = 1;
+ res = 1; /* end scan */
break;
case BTEqualStrategyNumber:
- if (cmp != 0)
- res = 1;
- else
+ if (cmp > 0)
+ res = -1; /* keep scanning */
+ else if (cmp == 0)
res = 0;
+ else
+ res = 1; /* end scan */
break;
case BTGreaterEqualStrategyNumber:
/* If original datum <= indexed one then return match */
if (cmp <= 0)
res = 0;
else
- res = 1;
+ res = -1; /* keep scanning */
break;
case BTGreaterStrategyNumber:
/* If original datum < indexed one then return match */
- /* If original datum == indexed one then continue scan */
if (cmp < 0)
res = 0;
- else if (cmp == 0)
- res = -1;
else
- res = 1;
+ res = -1; /* keep scanning */
break;
default:
elog(ERROR, "unrecognized strategy number: %d",
@@ -198,19 +232,20 @@ gin_btree_consistent(PG_FUNCTION_ARGS)
/*** GIN_SUPPORT macro defines the datatype specific functions ***/
-#define GIN_SUPPORT(type, is_varlena, leftmostvalue, typecmp) \
+#define GIN_SUPPORT(type, leftmostvalue, is_varlena, cvtfns, cmpfns) \
PG_FUNCTION_INFO_V1(gin_extract_value_##type); \
Datum \
gin_extract_value_##type(PG_FUNCTION_ARGS) \
{ \
- return gin_btree_extract_value(fcinfo, is_varlena); \
+ return gin_btree_extract_value(fcinfo, is_varlena[0]); \
} \
PG_FUNCTION_INFO_V1(gin_extract_query_##type); \
Datum \
gin_extract_query_##type(PG_FUNCTION_ARGS) \
{ \
return gin_btree_extract_query(fcinfo, \
- is_varlena, leftmostvalue, typecmp); \
+ leftmostvalue, is_varlena, \
+ cvtfns, cmpfns); \
} \
PG_FUNCTION_INFO_V1(gin_compare_prefix_##type); \
Datum \
@@ -222,13 +257,66 @@ gin_compare_prefix_##type(PG_FUNCTION_ARGS) \
/*** Datatype specifications ***/
+/* Function to produce the least possible value of the indexed datatype */
static Datum
leftmostvalue_int2(void)
{
return Int16GetDatum(SHRT_MIN);
}
-GIN_SUPPORT(int2, false, leftmostvalue_int2, btint2cmp)
+/*
+ * For cross-type support, we must provide conversion functions that produce
+ * a Datum of the indexed datatype, since GIN requires the "entry" datums to
+ * be of that type. If an exact conversion is not possible, produce a value
+ * that will lead GIN to find the first index entry that is greater than
+ * or equal to the actual comparison value. (But rounding down is OK, so
+ * sometimes we might find an index entry that's just less than the
+ * comparison value.)
+ *
+ * For integer values, it's sufficient to clamp the input to be in-range.
+ *
+ * Note: for out-of-range input values, we could in theory detect that the
+ * search condition matches all or none of the index, and avoid a useless
+ * index descent in the latter case. Such searches are probably rare though,
+ * so we don't contort this code enough to do that.
+ */
+static Datum
+cvt_int4_int2(Datum input)
+{
+ int32 val = DatumGetInt32(input);
+
+ val = Max(val, SHRT_MIN);
+ val = Min(val, SHRT_MAX);
+ return Int16GetDatum((int16) val);
+}
+
+static Datum
+cvt_int8_int2(Datum input)
+{
+ int64 val = DatumGetInt64(input);
+
+ val = Max(val, SHRT_MIN);
+ val = Min(val, SHRT_MAX);
+ return Int16GetDatum((int16) val);
+}
+
+/*
+ * RHS-type-is-varlena flags, conversion and comparison function arrays,
+ * indexed by high bits of the operator strategy number. A NULL in the
+ * conversion function array indicates that no conversion is needed, which
+ * will always be the case for the zero'th entry. Note that the cross-type
+ * comparison functions should be the ones with the indexed datatype second.
+ */
+static const bool int2_rhs_is_varlena[] =
+{false, false, false};
+
+static const btree_gin_convert_function int2_cvt_fns[] =
+{NULL, cvt_int4_int2, cvt_int8_int2};
+
+static const PGFunction int2_cmp_fns[] =
+{btint2cmp, btint42cmp, btint82cmp};
+
+GIN_SUPPORT(int2, leftmostvalue_int2, int2_rhs_is_varlena, int2_cvt_fns, int2_cmp_fns)
static Datum
leftmostvalue_int4(void)
@@ -236,7 +324,34 @@ leftmostvalue_int4(void)
return Int32GetDatum(INT_MIN);
}
-GIN_SUPPORT(int4, false, leftmostvalue_int4, btint4cmp)
+static Datum
+cvt_int2_int4(Datum input)
+{
+ int16 val = DatumGetInt16(input);
+
+ return Int32GetDatum((int32) val);
+}
+
+static Datum
+cvt_int8_int4(Datum input)
+{
+ int64 val = DatumGetInt64(input);
+
+ val = Max(val, INT_MIN);
+ val = Min(val, INT_MAX);
+ return Int32GetDatum((int32) val);
+}
+
+static const bool int4_rhs_is_varlena[] =
+{false, false, false};
+
+static const btree_gin_convert_function int4_cvt_fns[] =
+{NULL, cvt_int2_int4, cvt_int8_int4};
+
+static const PGFunction int4_cmp_fns[] =
+{btint4cmp, btint24cmp, btint84cmp};
+
+GIN_SUPPORT(int4, leftmostvalue_int4, int4_rhs_is_varlena, int4_cvt_fns, int4_cmp_fns)
static Datum
leftmostvalue_int8(void)
@@ -244,7 +359,32 @@ leftmostvalue_int8(void)
return Int64GetDatum(PG_INT64_MIN);
}
-GIN_SUPPORT(int8, false, leftmostvalue_int8, btint8cmp)
+static Datum
+cvt_int2_int8(Datum input)
+{
+ int16 val = DatumGetInt16(input);
+
+ return Int64GetDatum((int64) val);
+}
+
+static Datum
+cvt_int4_int8(Datum input)
+{
+ int32 val = DatumGetInt32(input);
+
+ return Int64GetDatum((int64) val);
+}
+
+static const bool int8_rhs_is_varlena[] =
+{false, false, false};
+
+static const btree_gin_convert_function int8_cvt_fns[] =
+{NULL, cvt_int2_int8, cvt_int4_int8};
+
+static const PGFunction int8_cmp_fns[] =
+{btint8cmp, btint28cmp, btint48cmp};
+
+GIN_SUPPORT(int8, leftmostvalue_int8, int8_rhs_is_varlena, int8_cvt_fns, int8_cmp_fns)
static Datum
leftmostvalue_float4(void)
@@ -252,7 +392,13 @@ leftmostvalue_float4(void)
return Float4GetDatum(-get_float4_infinity());
}
-GIN_SUPPORT(float4, false, leftmostvalue_float4, btfloat4cmp)
+static const bool float4_rhs_is_varlena[] =
+{false};
+
+static const PGFunction float4_cmp_fns[] =
+{btfloat4cmp};
+
+GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, NULL, float4_cmp_fns)
static Datum
leftmostvalue_float8(void)
@@ -260,7 +406,13 @@ leftmostvalue_float8(void)
return Float8GetDatum(-get_float8_infinity());
}
-GIN_SUPPORT(float8, false, leftmostvalue_float8, btfloat8cmp)
+static const bool float8_rhs_is_varlena[] =
+{false};
+
+static const PGFunction float8_cmp_fns[] =
+{btfloat8cmp};
+
+GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, NULL, float8_cmp_fns)
static Datum
leftmostvalue_money(void)
@@ -268,7 +420,13 @@ leftmostvalue_money(void)
return Int64GetDatum(PG_INT64_MIN);
}
-GIN_SUPPORT(money, false, leftmostvalue_money, cash_cmp)
+static const bool money_rhs_is_varlena[] =
+{false};
+
+static const PGFunction money_cmp_fns[] =
+{cash_cmp};
+
+GIN_SUPPORT(money, leftmostvalue_money, money_rhs_is_varlena, NULL, money_cmp_fns)
static Datum
leftmostvalue_oid(void)
@@ -276,7 +434,13 @@ leftmostvalue_oid(void)
return ObjectIdGetDatum(0);
}
-GIN_SUPPORT(oid, false, leftmostvalue_oid, btoidcmp)
+static const bool oid_rhs_is_varlena[] =
+{false};
+
+static const PGFunction oid_cmp_fns[] =
+{btoidcmp};
+
+GIN_SUPPORT(oid, leftmostvalue_oid, oid_rhs_is_varlena, NULL, oid_cmp_fns)
static Datum
leftmostvalue_timestamp(void)
@@ -284,9 +448,21 @@ leftmostvalue_timestamp(void)
return TimestampGetDatum(DT_NOBEGIN);
}
-GIN_SUPPORT(timestamp, false, leftmostvalue_timestamp, timestamp_cmp)
+static const bool timestamp_rhs_is_varlena[] =
+{false};
+
+static const PGFunction timestamp_cmp_fns[] =
+{timestamp_cmp};
+
+GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, NULL, timestamp_cmp_fns)
-GIN_SUPPORT(timestamptz, false, leftmostvalue_timestamp, timestamp_cmp)
+static const bool timestamptz_rhs_is_varlena[] =
+{false};
+
+static const PGFunction timestamptz_cmp_fns[] =
+{timestamp_cmp};
+
+GIN_SUPPORT(timestamptz, leftmostvalue_timestamp, timestamptz_rhs_is_varlena, NULL, timestamptz_cmp_fns)
static Datum
leftmostvalue_time(void)
@@ -294,7 +470,13 @@ leftmostvalue_time(void)
return TimeADTGetDatum(0);
}
-GIN_SUPPORT(time, false, leftmostvalue_time, time_cmp)
+static const bool time_rhs_is_varlena[] =
+{false};
+
+static const PGFunction time_cmp_fns[] =
+{time_cmp};
+
+GIN_SUPPORT(time, leftmostvalue_time, time_rhs_is_varlena, NULL, time_cmp_fns)
static Datum
leftmostvalue_timetz(void)
@@ -307,7 +489,13 @@ leftmostvalue_timetz(void)
return TimeTzADTPGetDatum(v);
}
-GIN_SUPPORT(timetz, false, leftmostvalue_timetz, timetz_cmp)
+static const bool timetz_rhs_is_varlena[] =
+{false};
+
+static const PGFunction timetz_cmp_fns[] =
+{timetz_cmp};
+
+GIN_SUPPORT(timetz, leftmostvalue_timetz, timetz_rhs_is_varlena, NULL, timetz_cmp_fns)
static Datum
leftmostvalue_date(void)
@@ -315,7 +503,13 @@ leftmostvalue_date(void)
return DateADTGetDatum(DATEVAL_NOBEGIN);
}
-GIN_SUPPORT(date, false, leftmostvalue_date, date_cmp)
+static const bool date_rhs_is_varlena[] =
+{false};
+
+static const PGFunction date_cmp_fns[] =
+{date_cmp};
+
+GIN_SUPPORT(date, leftmostvalue_date, date_rhs_is_varlena, NULL, date_cmp_fns)
static Datum
leftmostvalue_interval(void)
@@ -327,7 +521,13 @@ leftmostvalue_interval(void)
return IntervalPGetDatum(v);
}
-GIN_SUPPORT(interval, false, leftmostvalue_interval, interval_cmp)
+static const bool interval_rhs_is_varlena[] =
+{false};
+
+static const PGFunction interval_cmp_fns[] =
+{interval_cmp};
+
+GIN_SUPPORT(interval, leftmostvalue_interval, interval_rhs_is_varlena, NULL, interval_cmp_fns)
static Datum
leftmostvalue_macaddr(void)
@@ -337,7 +537,13 @@ leftmostvalue_macaddr(void)
return MacaddrPGetDatum(v);
}
-GIN_SUPPORT(macaddr, false, leftmostvalue_macaddr, macaddr_cmp)
+static const bool macaddr_rhs_is_varlena[] =
+{false};
+
+static const PGFunction macaddr_cmp_fns[] =
+{macaddr_cmp};
+
+GIN_SUPPORT(macaddr, leftmostvalue_macaddr, macaddr_rhs_is_varlena, NULL, macaddr_cmp_fns)
static Datum
leftmostvalue_macaddr8(void)
@@ -347,7 +553,13 @@ leftmostvalue_macaddr8(void)
return Macaddr8PGetDatum(v);
}
-GIN_SUPPORT(macaddr8, false, leftmostvalue_macaddr8, macaddr8_cmp)
+static const bool macaddr8_rhs_is_varlena[] =
+{false};
+
+static const PGFunction macaddr8_cmp_fns[] =
+{macaddr8_cmp};
+
+GIN_SUPPORT(macaddr8, leftmostvalue_macaddr8, macaddr8_rhs_is_varlena, NULL, macaddr8_cmp_fns)
static Datum
leftmostvalue_inet(void)
@@ -355,9 +567,21 @@ leftmostvalue_inet(void)
return DirectFunctionCall1(inet_in, CStringGetDatum("0.0.0.0/0"));
}
-GIN_SUPPORT(inet, true, leftmostvalue_inet, network_cmp)
+static const bool inet_rhs_is_varlena[] =
+{true};
+
+static const PGFunction inet_cmp_fns[] =
+{network_cmp};
+
+GIN_SUPPORT(inet, leftmostvalue_inet, inet_rhs_is_varlena, NULL, inet_cmp_fns)
+
+static const bool cidr_rhs_is_varlena[] =
+{true};
-GIN_SUPPORT(cidr, true, leftmostvalue_inet, network_cmp)
+static const PGFunction cidr_cmp_fns[] =
+{network_cmp};
+
+GIN_SUPPORT(cidr, leftmostvalue_inet, cidr_rhs_is_varlena, NULL, cidr_cmp_fns)
static Datum
leftmostvalue_text(void)
@@ -365,9 +589,21 @@ leftmostvalue_text(void)
return PointerGetDatum(cstring_to_text_with_len("", 0));
}
-GIN_SUPPORT(text, true, leftmostvalue_text, bttextcmp)
+static const bool text_rhs_is_varlena[] =
+{true};
+
+static const PGFunction text_cmp_fns[] =
+{bttextcmp};
+
+GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, NULL, text_cmp_fns)
-GIN_SUPPORT(bpchar, true, leftmostvalue_text, bpcharcmp)
+static const bool bpchar_rhs_is_varlena[] =
+{true};
+
+static const PGFunction bpchar_cmp_fns[] =
+{bpcharcmp};
+
+GIN_SUPPORT(bpchar, leftmostvalue_text, bpchar_rhs_is_varlena, NULL, bpchar_cmp_fns)
static Datum
leftmostvalue_char(void)
@@ -375,9 +611,21 @@ leftmostvalue_char(void)
return CharGetDatum(0);
}
-GIN_SUPPORT(char, false, leftmostvalue_char, btcharcmp)
+static const bool char_rhs_is_varlena[] =
+{false};
+
+static const PGFunction char_cmp_fns[] =
+{btcharcmp};
+
+GIN_SUPPORT(char, leftmostvalue_char, char_rhs_is_varlena, NULL, char_cmp_fns)
-GIN_SUPPORT(bytea, true, leftmostvalue_text, byteacmp)
+static const bool bytea_rhs_is_varlena[] =
+{true};
+
+static const PGFunction bytea_cmp_fns[] =
+{byteacmp};
+
+GIN_SUPPORT(bytea, leftmostvalue_text, bytea_rhs_is_varlena, NULL, bytea_cmp_fns)
static Datum
leftmostvalue_bit(void)
@@ -388,7 +636,13 @@ leftmostvalue_bit(void)
Int32GetDatum(-1));
}
-GIN_SUPPORT(bit, true, leftmostvalue_bit, bitcmp)
+static const bool bit_rhs_is_varlena[] =
+{true};
+
+static const PGFunction bit_cmp_fns[] =
+{bitcmp};
+
+GIN_SUPPORT(bit, leftmostvalue_bit, bit_rhs_is_varlena, NULL, bit_cmp_fns)
static Datum
leftmostvalue_varbit(void)
@@ -399,7 +653,13 @@ leftmostvalue_varbit(void)
Int32GetDatum(-1));
}
-GIN_SUPPORT(varbit, true, leftmostvalue_varbit, bitcmp)
+static const bool varbit_rhs_is_varlena[] =
+{true};
+
+static const PGFunction varbit_cmp_fns[] =
+{bitcmp};
+
+GIN_SUPPORT(varbit, leftmostvalue_varbit, varbit_rhs_is_varlena, NULL, varbit_cmp_fns)
/*
* Numeric type hasn't a real left-most value, so we use PointerGetDatum(NULL)
@@ -444,7 +704,13 @@ leftmostvalue_numeric(void)
return PointerGetDatum(NULL);
}
-GIN_SUPPORT(numeric, true, leftmostvalue_numeric, gin_numeric_cmp)
+static const bool numeric_rhs_is_varlena[] =
+{true};
+
+static const PGFunction numeric_cmp_fns[] =
+{gin_numeric_cmp};
+
+GIN_SUPPORT(numeric, leftmostvalue_numeric, numeric_rhs_is_varlena, NULL, numeric_cmp_fns)
/*
* Use a similar trick to that used for numeric for enums, since we don't
@@ -493,7 +759,13 @@ leftmostvalue_enum(void)
return ObjectIdGetDatum(InvalidOid);
}
-GIN_SUPPORT(anyenum, false, leftmostvalue_enum, gin_enum_cmp)
+static const bool enum_rhs_is_varlena[] =
+{false};
+
+static const PGFunction enum_cmp_fns[] =
+{gin_enum_cmp};
+
+GIN_SUPPORT(anyenum, leftmostvalue_enum, enum_rhs_is_varlena, NULL, enum_cmp_fns)
static Datum
leftmostvalue_uuid(void)
@@ -507,7 +779,13 @@ leftmostvalue_uuid(void)
return UUIDPGetDatum(retval);
}
-GIN_SUPPORT(uuid, false, leftmostvalue_uuid, uuid_cmp)
+static const bool uuid_rhs_is_varlena[] =
+{false};
+
+static const PGFunction uuid_cmp_fns[] =
+{uuid_cmp};
+
+GIN_SUPPORT(uuid, leftmostvalue_uuid, uuid_rhs_is_varlena, NULL, uuid_cmp_fns)
static Datum
leftmostvalue_name(void)
@@ -517,7 +795,13 @@ leftmostvalue_name(void)
return NameGetDatum(result);
}
-GIN_SUPPORT(name, false, leftmostvalue_name, btnamecmp)
+static const bool name_rhs_is_varlena[] =
+{false};
+
+static const PGFunction name_cmp_fns[] =
+{btnamecmp};
+
+GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, NULL, name_cmp_fns)
static Datum
leftmostvalue_bool(void)
@@ -525,4 +809,10 @@ leftmostvalue_bool(void)
return BoolGetDatum(false);
}
-GIN_SUPPORT(bool, false, leftmostvalue_bool, btboolcmp)
+static const bool bool_rhs_is_varlena[] =
+{false};
+
+static const PGFunction bool_cmp_fns[] =
+{btboolcmp};
+
+GIN_SUPPORT(bool, leftmostvalue_bool, bool_rhs_is_varlena, NULL, bool_cmp_fns)
diff --git a/contrib/btree_gin/btree_gin.control b/contrib/btree_gin/btree_gin.control
index 67d0c997d8..0c77c81727 100644
--- a/contrib/btree_gin/btree_gin.control
+++ b/contrib/btree_gin/btree_gin.control
@@ -1,6 +1,6 @@
# btree_gin extension
comment = 'support for indexing common datatypes in GIN'
-default_version = '1.3'
+default_version = '1.4'
module_pathname = '$libdir/btree_gin'
relocatable = true
trusted = true
diff --git a/contrib/btree_gin/expected/int2.out b/contrib/btree_gin/expected/int2.out
index 20d66a1b05..855292d53e 100644
--- a/contrib/btree_gin/expected/int2.out
+++ b/contrib/btree_gin/expected/int2.out
@@ -42,3 +42,187 @@ SELECT * FROM test_int2 WHERE i>1::int2 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+ QUERY PLAN
+-------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int2
+ Recheck Cond: (i < 1)
+ -> Bitmap Index Scan on idx_int2
+ Index Cond: (i < 1)
+(6 rows)
+
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i<=1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int2 WHERE i=1::int4 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int2 WHERE i>=1::int4 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i>1::int4 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int2
+ Recheck Cond: (i < '1'::bigint)
+ -> Bitmap Index Scan on idx_int2
+ Index Cond: (i < '1'::bigint)
+(6 rows)
+
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i<=1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int2 WHERE i=1::int8 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int2 WHERE i>=1::int8 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int2 WHERE i>1::int8 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_int2 VALUES ((-32768)::int2),(32767);
+SELECT * FROM test_int2 WHERE i<(-32769)::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i<=(-32769)::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i=(-32769)::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i>=(-32769)::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i>(-32769)::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i<32768::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i<=32768::int4 ORDER BY i;
+ i
+--------
+ -32768
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ 32767
+(8 rows)
+
+SELECT * FROM test_int2 WHERE i=32768::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i>=32768::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_int2 WHERE i>32768::int4 ORDER BY i;
+ i
+---
+(0 rows)
+
diff --git a/contrib/btree_gin/expected/int4.out b/contrib/btree_gin/expected/int4.out
index 0f0122c6f5..e62791e18b 100644
--- a/contrib/btree_gin/expected/int4.out
+++ b/contrib/btree_gin/expected/int4.out
@@ -42,3 +42,103 @@ SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int4
+ Recheck Cond: (i < '1'::smallint)
+ -> Bitmap Index Scan on idx_int4
+ Index Cond: (i < '1'::smallint)
+(6 rows)
+
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i<=1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int4 WHERE i=1::int2 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int4 WHERE i>=1::int2 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i>1::int2 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int4
+ Recheck Cond: (i < '1'::bigint)
+ -> Bitmap Index Scan on idx_int4
+ Index Cond: (i < '1'::bigint)
+(6 rows)
+
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i<=1::int8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int4 WHERE i=1::int8 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int4 WHERE i>=1::int8 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int4 WHERE i>1::int8 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
diff --git a/contrib/btree_gin/expected/int8.out b/contrib/btree_gin/expected/int8.out
index 307e19e7a0..c9aceb9d35 100644
--- a/contrib/btree_gin/expected/int8.out
+++ b/contrib/btree_gin/expected/int8.out
@@ -42,3 +42,103 @@ SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+ QUERY PLAN
+-----------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int8
+ Recheck Cond: (i < '1'::smallint)
+ -> Bitmap Index Scan on idx_int8
+ Index Cond: (i < '1'::smallint)
+(6 rows)
+
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i<=1::int2 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int8 WHERE i=1::int2 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int8 WHERE i>=1::int2 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i>1::int2 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+ QUERY PLAN
+-------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_int8
+ Recheck Cond: (i < 1)
+ -> Bitmap Index Scan on idx_int8
+ Index Cond: (i < 1)
+(6 rows)
+
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i<=1::int4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_int8 WHERE i=1::int4 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_int8 WHERE i>=1::int4 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_int8 WHERE i>1::int4 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
diff --git a/contrib/btree_gin/meson.build b/contrib/btree_gin/meson.build
index b2749f6e66..ece0a71697 100644
--- a/contrib/btree_gin/meson.build
+++ b/contrib/btree_gin/meson.build
@@ -22,6 +22,7 @@ install_data(
'btree_gin--1.0--1.1.sql',
'btree_gin--1.1--1.2.sql',
'btree_gin--1.2--1.3.sql',
+ 'btree_gin--1.3--1.4.sql',
kwargs: contrib_data_args,
)
diff --git a/contrib/btree_gin/sql/int2.sql b/contrib/btree_gin/sql/int2.sql
index f06f11702f..660785ad55 100644
--- a/contrib/btree_gin/sql/int2.sql
+++ b/contrib/btree_gin/sql/int2.sql
@@ -13,3 +13,37 @@ SELECT * FROM test_int2 WHERE i<=1::int2 ORDER BY i;
SELECT * FROM test_int2 WHERE i=1::int2 ORDER BY i;
SELECT * FROM test_int2 WHERE i>=1::int2 ORDER BY i;
SELECT * FROM test_int2 WHERE i>1::int2 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+
+SELECT * FROM test_int2 WHERE i<1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=1::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>1::int4 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+
+SELECT * FROM test_int2 WHERE i<1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=1::int8 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>1::int8 ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_int2 VALUES ((-32768)::int2),(32767);
+
+SELECT * FROM test_int2 WHERE i<(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=(-32769)::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>(-32769)::int4 ORDER BY i;
+
+SELECT * FROM test_int2 WHERE i<32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i<=32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i=32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>=32768::int4 ORDER BY i;
+SELECT * FROM test_int2 WHERE i>32768::int4 ORDER BY i;
diff --git a/contrib/btree_gin/sql/int4.sql b/contrib/btree_gin/sql/int4.sql
index 6499c29630..9a45530b63 100644
--- a/contrib/btree_gin/sql/int4.sql
+++ b/contrib/btree_gin/sql/int4.sql
@@ -13,3 +13,21 @@ SELECT * FROM test_int4 WHERE i<=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i>=1::int4 ORDER BY i;
SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+
+SELECT * FROM test_int4 WHERE i<1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i<=1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i=1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>=1::int2 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>1::int2 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+
+SELECT * FROM test_int4 WHERE i<1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i<=1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i=1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>=1::int8 ORDER BY i;
+SELECT * FROM test_int4 WHERE i>1::int8 ORDER BY i;
diff --git a/contrib/btree_gin/sql/int8.sql b/contrib/btree_gin/sql/int8.sql
index 4d9c287181..b31f27c69b 100644
--- a/contrib/btree_gin/sql/int8.sql
+++ b/contrib/btree_gin/sql/int8.sql
@@ -13,3 +13,21 @@ SELECT * FROM test_int8 WHERE i<=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i>=1::int8 ORDER BY i;
SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+
+SELECT * FROM test_int8 WHERE i<1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i<=1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i=1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>=1::int2 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>1::int2 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+
+SELECT * FROM test_int8 WHERE i<1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i<=1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i=1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>=1::int4 ORDER BY i;
+SELECT * FROM test_int8 WHERE i>1::int4 ORDER BY i;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index cb9388f61a..26a698cf04 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3377,6 +3377,7 @@ bloom_filter
boolKEY
brin_column_state
brin_serialize_callback_type
+btree_gin_convert_function
btree_gin_leftmost_function
bytea
cached_re_str
--
2.43.5
From 490825fd551df5f48ce2bcfc5804e601a92666e6 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 1 Feb 2025 18:37:11 -0500
Subject: [PATCH v1 3/5] Add cross-type comparisons for float types.
---
contrib/btree_gin/btree_gin--1.3--1.4.sql | 20 ++
contrib/btree_gin/btree_gin.c | 44 ++++-
contrib/btree_gin/expected/float4.out | 227 ++++++++++++++++++++++
contrib/btree_gin/expected/float8.out | 50 +++++
contrib/btree_gin/sql/float4.sql | 37 ++++
contrib/btree_gin/sql/float8.sql | 9 +
6 files changed, 381 insertions(+), 6 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 71e577de2d..88ed5d5b3e 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -61,3 +61,23 @@ ADD
OPERATOR 20 >= (int8, int4),
OPERATOR 21 > (int8, int4)
;
+
+ALTER OPERATOR FAMILY float4_ops USING gin
+ADD
+ -- Code 1: RHS is float8
+ OPERATOR 9 < (float4, float8),
+ OPERATOR 10 <= (float4, float8),
+ OPERATOR 11 = (float4, float8),
+ OPERATOR 12 >= (float4, float8),
+ OPERATOR 13 > (float4, float8)
+;
+
+ALTER OPERATOR FAMILY float8_ops USING gin
+ADD
+ -- Code 1: RHS is float4
+ OPERATOR 9 < (float8, float4),
+ OPERATOR 10 <= (float8, float4),
+ OPERATOR 11 = (float8, float4),
+ OPERATOR 12 >= (float8, float4),
+ OPERATOR 13 > (float8, float4)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 497c8c1946..95c193a4ca 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -392,13 +392,34 @@ leftmostvalue_float4(void)
return Float4GetDatum(-get_float4_infinity());
}
+static Datum
+cvt_float8_float4(Datum input)
+{
+ float8 val = DatumGetFloat8(input);
+ float4 result;
+
+ /*
+ * Assume that ordinary C conversion will produce a usable result.
+ * (Compare dtof(), which raises error conditions that we don't need.)
+ * Note that for inputs that aren't exactly representable as float4, it
+ * doesn't matter whether the conversion rounds up or down. That might
+ * cause us to scan a few index entries that we'll reject as not matching,
+ * but we won't miss any that should match.
+ */
+ result = (float4) val;
+ return Float4GetDatum(result);
+}
+
static const bool float4_rhs_is_varlena[] =
-{false};
+{false, false};
+
+static const btree_gin_convert_function float4_cvt_fns[] =
+{NULL, cvt_float8_float4};
static const PGFunction float4_cmp_fns[] =
-{btfloat4cmp};
+{btfloat4cmp, btfloat84cmp};
-GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, NULL, float4_cmp_fns)
+GIN_SUPPORT(float4, leftmostvalue_float4, float4_rhs_is_varlena, float4_cvt_fns, float4_cmp_fns)
static Datum
leftmostvalue_float8(void)
@@ -406,13 +427,24 @@ leftmostvalue_float8(void)
return Float8GetDatum(-get_float8_infinity());
}
+static Datum
+cvt_float4_float8(Datum input)
+{
+ float4 val = DatumGetFloat4(input);
+
+ return Float8GetDatum((float8) val);
+}
+
static const bool float8_rhs_is_varlena[] =
-{false};
+{false, false};
+
+static const btree_gin_convert_function float8_cvt_fns[] =
+{NULL, cvt_float4_float8};
static const PGFunction float8_cmp_fns[] =
-{btfloat8cmp};
+{btfloat8cmp, btfloat48cmp};
-GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, NULL, float8_cmp_fns)
+GIN_SUPPORT(float8, leftmostvalue_float8, float8_rhs_is_varlena, float8_cvt_fns, float8_cmp_fns)
static Datum
leftmostvalue_money(void)
diff --git a/contrib/btree_gin/expected/float4.out b/contrib/btree_gin/expected/float4.out
index 7b9134fcd4..02c88e4007 100644
--- a/contrib/btree_gin/expected/float4.out
+++ b/contrib/btree_gin/expected/float4.out
@@ -42,3 +42,230 @@ SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+ QUERY PLAN
+-------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_float4
+ Recheck Cond: (i < '1'::double precision)
+ -> Bitmap Index Scan on idx_float4
+ Index Cond: (i < '1'::double precision)
+(6 rows)
+
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
+-- Check endpoint and out-of-range cases
+INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf');
+SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+ NaN
+(9 rows)
+
+SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i;
+ i
+----------
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+ NaN
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+ NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i;
+ i
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+(7 rows)
+
+SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i;
+ i
+---
+(0 rows)
+
+SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+ NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i;
+ i
+----------
+ Infinity
+ NaN
+(2 rows)
+
+SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+(8 rows)
+
+SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i;
+ i
+-----------
+ -Infinity
+ -2
+ -1
+ 0
+ 1
+ 2
+ 3
+ Infinity
+ NaN
+(9 rows)
+
+SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i;
+ i
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i;
+ i
+-----
+ NaN
+(1 row)
+
+SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i;
+ i
+---
+(0 rows)
+
diff --git a/contrib/btree_gin/expected/float8.out b/contrib/btree_gin/expected/float8.out
index a41d4f9f6b..b2877dfa3c 100644
--- a/contrib/btree_gin/expected/float8.out
+++ b/contrib/btree_gin/expected/float8.out
@@ -42,3 +42,53 @@ SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i;
3
(2 rows)
+explain (costs off)
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_float8
+ Recheck Cond: (i < '1'::real)
+ -> Bitmap Index Scan on idx_float8
+ Index Cond: (i < '1'::real)
+(6 rows)
+
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+(3 rows)
+
+SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i;
+ i
+----
+ -2
+ -1
+ 0
+ 1
+(4 rows)
+
+SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i;
+ i
+---
+ 1
+(1 row)
+
+SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i;
+ i
+---
+ 2
+ 3
+(2 rows)
+
diff --git a/contrib/btree_gin/sql/float4.sql b/contrib/btree_gin/sql/float4.sql
index 759778ad3c..e850e1a058 100644
--- a/contrib/btree_gin/sql/float4.sql
+++ b/contrib/btree_gin/sql/float4.sql
@@ -13,3 +13,40 @@ SELECT * FROM test_float4 WHERE i<=1::float4 ORDER BY i;
SELECT * FROM test_float4 WHERE i=1::float4 ORDER BY i;
SELECT * FROM test_float4 WHERE i>=1::float4 ORDER BY i;
SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>=1::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>1::float8 ORDER BY i;
+
+-- Check endpoint and out-of-range cases
+
+INSERT INTO test_float4 VALUES ('NaN'), ('Inf'), ('-Inf');
+
+SELECT * FROM test_float4 WHERE i<'-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='-Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'-Inf'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='Inf'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'Inf'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='1e300'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'1e300'::float8 ORDER BY i;
+
+SELECT * FROM test_float4 WHERE i<'NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i<='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>='NaN'::float8 ORDER BY i;
+SELECT * FROM test_float4 WHERE i>'NaN'::float8 ORDER BY i;
diff --git a/contrib/btree_gin/sql/float8.sql b/contrib/btree_gin/sql/float8.sql
index b046ac4e6c..5f39314708 100644
--- a/contrib/btree_gin/sql/float8.sql
+++ b/contrib/btree_gin/sql/float8.sql
@@ -13,3 +13,12 @@ SELECT * FROM test_float8 WHERE i<=1::float8 ORDER BY i;
SELECT * FROM test_float8 WHERE i=1::float8 ORDER BY i;
SELECT * FROM test_float8 WHERE i>=1::float8 ORDER BY i;
SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+
+SELECT * FROM test_float8 WHERE i<1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i<=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i>=1::float4 ORDER BY i;
+SELECT * FROM test_float8 WHERE i>1::float4 ORDER BY i;
--
2.43.5
From eb42f59a3d6059b95e13d7d76483c95f36ba2049 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 1 Feb 2025 18:49:05 -0500
Subject: [PATCH v1 4/5] Add cross-type comparisons for string types.
(Only these two cases appear in the catalogs.)
---
contrib/btree_gin/btree_gin--1.3--1.4.sql | 20 ++++++++
contrib/btree_gin/btree_gin.c | 49 ++++++++++++++++---
contrib/btree_gin/expected/name.out | 59 +++++++++++++++++++++++
contrib/btree_gin/expected/text.out | 50 +++++++++++++++++++
contrib/btree_gin/sql/name.sql | 11 +++++
contrib/btree_gin/sql/text.sql | 9 ++++
6 files changed, 192 insertions(+), 6 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 88ed5d5b3e..13c84ad667 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -81,3 +81,23 @@ ADD
OPERATOR 12 >= (float8, float4),
OPERATOR 13 > (float8, float4)
;
+
+ALTER OPERATOR FAMILY text_ops USING gin
+ADD
+ -- Code 1: RHS is name
+ OPERATOR 9 < (text, name),
+ OPERATOR 10 <= (text, name),
+ OPERATOR 11 = (text, name),
+ OPERATOR 12 >= (text, name),
+ OPERATOR 13 > (text, name)
+;
+
+ALTER OPERATOR FAMILY name_ops USING gin
+ADD
+ -- Code 1: RHS is text
+ OPERATOR 9 < (name, text),
+ OPERATOR 10 <= (name, text),
+ OPERATOR 11 = (name, text),
+ OPERATOR 12 >= (name, text),
+ OPERATOR 13 > (name, text)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index 95c193a4ca..1d4469bef0 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -6,6 +6,7 @@
#include <limits.h>
#include "access/stratnum.h"
+#include "mb/pg_wchar.h"
#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/float.h"
@@ -13,6 +14,7 @@
#include "utils/numeric.h"
#include "utils/timestamp.h"
#include "utils/uuid.h"
+#include "varatt.h"
PG_MODULE_MAGIC;
@@ -621,13 +623,24 @@ leftmostvalue_text(void)
return PointerGetDatum(cstring_to_text_with_len("", 0));
}
+static Datum
+cvt_name_text(Datum input)
+{
+ Name val = DatumGetName(input);
+
+ return PointerGetDatum(cstring_to_text(NameStr(*val)));
+}
+
static const bool text_rhs_is_varlena[] =
-{true};
+{true, false};
+
+static const btree_gin_convert_function text_cvt_fns[] =
+{NULL, cvt_name_text};
static const PGFunction text_cmp_fns[] =
-{bttextcmp};
+{bttextcmp, btnametextcmp};
-GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, NULL, text_cmp_fns)
+GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, text_cvt_fns, text_cmp_fns)
static const bool bpchar_rhs_is_varlena[] =
{true};
@@ -827,13 +840,37 @@ leftmostvalue_name(void)
return NameGetDatum(result);
}
+static Datum
+cvt_text_name(Datum input)
+{
+ text *val = DatumGetTextPP(input);
+ NameData *result = (NameData *) palloc0(NAMEDATALEN);
+ int len = VARSIZE_ANY_EXHDR(val);
+
+ /*
+ * Truncate oversize input. We're assuming this will produce a result
+ * considered less than the original. That could be a bad assumption in
+ * some collations, but fortunately an index on "name" is generally going
+ * to use C collation.
+ */
+ if (len >= NAMEDATALEN)
+ len = pg_mbcliplen(VARDATA_ANY(val), len, NAMEDATALEN - 1);
+
+ memcpy(NameStr(*result), VARDATA_ANY(val), len);
+
+ return NameGetDatum(result);
+}
+
static const bool name_rhs_is_varlena[] =
-{false};
+{false, true};
+
+static const btree_gin_convert_function name_cvt_fns[] =
+{NULL, cvt_text_name};
static const PGFunction name_cmp_fns[] =
-{btnamecmp};
+{btnamecmp, bttextnamecmp};
-GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, NULL, name_cmp_fns)
+GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, name_cvt_fns, name_cmp_fns)
static Datum
leftmostvalue_bool(void)
diff --git a/contrib/btree_gin/expected/name.out b/contrib/btree_gin/expected/name.out
index 174de6576f..3a30f62519 100644
--- a/contrib/btree_gin/expected/name.out
+++ b/contrib/btree_gin/expected/name.out
@@ -95,3 +95,62 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i;
Index Cond: (i > 'abc'::name)
(6 rows)
+explain (costs off)
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+ QUERY PLAN
+---------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_name
+ Recheck Cond: (i < 'abc'::text)
+ -> Bitmap Index Scan on idx_name
+ Index Cond: (i < 'abc'::text)
+(6 rows)
+
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+(3 rows)
+
+SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
+SELECT * FROM test_name WHERE i='abc'::text ORDER BY i;
+ i
+-----
+ abc
+(1 row)
+
+SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i;
+ i
+-----
+ abc
+ axy
+ xyz
+(3 rows)
+
+SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i;
+ i
+-----
+ axy
+ xyz
+(2 rows)
+
+SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
diff --git a/contrib/btree_gin/expected/text.out b/contrib/btree_gin/expected/text.out
index 3e31ad744d..7f52f3db7b 100644
--- a/contrib/btree_gin/expected/text.out
+++ b/contrib/btree_gin/expected/text.out
@@ -42,3 +42,53 @@ SELECT * FROM test_text WHERE i>'abc' ORDER BY i;
xyz
(2 rows)
+explain (costs off)
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: i
+ -> Bitmap Heap Scan on test_text
+ Recheck Cond: (i < 'abc'::name COLLATE "default")
+ -> Bitmap Index Scan on idx_text
+ Index Cond: (i < 'abc'::name COLLATE "default")
+(6 rows)
+
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+(3 rows)
+
+SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
+SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ abc
+(1 row)
+
+SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ abc
+ axy
+ xyz
+(3 rows)
+
+SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i;
+ i
+-----
+ axy
+ xyz
+(2 rows)
+
diff --git a/contrib/btree_gin/sql/name.sql b/contrib/btree_gin/sql/name.sql
index c11580cdf9..551d928940 100644
--- a/contrib/btree_gin/sql/name.sql
+++ b/contrib/btree_gin/sql/name.sql
@@ -19,3 +19,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<='abc' ORDER BY i;
EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i='abc' ORDER BY i;
EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>='abc' ORDER BY i;
EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i;
+
+SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i;
diff --git a/contrib/btree_gin/sql/text.sql b/contrib/btree_gin/sql/text.sql
index d5b3b39898..978b21376f 100644
--- a/contrib/btree_gin/sql/text.sql
+++ b/contrib/btree_gin/sql/text.sql
@@ -13,3 +13,12 @@ SELECT * FROM test_text WHERE i<='abc' ORDER BY i;
SELECT * FROM test_text WHERE i='abc' ORDER BY i;
SELECT * FROM test_text WHERE i>='abc' ORDER BY i;
SELECT * FROM test_text WHERE i>'abc' ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i;
--
2.43.5
From 1f7bea765edc1d5556b4a0351927e0f18c079217 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 1 Feb 2025 19:48:15 -0500
Subject: [PATCH v1 5/5] Add cross-type comparisons for datetime types.
---
contrib/btree_gin/btree_gin--1.3--1.4.sql | 48 ++++
contrib/btree_gin/btree_gin.c | 155 ++++++++++-
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, 981 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 13c84ad667..b8483c3b9a 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 1d4469bef0..bac7e40fbd 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -9,6 +9,7 @@
#include "mb/pg_wchar.h"
#include "utils/builtins.h"
#include "utils/date.h"
+#include "utils/datetime.h"
#include "utils/float.h"
#include "utils/inet.h"
#include "utils/numeric.h"
@@ -482,21 +483,91 @@ 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)
+{
+ /* Sadly, there's no timestamptz2timestamp_opt_overflow */
+ TimestampTz timestamp = DatumGetTimestampTz(input);
+ Timestamp result;
+ struct pg_tm tt,
+ *tm = &tt;
+ fsec_t fsec;
+ int tz;
+
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ result = timestamp;
+ else
+ {
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0 ||
+ tm2timestamp(tm, fsec, NULL, &result) != 0)
+ {
+ /* Rather than throwing an error, use DT_NOBEGIN/NOEND */
+ if (timestamp < 0)
+ TIMESTAMP_NOBEGIN(result);
+ else
+ TIMESTAMP_NOEND(result);
+ }
+ }
+ 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;
+
+ 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 timestamp = DatumGetTimestamp(input);
+ TimestampTz result;
+ int overflow;
-GIN_SUPPORT(timestamp, leftmostvalue_timestamp, timestamp_rhs_is_varlena, NULL, timestamp_cmp_fns)
+ result = timestamp2timestamptz_opt_overflow(timestamp, &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)
@@ -537,13 +608,79 @@ leftmostvalue_date(void)
return DateADTGetDatum(DATEVAL_NOBEGIN);
}
+static Datum
+cvt_timestamp_date(Datum input)
+{
+ Timestamp timestamp = DatumGetTimestamp(input);
+ DateADT result;
+ struct pg_tm tt,
+ *tm = &tt;
+ fsec_t fsec;
+
+ if (TIMESTAMP_IS_NOBEGIN(timestamp))
+ DATE_NOBEGIN(result);
+ else if (TIMESTAMP_IS_NOEND(timestamp))
+ DATE_NOEND(result);
+ else
+ {
+ if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+ {
+ /* Rather than throwing an error, use DATEVAL_NOBEGIN/NOEND */
+ /* (This code is probably unreachable given the types' ranges) */
+ if (timestamp < 0)
+ DATE_NOBEGIN(result);
+ else
+ DATE_NOEND(result);
+ }
+ else
+ result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
+ }
+
+ return DateADTGetDatum(result);
+}
+
+static Datum
+cvt_timestamptz_date(Datum input)
+{
+ TimestampTz timestamp = DatumGetTimestampTz(input);
+ DateADT result;
+ struct pg_tm tt,
+ *tm = &tt;
+ fsec_t fsec;
+ int tz;
+
+ if (TIMESTAMP_IS_NOBEGIN(timestamp))
+ DATE_NOBEGIN(result);
+ else if (TIMESTAMP_IS_NOEND(timestamp))
+ DATE_NOEND(result);
+ else
+ {
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ {
+ /* Rather than throwing an error, use DATEVAL_NOBEGIN/NOEND */
+ /* (This code is probably unreachable given the types' ranges) */
+ if (timestamp < 0)
+ DATE_NOBEGIN(result);
+ else
+ DATE_NOEND(result);
+ }
+ else
+ result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
+ }
+
+ 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 40dfa308cf..d638299b21 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 a236cdc94a..9f823a3c74 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 d53963d2a0..0dada0b662 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 35086f6b81..6bd6a08da3 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 56727e81c4..9f830bcf69 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 e6cfdb1b07..40d2d7ed32 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.5
pgsql-hackers by date: