On Tue, Sep 17, 2019 at 5:18 AM David Fetter <da...@fetter.org> wrote:
> It's not done by pull request at this time. Instead, it is done by sending
> patches to this mailing list.
Dear all
You will find enclosed the patch that extends the range type operators so
they cope with elements.
Any comments most welcome.
Esteban
diff -urdN postgresql-11.5-orig/doc/src/sgml/func.sgml postgresql-11.5-ranges/doc/src/sgml/func.sgml
--- postgresql-11.5-orig/doc/src/sgml/func.sgml 2019-09-21 11:28:11.836309263 +0200
+++ postgresql-11.5-ranges/doc/src/sgml/func.sgml 2019-09-21 10:32:53.320004000 +0200
@@ -13228,6 +13228,20 @@
</row>
<row>
+ <entry> <literal><<</literal> </entry>
+ <entry>strictly left of element</entry>
+ <entry><literal>int8range(1,10) << 100</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal><<</literal> </entry>
+ <entry>element strictly left of</entry>
+ <entry><literal>10 << int8range(100,110)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>>></literal> </entry>
<entry>strictly right of</entry>
<entry><literal>int8range(50,60) >> int8range(20,30)</literal></entry>
@@ -13235,6 +13249,20 @@
</row>
<row>
+ <entry> <literal>>></literal> </entry>
+ <entry>strictly right of element</entry>
+ <entry><literal>int8range(50,60) >> 20</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>>></literal> </entry>
+ <entry>element strictly right of</entry>
+ <entry><literal>50 >> int8range(20,30)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>&<</literal> </entry>
<entry>does not extend to the right of</entry>
<entry><literal>int8range(1,20) &< int8range(18,20)</literal></entry>
@@ -13242,6 +13270,20 @@
</row>
<row>
+ <entry> <literal>&<</literal> </entry>
+ <entry>does not extend to the right of element</entry>
+ <entry><literal>int8range(1,20) &< 20</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>&<</literal> </entry>
+ <entry>element does not extend to the right of</entry>
+ <entry><literal>19 &< int8range(18,20)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>&></literal> </entry>
<entry>does not extend to the left of</entry>
<entry><literal>int8range(7,20) &> int8range(5,10)</literal></entry>
@@ -13249,12 +13291,40 @@
</row>
<row>
+ <entry> <literal>&></literal> </entry>
+ <entry>does not extend to the left of element</entry>
+ <entry><literal>int8range(7,20) &> 5</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>&></literal> </entry>
+ <entry>element does not extend to the left of</entry>
+ <entry><literal>7 &> int8range(5,10)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>-|-</literal> </entry>
<entry>is adjacent to</entry>
<entry><literal>numrange(1.1,2.2) -|- numrange(2.2,3.3)</literal></entry>
<entry><literal>t</literal></entry>
</row>
+ <row>
+ <entry> <literal>-|-</literal> </entry>
+ <entry>is adjacent to element</entry>
+ <entry><literal>numrange(1.1,2.2) -|- 2.2</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>-|-</literal> </entry>
+ <entry>element is adjacent to</entry>
+ <entry><literal>2.2 -|- numrange(2.2,3.3, '()')</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
<row>
<entry> <literal>+</literal> </entry>
<entry>union</entry>
diff -urdN postgresql-11.5-orig/src/backend/utils/adt/rangetypes.c postgresql-11.5-ranges/src/backend/utils/adt/rangetypes.c
--- postgresql-11.5-orig/src/backend/utils/adt/rangetypes.c 2019-09-21 11:28:11.628205263 +0200
+++ postgresql-11.5-ranges/src/backend/utils/adt/rangetypes.c 2019-09-21 10:32:53.320004000 +0200
@@ -548,6 +548,322 @@
PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
}
+/* strictly left of element? (internal version) */
+bool
+range_before_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+ int32 cmp;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any other range */
+ if (empty)
+ return false;
+
+ if (!upper.infinite)
+ {
+ cmp = DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+ typcache->rng_collation,
+ upper.val, val));
+ if (cmp < 0 ||
+ (cmp == 0 && !upper.inclusive))
+ return true;
+ }
+
+ return false;
+}
+
+/* strictly left of element? */
+Datum
+range_before_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_before_elem_internal(typcache, r, val));
+}
+
+/* does not extend to right of element? (internal version) */
+bool
+range_overleft_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!upper.infinite)
+ {
+ if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+ typcache->rng_collation,
+ upper.val, val)) <= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* does not extend to right of element? */
+Datum
+range_overleft_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_overleft_elem_internal(typcache, r, val));
+}
+
+/* strictly right of element? (internal version) */
+bool
+range_after_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+ int32 cmp;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any other range */
+ if (empty)
+ return false;
+
+ if (!lower.infinite)
+ {
+ cmp = DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+ typcache->rng_collation,
+ lower.val, val));
+ if (cmp > 0 ||
+ (cmp == 0 && !lower.inclusive))
+ return true;
+ }
+
+ return false;
+}
+
+/* strictly right of element? */
+Datum
+range_after_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_after_elem_internal(typcache, r, val));
+}
+
+/* does not extend to left of element? (internal version) */
+bool
+range_overright_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!lower.infinite)
+ {
+ if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+ typcache->rng_collation,
+ lower.val, val)) >= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* does not extend to left of element? */
+Datum
+range_overright_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_overright_elem_internal(typcache, r, val));
+}
+
+/* adjacent to element (but not overlapping)? (internal version) */
+bool
+range_adjacent_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+ RangeBound elembound;
+ bool isadj;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is not adjacent to any element */
+ if (empty)
+ return false;
+
+ /*
+ * A range A..B and a value V are adjacent if and only if
+ * B is adjacent to V, or V is adjacent to A.
+ */
+ elembound.val = val;
+ elembound.infinite = false;
+ elembound.inclusive = true;
+ elembound.lower = true;
+ isadj = bounds_adjacent(typcache, upper, elembound);
+ elembound.lower = false;
+ return (isadj || bounds_adjacent(typcache, elembound, lower));
+}
+
+/* adjacent to element (but not overlapping)? */
+Datum
+range_adjacent_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_adjacent_elem_internal(typcache, r, val));
+}
+
+/******************************************************************************/
+
+/* element strictly left of? */
+Datum
+elem_before_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_after_elem_internal(typcache, r, val));
+}
+
+/* element does not extend to right of? (internal version) */
+bool
+elem_overleft_range_internal(TypeCacheEntry *typcache, Datum val, RangeType *r)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!upper.infinite)
+ {
+ if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+ typcache->rng_collation,
+ val, upper.val)) <= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* element does not extend to right of? */
+Datum
+elem_overleft_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(elem_overleft_range_internal(typcache, val, r));
+}
+
+/* element strictly right of? */
+Datum
+elem_after_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_before_elem_internal(typcache, r, val));
+}
+
+/* element does not extend to left of? (internal version) */
+bool
+elem_overright_range_internal(TypeCacheEntry *typcache, Datum val, RangeType *r)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!lower.infinite)
+ {
+ if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+ typcache->rng_collation,
+ val, lower.val)) >= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* element does not extend the left of? */
+Datum
+elem_overright_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(elem_overright_range_internal(typcache, val, r));
+}
+
+/* element adjacent to? */
+Datum
+elem_adjacent_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_adjacent_elem_internal(typcache, r, val));
+}
/* range, range -> bool functions */
diff -urdN postgresql-11.5-orig/src/include/catalog/pg_operator.dat postgresql-11.5-ranges/src/include/catalog/pg_operator.dat
--- postgresql-11.5-orig/src/include/catalog/pg_operator.dat 2019-09-21 11:28:11.484133263 +0200
+++ postgresql-11.5-ranges/src/include/catalog/pg_operator.dat 2019-09-21 17:05:20.566965777 +0200
@@ -3336,5 +3336,59 @@
{ oid => '3287', descr => 'delete path',
oprname => '#-', oprleft => 'jsonb', oprright => '_text',
oprresult => 'jsonb', oprcode => 'jsonb_delete_path' },
+{ oid => '4001', oid_symbol => 'OID_RANGE_ADJACENT_ELEM_OP',
+ descr => 'range is adjacent to element',
+ oprname => '-|-', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcom => '-|-(anyelement,anyrange)',
+ oprcode => 'range_adjacent_elem', oprrest => 'contsel', oprjoin => 'contjoinsel' },
+{ oid => '4002', oid_symbol => 'OID_ELEM_ADJACENT_RANGE_OP',
+ descr => 'element is adjacent to range',
+ oprname => '-|-', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcom => '-|-(anyrange,anyelement)',
+ oprcode => 'elem_adjacent_range', oprrest => 'contsel', oprjoin => 'contjoinsel' },
+{ oid => '4003', oid_symbol => 'OID_RANGE_LEFT_ELEM_OP',
+ descr => 'is left of element',
+ oprname => '<<', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcom => '>>(anyelement,anyrange)',
+ oprcode => 'range_before_elem', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4004', oid_symbol => 'OID_ELEM_LEFT_RANGE_OP',
+ descr => 'element is left of range',
+ oprname => '<<', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcom => '>>(anyrange,anyelement)',
+ oprcode => 'elem_before_range', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4005', oid_symbol => 'OID_RANGE_RIGHT_ELEM_OP',
+ descr => 'is right of element',
+ oprname => '>>', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcom => '<<(anyelement,anyrange)',
+ oprcode => 'range_after_elem', oprrest => 'rangesel',
+ oprjoin => 'scalargtjoinsel' },
+{ oid => '4006', oid_symbol => 'OID_ELEM_RIGHT_RANGE_OP',
+ descr => 'element is right of range',
+ oprname => '>>', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcom => '<<(anyrange,anyelement)',
+ oprcode => 'elem_after_range', oprrest => 'rangesel',
+ oprjoin => 'scalargtjoinsel' },
+{ oid => '4007', oid_symbol => 'OID_RANGE_OVERLAPS_LEFT_ELEM_OP',
+ descr => 'overlaps or is left of element',
+ oprname => '&<', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcode => 'range_overleft_elem', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4008', oid_symbol => 'OID_ELEM_OVERLAPS_LEFT_RANGE_OP',
+ descr => 'element overlaps or is left of range',
+ oprname => '&<', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcode => 'elem_overleft_range', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4009', oid_symbol => 'OID_RANGE_OVERLAPS_RIGHT_ELEM_OP',
+ descr => 'overlaps or is right of element',
+ oprname => '&>', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcode => 'range_overright_elem', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4010', oid_symbol => 'OID_ELEM_OVERLAPS_RIGHT_RANGE_OP',
+ descr => 'element overlaps or is right of range',
+ oprname => '&>', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcode => 'elem_overright_range', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
]
diff -urdN postgresql-11.5-orig/src/include/catalog/pg_proc.dat postgresql-11.5-ranges/src/include/catalog/pg_proc.dat
--- postgresql-11.5-orig/src/include/catalog/pg_proc.dat 2019-09-21 11:28:11.476129264 +0200
+++ postgresql-11.5-ranges/src/include/catalog/pg_proc.dat 2019-09-21 16:00:11.649542000 +0200
@@ -9591,6 +9591,36 @@
{ oid => '3169', descr => 'restriction selectivity for range operators',
proname => 'rangesel', provolatile => 's', prorettype => 'float8',
proargtypes => 'internal oid internal int4', prosrc => 'rangesel' },
+{ oid => '4142',
+ proname => 'range_adjacent_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_adjacent_elem' },
+{ oid => '4143',
+ proname => 'elem_adjacent_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_adjacent_range' },
+{ oid => '4144',
+ proname => 'range_before_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_before_elem' },
+{ oid => '4145',
+ proname => 'elem_before_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_before_range' },
+{ oid => '4146',
+ proname => 'range_after_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_after_elem' },
+{ oid => '4147',
+ proname => 'elem_after_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_after_range' },
+{ oid => '4148',
+ proname => 'range_overleft_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_overleft_elem' },
+{ oid => '4149',
+ proname => 'elem_overleft_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_overleft_range' },
+{ oid => '4150',
+ proname => 'range_overright_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_overright_elem' },
+{ oid => '4151',
+ proname => 'elem_overright_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_overright_range' },
{ oid => '3914', descr => 'convert an int4 range to canonical form',
proname => 'int4range_canonical', prorettype => 'int4range',
diff -urdN postgresql-11.5-orig/src/include/utils/rangetypes.h postgresql-11.5-ranges/src/include/utils/rangetypes.h
--- postgresql-11.5-orig/src/include/utils/rangetypes.h 2019-09-21 11:28:11.472127264 +0200
+++ postgresql-11.5-ranges/src/include/utils/rangetypes.h 2019-09-21 10:32:53.320004000 +0200
@@ -94,6 +94,18 @@
extern bool range_contains_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val);
+extern bool range_before_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val);
+extern bool range_overleft_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val);
+extern bool range_after_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val);
+extern bool range_overright_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val);
+extern bool range_adjacent_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val);
+
+extern bool elem_before_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_overleft_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_after_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_overright_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_adjacent_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+
/* internal versions of the above */
extern bool range_eq_internal(TypeCacheEntry *typcache, RangeType *r1,
RangeType *r2);
diff -urdN postgresql-11.5-orig/src/test/regress/expected/rangetypes.out postgresql-11.5-ranges/src/test/regress/expected/rangetypes.out
--- postgresql-11.5-orig/src/test/regress/expected/rangetypes.out 2019-09-21 11:28:11.536159263 +0200
+++ postgresql-11.5-ranges/src/test/regress/expected/rangetypes.out 2019-09-21 17:26:44.324523000 +0200
@@ -378,6 +378,18 @@
t
(1 row)
+select numrange(1.0, 2.0) -|- 2.0;
+ ?column?
+----------
+ t
+(1 row)
+
+select 2.0 -|- numrange(2.0, 3.0,'()');
+ ?column?
+----------
+ t
+(1 row)
+
select numrange(1.1, 3.3) <@ numrange(0.1,10.1);
?column?
----------
@@ -432,6 +444,18 @@
t
(1 row)
+select numrange(1.0, 2.0) << 3.0;
+ ?column?
+----------
+ t
+(1 row)
+
+select 2.0 << numrange(3.0, 4.0);
+ ?column?
+----------
+ t
+(1 row)
+
select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
?column?
----------
@@ -450,12 +474,54 @@
f
(1 row)
+select numrange(1.0, 2.0) >> 3.0;
+ ?column?
+----------
+ f
+(1 row)
+
+select 2.0 >> numrange(3.0, 4.0);
+ ?column?
+----------
+ f
+(1 row)
+
select numrange(3.0, 70.0) &< numrange(6.6, 100.0);
?column?
----------
t
(1 row)
+select numrange(3.0, 70.0) &< 6.6;
+ ?column?
+----------
+ f
+(1 row)
+
+select 70.0 &< numrange(6.6, 100.0);
+ ?column?
+----------
+ t
+(1 row)
+
+select numrange(3.0, 70.0) &> numrange(6.6, 100.0);
+ ?column?
+----------
+ f
+(1 row)
+
+select numrange(3.0, 70.0) &> 6.6;
+ ?column?
+----------
+ f
+(1 row)
+
+select 70.0 &> numrange(6.6, 100.0);
+ ?column?
+----------
+ t
+(1 row)
+
select numrange(1.1, 2.2) < numrange(1.0, 200.2);
?column?
----------
@@ -732,30 +798,60 @@
189
(1 row)
+select count(*) from test_range_gist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_gist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_gist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_gist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_gist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_gist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_gist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using index
SET enable_seqscan = f;
SET enable_indexscan = t;
@@ -802,30 +898,60 @@
189
(1 row)
+select count(*) from test_range_gist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_gist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_gist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_gist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_gist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_gist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_gist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using a bulk-loaded index
drop index test_range_gist_idx;
create index test_range_gist_idx on test_range_gist using gist (ir);
@@ -871,30 +997,60 @@
189
(1 row)
+select count(*) from test_range_gist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_gist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_gist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_gist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_gist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_gist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_gist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- test SP-GiST index that's been built incrementally
create table test_range_spgist(ir int4range);
create index test_range_spgist_idx on test_range_spgist using spgist (ir);
@@ -951,30 +1107,60 @@
189
(1 row)
+select count(*) from test_range_spgist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_spgist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_spgist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_spgist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_spgist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_spgist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_spgist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using index
SET enable_seqscan = f;
SET enable_indexscan = t;
@@ -1021,30 +1207,60 @@
189
(1 row)
+select count(*) from test_range_spgist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_spgist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_spgist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_spgist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_spgist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_spgist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_spgist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using a bulk-loaded index
drop index test_range_spgist_idx;
create index test_range_spgist_idx on test_range_spgist using spgist (ir);
@@ -1090,30 +1306,60 @@
189
(1 row)
+select count(*) from test_range_spgist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_spgist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_spgist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_spgist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_spgist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_spgist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_spgist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- test index-only scans
explain (costs off)
select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
diff -urdN postgresql-11.5-orig/src/test/regress/sql/rangetypes.sql postgresql-11.5-ranges/src/test/regress/sql/rangetypes.sql
--- postgresql-11.5-orig/src/test/regress/sql/rangetypes.sql 2019-09-21 11:28:11.512147263 +0200
+++ postgresql-11.5-ranges/src/test/regress/sql/rangetypes.sql 2019-09-21 10:32:53.320004000 +0200
@@ -87,6 +87,9 @@
select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]');
select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]'));
+select numrange(1.0, 2.0) -|- 2.0;
+select 2.0 -|- numrange(2.0, 3.0,'()');
+
select numrange(1.1, 3.3) <@ numrange(0.1,10.1);
select numrange(0.1, 10.1) <@ numrange(1.1,3.3);
@@ -98,10 +101,19 @@
select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
select numrange(1.0, 2.0) << numrange(3.0, 4.0);
+select numrange(1.0, 2.0) << 3.0;
+select 2.0 << numrange(3.0, 4.0);
select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
select numrange(1.0, 3.0,'()') << numrange(3.0, 4.0,'()');
select numrange(1.0, 2.0) >> numrange(3.0, 4.0);
+select numrange(1.0, 2.0) >> 3.0;
+select 2.0 >> numrange(3.0, 4.0);
select numrange(3.0, 70.0) &< numrange(6.6, 100.0);
+select numrange(3.0, 70.0) &< 6.6;
+select 70.0 &< numrange(6.6, 100.0);
+select numrange(3.0, 70.0) &> numrange(6.6, 100.0);
+select numrange(3.0, 70.0) &> 6.6;
+select 70.0 &> numrange(6.6, 100.0);
select numrange(1.1, 2.2) < numrange(1.0, 200.2);
select numrange(1.1, 2.2) < numrange(1.1, 1.2);
@@ -194,10 +206,15 @@
select count(*) from test_range_gist where ir && int4range(10,20);
select count(*) from test_range_gist where ir <@ int4range(10,50);
select count(*) from test_range_gist where ir << int4range(100,500);
+select count(*) from test_range_gist where ir << 100;
select count(*) from test_range_gist where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir >> 100;
select count(*) from test_range_gist where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &< 100;
select count(*) from test_range_gist where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir &> 100;
select count(*) from test_range_gist where ir -|- int4range(100,500);
+select count(*) from test_range_gist where ir -|- 100;
-- now check same queries using index
SET enable_seqscan = f;
@@ -211,10 +228,15 @@
select count(*) from test_range_gist where ir && int4range(10,20);
select count(*) from test_range_gist where ir <@ int4range(10,50);
select count(*) from test_range_gist where ir << int4range(100,500);
+select count(*) from test_range_gist where ir << 100;
select count(*) from test_range_gist where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir >> 100;
select count(*) from test_range_gist where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &< 100;
select count(*) from test_range_gist where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir &> 100;
select count(*) from test_range_gist where ir -|- int4range(100,500);
+select count(*) from test_range_gist where ir -|- 100;
-- now check same queries using a bulk-loaded index
drop index test_range_gist_idx;
@@ -227,10 +249,15 @@
select count(*) from test_range_gist where ir && int4range(10,20);
select count(*) from test_range_gist where ir <@ int4range(10,50);
select count(*) from test_range_gist where ir << int4range(100,500);
+select count(*) from test_range_gist where ir << 100;
select count(*) from test_range_gist where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir >> 100;
select count(*) from test_range_gist where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &< 100;
select count(*) from test_range_gist where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir &> 100;
select count(*) from test_range_gist where ir -|- int4range(100,500);
+select count(*) from test_range_gist where ir -|- 100;
-- test SP-GiST index that's been built incrementally
create table test_range_spgist(ir int4range);
@@ -256,10 +283,15 @@
select count(*) from test_range_spgist where ir && int4range(10,20);
select count(*) from test_range_spgist where ir <@ int4range(10,50);
select count(*) from test_range_spgist where ir << int4range(100,500);
+select count(*) from test_range_spgist where ir << 100;
select count(*) from test_range_spgist where ir >> int4range(100,500);
+select count(*) from test_range_spgist where ir >> 100;
select count(*) from test_range_spgist where ir &< int4range(100,500);
+select count(*) from test_range_spgist where ir &< 100;
select count(*) from test_range_spgist where ir &> int4range(100,500);
+select count(*) from test_range_spgist where ir &> 100;
select count(*) from test_range_spgist where ir -|- int4range(100,500);
+select count(*) from test_range_spgist where ir -|- 100;
-- now check same queries using index
SET enable_seqscan = f;
@@ -273,10 +305,15 @@
select count(*) from test_range_spgist where ir && int4range(10,20);
select count(*) from test_range_spgist where ir <@ int4range(10,50);
select count(*) from test_range_spgist where ir << int4range(100,500);
+select count(*) from test_range_spgist where ir << 100;
select count(*) from test_range_spgist where ir >> int4range(100,500);
+select count(*) from test_range_spgist where ir >> 100;
select count(*) from test_range_spgist where ir &< int4range(100,500);
+select count(*) from test_range_spgist where ir &< 100;
select count(*) from test_range_spgist where ir &> int4range(100,500);
+select count(*) from test_range_spgist where ir &> 100;
select count(*) from test_range_spgist where ir -|- int4range(100,500);
+select count(*) from test_range_spgist where ir -|- 100;
-- now check same queries using a bulk-loaded index
drop index test_range_spgist_idx;
@@ -289,10 +326,15 @@
select count(*) from test_range_spgist where ir && int4range(10,20);
select count(*) from test_range_spgist where ir <@ int4range(10,50);
select count(*) from test_range_spgist where ir << int4range(100,500);
+select count(*) from test_range_spgist where ir << 100;
select count(*) from test_range_spgist where ir >> int4range(100,500);
+select count(*) from test_range_spgist where ir >> 100;
select count(*) from test_range_spgist where ir &< int4range(100,500);
+select count(*) from test_range_spgist where ir &< 100;
select count(*) from test_range_spgist where ir &> int4range(100,500);
+select count(*) from test_range_spgist where ir &> 100;
select count(*) from test_range_spgist where ir -|- int4range(100,500);
+select count(*) from test_range_spgist where ir -|- 100;
-- test index-only scans
explain (costs off)
