Contains and is contained by operators of inet datatypes
Started by Emre Hasegeliover 9 years ago10 messages
Attached patch adds <@, @>, <<@, and @>> operator symbols for inet
datatype to replace <<=, >>=, <<, and >>. <@ and @> symbols are used
for containment for all datatypes except inet, particularly on the
geometric types, arrays; cube, hstore, intaray, ltree extensions.
<@ and @> symbols are standardised as on version 8.2 by Tom Lane at
2006 [1]/messages/by-id/14277.1157304939@sss.pgh.pa.us. The previous symbols are left in-place but deprecated. The
patch does exactly the same for inet datatypes.
The << and >> are standard symbols for strictly left of and strictly
right of operators. Those operators would also make sense for inet
datatypes. If we make this change now; we can remove the symbols, and
reuse them for new operators in distant future.
The patch removes the recently committed SP-GiST index support for the
existing operator symbols to give move reason to the users to use the
new symbols. This change will also indirectly deprecate the
undocumented non-transparent btree index support that works sometimes
for some of the existing operators [2]/messages/by-id/389.1042992616@sss.pgh.pa.us.
The patch includes changes on the regression tests and the
documentation. I will add it to 2016-11 Commitfest.
[1]: /messages/by-id/14277.1157304939@sss.pgh.pa.us
[2]: /messages/by-id/389.1042992616@sss.pgh.pa.us
Attachments:
0001-inet-contain-op-v1.patchapplication/octet-stream; name=0001-inet-contain-op-v1.patchDownload
From 61aa5bfdb0bf7a111674d731a85fe386dd30d96d Mon Sep 17 00:00:00 2001
From: Emre Hasegeli <emre@hasegeli.com>
Date: Wed, 31 Aug 2016 09:58:18 +0200
Subject: [PATCH] inet-contain-op-v1
---
doc/src/sgml/brin.sgml | 14 +++++---
doc/src/sgml/func.sgml | 35 +++++++++++++-------
doc/src/sgml/gist.sgml | 16 +++++----
doc/src/sgml/spgist.sgml | 12 +++----
src/backend/utils/adt/network.c | 10 +++---
src/backend/utils/adt/network_gist.c | 12 +++++++
src/backend/utils/adt/network_selfuncs.c | 8 +++--
src/backend/utils/adt/network_spgist.c | 8 ++---
src/include/access/stratnum.h | 4 +--
src/include/catalog/pg_amop.h | 20 ++++++++----
src/include/catalog/pg_operator.h | 34 ++++++++++++-------
src/include/catalog/pg_proc.h | 10 +++---
src/include/utils/builtins.h | 10 +++---
src/test/regress/expected/brin.out | 52 ++++++++++++++++++++---------
src/test/regress/expected/inet.out | 56 +++++++++++++++++++++++++-------
src/test/regress/expected/opr_sanity.out | 19 ++++++-----
src/test/regress/sql/brin.sql | 52 ++++++++++++++++++++---------
src/test/regress/sql/inet.sql | 20 +++++++-----
18 files changed, 263 insertions(+), 129 deletions(-)
diff --git a/doc/src/sgml/brin.sgml b/doc/src/sgml/brin.sgml
index f519285..508bbce 100644
--- a/doc/src/sgml/brin.sgml
+++ b/doc/src/sgml/brin.sgml
@@ -230,29 +230,33 @@
<entry><type>inet</type></entry>
<entry>
<literal><</literal>
<literal><=</literal>
<literal>=</literal>
<literal>>=</literal>
<literal>></literal>
</entry>
</row>
<row>
- <entry><literal>network_inclusion_ops</literal></entry>
+ <entry><literal>inet_inclusion_ops</literal></entry>
<entry><type>inet</type></entry>
<entry>
- <literal>&&</>
- <literal>>>=</>
- <literal><<=</literal>
+ <literal>&&</literal>
+ <literal>@></literal>
+ <literal><@</literal>
+ <literal>@>></literal>
+ <literal><<@</literal>
<literal>=</literal>
- <literal>>></>
<literal><<</literal>
+ <literal><<=</literal>
+ <literal>>></literal>
+ <literal>>>=</literal>
</entry>
</row>
<row>
<entry><literal>int4_minmax_ops</literal></entry>
<entry><type>integer</type></entry>
<entry>
<literal><</literal>
<literal><=</literal>
<literal>=</literal>
<literal>>=</literal>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a588350..1aeb12d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8913,38 +8913,38 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<entry> <literal>></literal> </entry>
<entry>is greater than</entry>
<entry><literal>inet '192.168.1.5' > inet '192.168.1.4'</literal></entry>
</row>
<row>
<entry> <literal><></literal> </entry>
<entry>is not equal</entry>
<entry><literal>inet '192.168.1.5' <> inet '192.168.1.4'</literal></entry>
</row>
<row>
- <entry> <literal><<</literal> </entry>
- <entry>is contained by</entry>
- <entry><literal>inet '192.168.1.5' << inet '192.168.1/24'</literal></entry>
+ <entry> <literal><<@</literal> </entry>
+ <entry>is contained by and smaller network (subnet)</entry>
+ <entry><literal>inet '192.168.1.5' <<@ inet '192.168.1/24'</literal></entry>
</row>
<row>
- <entry> <literal><<=</literal> </entry>
- <entry>is contained by or equals</entry>
- <entry><literal>inet '192.168.1/24' <<= inet '192.168.1/24'</literal></entry>
+ <entry> <literal><@</literal> </entry>
+ <entry>is contained by (can have the same masklen)</entry>
+ <entry><literal>inet '192.168.1/24' <@ inet '192.168.1/24'</literal></entry>
</row>
<row>
- <entry> <literal>>></literal> </entry>
- <entry>contains</entry>
- <entry><literal>inet '192.168.1/24' >> inet '192.168.1.5'</literal></entry>
+ <entry> <literal>@>></literal> </entry>
+ <entry>contains and bigger network (supernet)</entry>
+ <entry><literal>inet '192.168.1/24' @>> inet '192.168.1.5'</literal></entry>
</row>
<row>
- <entry> <literal>>>=</literal> </entry>
- <entry>contains or equals</entry>
- <entry><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></entry>
+ <entry> <literal>@></literal> </entry>
+ <entry>contains (can have the same masklen)</entry>
+ <entry><literal>inet '192.168.1/24' @> inet '192.168.1/24'</literal></entry>
</row>
<row>
<entry> <literal>&&</literal> </entry>
<entry>contains or is contained by</entry>
<entry><literal>inet '192.168.1/24' && inet '192.168.1.80/28'</literal></entry>
</row>
<row>
<entry> <literal>~</literal> </entry>
<entry>bitwise NOT</entry>
<entry><literal>~ inet '192.168.1.6'</literal></entry>
@@ -8971,20 +8971,31 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</row>
<row>
<entry> <literal>-</literal> </entry>
<entry>subtraction</entry>
<entry><literal>inet '192.168.1.43' - inet '192.168.1.19'</literal></entry>
</row>
</tbody>
</tgroup>
</table>
+ <note>
+ <para>
+ Before <productname>PostgreSQL</productname> 10, the containment
+ operators <literal><@</>, <literal>@></>, <literal><<@</>,
+ and <literal>@>></> were respectively called
+ <literal><<=</>, <literal>>>=</>, <literal><<</>,
+ and <literal>>></>. These names are still available, but are
+ deprecated and will eventually be removed.
+ </para>
+ </note>
+
<para>
<xref linkend="cidr-inet-functions-table"> shows the functions
available for use with the <type>cidr</type> and <type>inet</type>
types. The <function>abbrev</function>, <function>host</function>,
and <function>text</function>
functions are primarily intended to offer alternative display
formats.
</para>
<table id="cidr-inet-functions-table">
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index b3cc347..7a2dada 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -106,30 +106,34 @@
</entry>
<entry>
<literal><-></>
</entry>
</row>
<row>
<entry><literal>inet_ops</></entry>
<entry><type>inet</>, <type>cidr</></entry>
<entry>
<literal>&&</>
- <literal>>></>
- <literal>>>=</>
+ <literal>@></>
+ <literal><@</>
+ <literal>@>></>
+ <literal><<@</>
+ <literal>=</>
+ <literal><></>
<literal>></>
<literal>>=</>
- <literal><></>
- <literal><<</>
- <literal><<=</>
<literal><</>
<literal><=</>
- <literal>=</>
+ <literal><<</>
+ <literal><<=</>
+ <literal>>></>
+ <literal>>>=</>
</entry>
<entry>
</entry>
</row>
<row>
<entry><literal>point_ops</></entry>
<entry><type>point</></entry>
<entry>
<literal>>></>
<literal>>^</>
diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml
index cd4a8d0..7867dd7 100644
--- a/doc/src/sgml/spgist.sgml
+++ b/doc/src/sgml/spgist.sgml
@@ -143,30 +143,30 @@
<literal>~<~</>
<literal>~>=~</>
<literal>~>~</>
</entry>
</row>
<row>
<entry><literal>inet_ops</></entry>
<entry><type>inet</>, <type>cidr</></entry>
<entry>
<literal>&&</>
- <literal>>></>
- <literal>>>=</>
+ <literal>@></>
+ <literal><@</>
+ <literal>@>></>
+ <literal><<@</>
+ <literal>=</>
+ <literal><></>
<literal>></>
<literal>>=</>
- <literal><></>
- <literal><<</>
- <literal><<=</>
<literal><</>
<literal><=</>
- <literal>=</>
</entry>
</row>
</tbody>
</tgroup>
</table>
<para>
Of the two operator classes for type <type>point</>,
<literal>quad_point_ops</> is the default. <literal>kd_point_ops</>
supports the same operators but uses a different index data structure which
diff --git a/src/backend/utils/adt/network.c b/src/backend/utils/adt/network.c
index dbc557e..8b654b5 100644
--- a/src/backend/utils/adt/network.c
+++ b/src/backend/utils/adt/network.c
@@ -483,81 +483,81 @@ hashinet(PG_FUNCTION_ARGS)
int addrsize = ip_addrsize(addr);
/* XXX this assumes there are no pad bytes in the data structure */
return hash_any((unsigned char *) VARDATA_ANY(addr), addrsize + 2);
}
/*
* Boolean network-inclusion tests.
*/
Datum
-network_sub(PG_FUNCTION_ARGS)
+network_subnet(PG_FUNCTION_ARGS)
{
inet *a1 = PG_GETARG_INET_PP(0);
inet *a2 = PG_GETARG_INET_PP(1);
if (ip_family(a1) == ip_family(a2))
{
PG_RETURN_BOOL(ip_bits(a1) > ip_bits(a2) &&
bitncmp(ip_addr(a1), ip_addr(a2), ip_bits(a2)) == 0);
}
PG_RETURN_BOOL(false);
}
Datum
-network_subeq(PG_FUNCTION_ARGS)
+network_contained(PG_FUNCTION_ARGS)
{
inet *a1 = PG_GETARG_INET_PP(0);
inet *a2 = PG_GETARG_INET_PP(1);
if (ip_family(a1) == ip_family(a2))
{
PG_RETURN_BOOL(ip_bits(a1) >= ip_bits(a2) &&
bitncmp(ip_addr(a1), ip_addr(a2), ip_bits(a2)) == 0);
}
PG_RETURN_BOOL(false);
}
Datum
-network_sup(PG_FUNCTION_ARGS)
+network_supernet(PG_FUNCTION_ARGS)
{
inet *a1 = PG_GETARG_INET_PP(0);
inet *a2 = PG_GETARG_INET_PP(1);
if (ip_family(a1) == ip_family(a2))
{
PG_RETURN_BOOL(ip_bits(a1) < ip_bits(a2) &&
bitncmp(ip_addr(a1), ip_addr(a2), ip_bits(a1)) == 0);
}
PG_RETURN_BOOL(false);
}
Datum
-network_supeq(PG_FUNCTION_ARGS)
+network_contains(PG_FUNCTION_ARGS)
{
inet *a1 = PG_GETARG_INET_PP(0);
inet *a2 = PG_GETARG_INET_PP(1);
if (ip_family(a1) == ip_family(a2))
{
PG_RETURN_BOOL(ip_bits(a1) <= ip_bits(a2) &&
bitncmp(ip_addr(a1), ip_addr(a2), ip_bits(a1)) == 0);
}
PG_RETURN_BOOL(false);
}
Datum
-network_overlap(PG_FUNCTION_ARGS)
+network_overlaps(PG_FUNCTION_ARGS)
{
inet *a1 = PG_GETARG_INET_PP(0);
inet *a2 = PG_GETARG_INET_PP(1);
if (ip_family(a1) == ip_family(a2))
{
PG_RETURN_BOOL(bitncmp(ip_addr(a1), ip_addr(a2),
Min(ip_bits(a1), ip_bits(a2))) == 0);
}
diff --git a/src/backend/utils/adt/network_gist.c b/src/backend/utils/adt/network_gist.c
index 2caff94..c994b92a 100644
--- a/src/backend/utils/adt/network_gist.c
+++ b/src/backend/utils/adt/network_gist.c
@@ -55,23 +55,27 @@
* Operator strategy numbers used in the GiST inet_ops opclass
*/
#define INETSTRAT_OVERLAPS RTOverlapStrategyNumber
#define INETSTRAT_EQ RTEqualStrategyNumber
#define INETSTRAT_NE RTNotEqualStrategyNumber
#define INETSTRAT_LT RTLessStrategyNumber
#define INETSTRAT_LE RTLessEqualStrategyNumber
#define INETSTRAT_GT RTGreaterStrategyNumber
#define INETSTRAT_GE RTGreaterEqualStrategyNumber
#define INETSTRAT_SUB RTSubStrategyNumber
+#define INETSTRAT_SUBNET RTOldContainedByStrategyNumber
#define INETSTRAT_SUBEQ RTSubEqualStrategyNumber
+#define INETSTRAT_CONTAINED RTContainedByStrategyNumber
#define INETSTRAT_SUP RTSuperStrategyNumber
+#define INETSTRAT_SUPERNET RTOldContainsStrategyNumber
#define INETSTRAT_SUPEQ RTSuperEqualStrategyNumber
+#define INETSTRAT_CONTAINS RTContainsStrategyNumber
/*
* Representation of a GiST INET/CIDR index key. This is not identical to
* INET/CIDR because we need to keep track of the length of the common address
* prefix as well as the minimum netmask length. However, as long as it
* follows varlena header rules, the core GiST code won't know the difference.
* For simplicity we always use 1-byte-header varlena format.
*/
typedef struct GistInetKey
@@ -169,36 +173,40 @@ inet_gist_consistent(PG_FUNCTION_ARGS)
* Check 2: network bit count
*
* Network bit count (ip_bits) helps to check leaves for sub network and
* sup network operators. At non-leaf nodes, we know every child value
* has ip_bits >= gk_ip_minbits(key), so we can avoid descending in some
* cases too.
*/
switch (strategy)
{
case INETSTRAT_SUB:
+ case INETSTRAT_SUBNET:
if (GIST_LEAF(ent) && gk_ip_minbits(key) <= ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUBEQ:
+ case INETSTRAT_CONTAINED:
if (GIST_LEAF(ent) && gk_ip_minbits(key) < ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUPEQ:
+ case INETSTRAT_CONTAINS:
case INETSTRAT_EQ:
if (gk_ip_minbits(key) > ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUP:
+ case INETSTRAT_SUPERNET:
if (gk_ip_minbits(key) >= ip_bits(query))
PG_RETURN_BOOL(false);
break;
}
/*
* Check 3: common network bits
*
* Compare available common prefix bits to the query, but not beyond
* either the query's netmask or the minimum netmask among the represented
@@ -210,24 +218,28 @@ inet_gist_consistent(PG_FUNCTION_ARGS)
* network part of the address.
*/
minbits = Min(gk_ip_commonbits(key), gk_ip_minbits(key));
minbits = Min(minbits, ip_bits(query));
order = bitncmp(gk_ip_addr(key), ip_addr(query), minbits);
switch (strategy)
{
case INETSTRAT_SUB:
+ case INETSTRAT_SUBNET:
case INETSTRAT_SUBEQ:
+ case INETSTRAT_CONTAINED:
case INETSTRAT_OVERLAPS:
case INETSTRAT_SUPEQ:
+ case INETSTRAT_CONTAINS:
case INETSTRAT_SUP:
+ case INETSTRAT_SUPERNET:
PG_RETURN_BOOL(order == 0);
case INETSTRAT_LT:
case INETSTRAT_LE:
if (order > 0)
PG_RETURN_BOOL(false);
if (order < 0 || !GIST_LEAF(ent))
PG_RETURN_BOOL(true);
break;
diff --git a/src/backend/utils/adt/network_selfuncs.c b/src/backend/utils/adt/network_selfuncs.c
index 2e39687..bd369bc 100644
--- a/src/backend/utils/adt/network_selfuncs.c
+++ b/src/backend/utils/adt/network_selfuncs.c
@@ -29,21 +29,21 @@
/* Default selectivity for the inet overlap operator */
#define DEFAULT_OVERLAP_SEL 0.01
/* Default selectivity for the various inclusion operators */
#define DEFAULT_INCLUSION_SEL 0.005
/* Default selectivity for specified operator */
#define DEFAULT_SEL(operator) \
- ((operator) == OID_INET_OVERLAP_OP ? \
+ ((operator) == OID_INET_OVERLAPS_OP ? \
DEFAULT_OVERLAP_SEL : DEFAULT_INCLUSION_SEL)
/* Maximum number of items to consider in join selectivity calculations */
#define MAX_CONSIDERED_ELEMS 1024
static Selectivity networkjoinsel_inner(Oid operator,
VariableStatData *vardata1, VariableStatData *vardata2);
static Selectivity networkjoinsel_semi(Oid operator,
VariableStatData *vardata1, VariableStatData *vardata2);
static Selectivity mcv_population(float4 *mcv_numbers, int mcv_nvalues);
@@ -865,28 +865,32 @@ inet_semi_join_sel(Datum lhs_value,
* on the exact codes assigned here; but many other places in this file
* know that they can negate a code to obtain the code for the commutator
* operator.
*/
static int
inet_opr_codenum(Oid operator)
{
switch (operator)
{
case OID_INET_SUP_OP:
+ case OID_INET_SUPERNET_OP:
return -2;
case OID_INET_SUPEQ_OP:
+ case OID_INET_CONTAINS_OP:
return -1;
- case OID_INET_OVERLAP_OP:
+ case OID_INET_OVERLAPS_OP:
return 0;
+ case OID_INET_CONTAINED_OP:
case OID_INET_SUBEQ_OP:
return 1;
case OID_INET_SUB_OP:
+ case OID_INET_SUBNET_OP:
return 2;
default:
elog(ERROR, "unrecognized operator %u for inet selectivity",
operator);
}
return 0; /* unreached, but keep compiler quiet */
}
/*
* Comparison function for the subnet inclusion/overlap operators
diff --git a/src/backend/utils/adt/network_spgist.c b/src/backend/utils/adt/network_spgist.c
index a198a83..819d6fd 100644
--- a/src/backend/utils/adt/network_spgist.c
+++ b/src/backend/utils/adt/network_spgist.c
@@ -435,38 +435,38 @@ inet_spg_consistent_bitmap(const inet *prefix, int nkeys, ScanKey scankeys,
* value has greater ip_bits, so we can avoid descending in some cases
* too.
*
* This check is less expensive than checking the address bits, so we
* are doing this before, but it has to be done after for the basic
* comparison strategies, because ip_bits only affect their results
* when the common network bits are the same.
*/
switch (strategy)
{
- case RTSubStrategyNumber:
+ case RTOldContainedByStrategyNumber:
if (commonbits <= ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
break;
- case RTSubEqualStrategyNumber:
+ case RTContainedByStrategyNumber:
if (commonbits < ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
break;
- case RTSuperStrategyNumber:
+ case RTOldContainsStrategyNumber:
if (commonbits == ip_bits(argument) - 1)
bitmap &= 1 | (1 << 1);
else if (commonbits >= ip_bits(argument))
bitmap = 0;
break;
- case RTSuperEqualStrategyNumber:
+ case RTContainsStrategyNumber:
if (commonbits == ip_bits(argument))
bitmap &= 1 | (1 << 1);
else if (commonbits > ip_bits(argument))
bitmap = 0;
break;
case RTEqualStrategyNumber:
if (commonbits < ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
else if (commonbits == ip_bits(argument))
diff --git a/src/include/access/stratnum.h b/src/include/access/stratnum.h
index eabced5..2916815 100644
--- a/src/include/access/stratnum.h
+++ b/src/include/access/stratnum.h
@@ -57,19 +57,19 @@ typedef uint16 StrategyNumber;
#define RTOldContainedByStrategyNumber 14 /* for old spelling of <@ */
#define RTKNNSearchStrategyNumber 15 /* for <-> (distance) */
#define RTContainsElemStrategyNumber 16 /* for range types @> elem */
#define RTAdjacentStrategyNumber 17 /* for -|- */
#define RTEqualStrategyNumber 18 /* for = */
#define RTNotEqualStrategyNumber 19 /* for != */
#define RTLessStrategyNumber 20 /* for < */
#define RTLessEqualStrategyNumber 21 /* for <= */
#define RTGreaterStrategyNumber 22 /* for > */
#define RTGreaterEqualStrategyNumber 23 /* for >= */
-#define RTSubStrategyNumber 24 /* for inet >> */
+#define RTSubStrategyNumber 24 /* for inet << */
#define RTSubEqualStrategyNumber 25 /* for inet <<= */
-#define RTSuperStrategyNumber 26 /* for inet << */
+#define RTSuperStrategyNumber 26 /* for inet >> */
#define RTSuperEqualStrategyNumber 27 /* for inet >>= */
#define RTMaxStrategyNumber 27
#endif /* STRATNUM_H */
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index 15b6290..6b1d0bb 100644
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -844,45 +844,49 @@ DATA(insert ( 5000 603 603 7 s 498 4000 0 ));
DATA(insert ( 5000 603 603 8 s 497 4000 0 ));
DATA(insert ( 5000 603 603 9 s 2571 4000 0 ));
DATA(insert ( 5000 603 603 10 s 2570 4000 0 ));
DATA(insert ( 5000 603 603 11 s 2573 4000 0 ));
DATA(insert ( 5000 603 603 12 s 2572 4000 0 ));
/*
* GiST inet_ops
*/
DATA(insert ( 3550 869 869 3 s 3552 783 0 ));
+DATA(insert ( 3550 869 869 7 s 3453 783 0 ));
+DATA(insert ( 3550 869 869 8 s 3451 783 0 ));
+DATA(insert ( 3550 869 869 13 s 3452 783 0 ));
+DATA(insert ( 3550 869 869 14 s 3450 783 0 ));
DATA(insert ( 3550 869 869 18 s 1201 783 0 ));
DATA(insert ( 3550 869 869 19 s 1202 783 0 ));
DATA(insert ( 3550 869 869 20 s 1203 783 0 ));
DATA(insert ( 3550 869 869 21 s 1204 783 0 ));
DATA(insert ( 3550 869 869 22 s 1205 783 0 ));
DATA(insert ( 3550 869 869 23 s 1206 783 0 ));
DATA(insert ( 3550 869 869 24 s 931 783 0 ));
DATA(insert ( 3550 869 869 25 s 932 783 0 ));
DATA(insert ( 3550 869 869 26 s 933 783 0 ));
DATA(insert ( 3550 869 869 27 s 934 783 0 ));
/*
* SP-GiST inet_ops
*/
DATA(insert ( 3794 869 869 3 s 3552 4000 0 ));
+DATA(insert ( 3794 869 869 7 s 3453 4000 0 ));
+DATA(insert ( 3794 869 869 8 s 3451 4000 0 ));
+DATA(insert ( 3794 869 869 13 s 3452 4000 0 ));
+DATA(insert ( 3794 869 869 14 s 3450 4000 0 ));
DATA(insert ( 3794 869 869 18 s 1201 4000 0 ));
DATA(insert ( 3794 869 869 19 s 1202 4000 0 ));
DATA(insert ( 3794 869 869 20 s 1203 4000 0 ));
DATA(insert ( 3794 869 869 21 s 1204 4000 0 ));
DATA(insert ( 3794 869 869 22 s 1205 4000 0 ));
DATA(insert ( 3794 869 869 23 s 1206 4000 0 ));
-DATA(insert ( 3794 869 869 24 s 931 4000 0 ));
-DATA(insert ( 3794 869 869 25 s 932 4000 0 ));
-DATA(insert ( 3794 869 869 26 s 933 4000 0 ));
-DATA(insert ( 3794 869 869 27 s 934 4000 0 ));
/* BRIN opclasses */
/* minmax bytea */
DATA(insert ( 4064 17 17 1 s 1957 3580 0 ));
DATA(insert ( 4064 17 17 2 s 1958 3580 0 ));
DATA(insert ( 4064 17 17 3 s 1955 3580 0 ));
DATA(insert ( 4064 17 17 4 s 1960 3580 0 ));
DATA(insert ( 4064 17 17 5 s 1959 3580 0 ));
/* minmax "char" */
DATA(insert ( 4062 18 18 1 s 631 3580 0 ));
@@ -1002,25 +1006,29 @@ DATA(insert ( 4074 829 829 3 s 1220 3580 0 ));
DATA(insert ( 4074 829 829 4 s 1225 3580 0 ));
DATA(insert ( 4074 829 829 5 s 1224 3580 0 ));
/* minmax inet */
DATA(insert ( 4075 869 869 1 s 1203 3580 0 ));
DATA(insert ( 4075 869 869 2 s 1204 3580 0 ));
DATA(insert ( 4075 869 869 3 s 1201 3580 0 ));
DATA(insert ( 4075 869 869 4 s 1206 3580 0 ));
DATA(insert ( 4075 869 869 5 s 1205 3580 0 ));
/* inclusion inet */
DATA(insert ( 4102 869 869 3 s 3552 3580 0 ));
-DATA(insert ( 4102 869 869 7 s 934 3580 0 ));
-DATA(insert ( 4102 869 869 8 s 932 3580 0 ));
+DATA(insert ( 4102 869 869 7 s 3453 3580 0 ));
+DATA(insert ( 4102 869 869 8 s 3451 3580 0 ));
+DATA(insert ( 4102 869 869 13 s 3452 3580 0 ));
+DATA(insert ( 4102 869 869 14 s 3450 3580 0 ));
DATA(insert ( 4102 869 869 18 s 1201 3580 0 ));
DATA(insert ( 4102 869 869 24 s 933 3580 0 ));
+DATA(insert ( 4102 869 869 25 s 934 3580 0 ));
DATA(insert ( 4102 869 869 26 s 931 3580 0 ));
+DATA(insert ( 4102 869 869 27 s 932 3580 0 ));
/* minmax character */
DATA(insert ( 4076 1042 1042 1 s 1058 3580 0 ));
DATA(insert ( 4076 1042 1042 2 s 1059 3580 0 ));
DATA(insert ( 4076 1042 1042 3 s 1054 3580 0 ));
DATA(insert ( 4076 1042 1042 4 s 1061 3580 0 ));
DATA(insert ( 4076 1042 1042 5 s 1060 3580 0 ));
/* minmax time without time zone */
DATA(insert ( 4077 1083 1083 1 s 1110 3580 0 ));
DATA(insert ( 4077 1083 1083 2 s 1111 3580 0 ));
DATA(insert ( 4077 1083 1083 3 s 1108 3580 0 ));
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index b8f06b3..cb645b3 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -1142,35 +1142,47 @@ DESCR("equal");
DATA(insert OID = 1202 ( "<>" PGNSP PGUID b f f 869 869 16 1202 1201 network_ne neqsel neqjoinsel ));
DESCR("not equal");
DATA(insert OID = 1203 ( "<" PGNSP PGUID b f f 869 869 16 1205 1206 network_lt scalarltsel scalarltjoinsel ));
DESCR("less than");
DATA(insert OID = 1204 ( "<=" PGNSP PGUID b f f 869 869 16 1206 1205 network_le scalarltsel scalarltjoinsel ));
DESCR("less than or equal");
DATA(insert OID = 1205 ( ">" PGNSP PGUID b f f 869 869 16 1203 1204 network_gt scalargtsel scalargtjoinsel ));
DESCR("greater than");
DATA(insert OID = 1206 ( ">=" PGNSP PGUID b f f 869 869 16 1204 1203 network_ge scalargtsel scalargtjoinsel ));
DESCR("greater than or equal");
-DATA(insert OID = 931 ( "<<" PGNSP PGUID b f f 869 869 16 933 0 network_sub networksel networkjoinsel ));
-DESCR("is subnet");
+DATA(insert OID = 931 ( "<<" PGNSP PGUID b f f 869 869 16 933 0 network_subnet networksel networkjoinsel ));
+DESCR("deprecated, use <<@ instead");
#define OID_INET_SUB_OP 931
-DATA(insert OID = 932 ( "<<=" PGNSP PGUID b f f 869 869 16 934 0 network_subeq networksel networkjoinsel ));
-DESCR("is subnet or equal");
+DATA(insert OID = 3450 ( "<<@" PGNSP PGUID b f f 869 869 16 3452 0 network_subnet networksel networkjoinsel ));
+DESCR("is subnet");
+#define OID_INET_SUBNET_OP 3450
+DATA(insert OID = 932 ( "<<=" PGNSP PGUID b f f 869 869 16 934 0 network_contained networksel networkjoinsel ));
+DESCR("deprecated, use <@ instead");
#define OID_INET_SUBEQ_OP 932
-DATA(insert OID = 933 ( ">>" PGNSP PGUID b f f 869 869 16 931 0 network_sup networksel networkjoinsel ));
-DESCR("is supernet");
+DATA(insert OID = 3451 ( "<@" PGNSP PGUID b f f 869 869 16 3453 0 network_contained networksel networkjoinsel ));
+DESCR("is contained by");
+#define OID_INET_CONTAINED_OP 3451
+DATA(insert OID = 933 ( ">>" PGNSP PGUID b f f 869 869 16 931 0 network_supernet networksel networkjoinsel ));
+DESCR("deprecated, use @>> instead");
#define OID_INET_SUP_OP 933
-DATA(insert OID = 934 ( ">>=" PGNSP PGUID b f f 869 869 16 932 0 network_supeq networksel networkjoinsel ));
-DESCR("is supernet or equal");
+DATA(insert OID = 3452 ( "@>>" PGNSP PGUID b f f 869 869 16 3450 0 network_supernet networksel networkjoinsel ));
+DESCR("is supernet");
+#define OID_INET_SUPERNET_OP 3452
+DATA(insert OID = 934 ( ">>=" PGNSP PGUID b f f 869 869 16 932 0 network_contains networksel networkjoinsel ));
+DESCR("deprecated, use @> instead");
#define OID_INET_SUPEQ_OP 934
-DATA(insert OID = 3552 ( "&&" PGNSP PGUID b f f 869 869 16 3552 0 network_overlap networksel networkjoinsel ));
-DESCR("overlaps (is subnet or supernet)");
-#define OID_INET_OVERLAP_OP 3552
+DATA(insert OID = 3453 ( "@>" PGNSP PGUID b f f 869 869 16 3451 0 network_contains networksel networkjoinsel ));
+DESCR("contains");
+#define OID_INET_CONTAINS_OP 3453
+DATA(insert OID = 3552 ( "&&" PGNSP PGUID b f f 869 869 16 3552 0 network_overlaps networksel networkjoinsel ));
+DESCR("overlaps (contains or is contained by)");
+#define OID_INET_OVERLAPS_OP 3552
DATA(insert OID = 2634 ( "~" PGNSP PGUID l f f 0 869 869 0 0 inetnot - - ));
DESCR("bitwise not");
DATA(insert OID = 2635 ( "&" PGNSP PGUID b f f 869 869 869 0 0 inetand - - ));
DESCR("bitwise and");
DATA(insert OID = 2636 ( "|" PGNSP PGUID b f f 869 869 869 0 0 inetor - - ));
DESCR("bitwise or");
DATA(insert OID = 2637 ( "+" PGNSP PGUID b f f 869 20 869 2638 0 inetpl - - ));
DESCR("add");
DATA(insert OID = 2638 ( "+" PGNSP PGUID b f f 20 869 869 2637 0 int8pl_inet - - ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index e2d08ba..820f10a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2132,25 +2132,25 @@ DATA(insert OID = 921 ( network_lt PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0
DATA(insert OID = 922 ( network_le PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_le _null_ _null_ _null_ ));
DATA(insert OID = 923 ( network_gt PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_gt _null_ _null_ _null_ ));
DATA(insert OID = 924 ( network_ge PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_ge _null_ _null_ _null_ ));
DATA(insert OID = 925 ( network_ne PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_ne _null_ _null_ _null_ ));
DATA(insert OID = 3562 ( network_larger PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 869 "869 869" _null_ _null_ _null_ _null_ _null_ network_larger _null_ _null_ _null_ ));
DESCR("larger of two");
DATA(insert OID = 3563 ( network_smaller PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 869 "869 869" _null_ _null_ _null_ _null_ _null_ network_smaller _null_ _null_ _null_ ));
DESCR("smaller of two");
DATA(insert OID = 926 ( network_cmp PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 23 "869 869" _null_ _null_ _null_ _null_ _null_ network_cmp _null_ _null_ _null_ ));
DESCR("less-equal-greater");
-DATA(insert OID = 927 ( network_sub PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_sub _null_ _null_ _null_ ));
-DATA(insert OID = 928 ( network_subeq PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_subeq _null_ _null_ _null_ ));
-DATA(insert OID = 929 ( network_sup PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_sup _null_ _null_ _null_ ));
-DATA(insert OID = 930 ( network_supeq PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_supeq _null_ _null_ _null_ ));
-DATA(insert OID = 3551 ( network_overlap PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_overlap _null_ _null_ _null_ ));
+DATA(insert OID = 927 ( network_subnet PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_subnet _null_ _null_ _null_ ));
+DATA(insert OID = 928 ( network_contained PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_contained _null_ _null_ _null_ ));
+DATA(insert OID = 929 ( network_supernet PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_supernet _null_ _null_ _null_ ));
+DATA(insert OID = 930 ( network_contains PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_contains _null_ _null_ _null_ ));
+DATA(insert OID = 3551 ( network_overlaps PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ network_overlaps _null_ _null_ _null_ ));
/* inet/cidr functions */
DATA(insert OID = 598 ( abbrev PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "869" _null_ _null_ _null_ _null_ _null_ inet_abbrev _null_ _null_ _null_ ));
DESCR("abbreviated display of inet value");
DATA(insert OID = 599 ( abbrev PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "650" _null_ _null_ _null_ _null_ _null_ cidr_abbrev _null_ _null_ _null_ ));
DESCR("abbreviated display of cidr value");
DATA(insert OID = 605 ( set_masklen PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 869 "869 23" _null_ _null_ _null_ _null_ _null_ inet_set_masklen _null_ _null_ _null_ ));
DESCR("change netmask of inet");
DATA(insert OID = 635 ( set_masklen PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 650 "650 23" _null_ _null_ _null_ _null_ _null_ cidr_set_masklen _null_ _null_ _null_ ));
DESCR("change netmask of cidr");
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 2ae212a..144ee21 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -962,25 +962,25 @@ extern Datum cidr_send(PG_FUNCTION_ARGS);
extern Datum network_cmp(PG_FUNCTION_ARGS);
extern Datum network_lt(PG_FUNCTION_ARGS);
extern Datum network_le(PG_FUNCTION_ARGS);
extern Datum network_eq(PG_FUNCTION_ARGS);
extern Datum network_ge(PG_FUNCTION_ARGS);
extern Datum network_gt(PG_FUNCTION_ARGS);
extern Datum network_ne(PG_FUNCTION_ARGS);
extern Datum network_smaller(PG_FUNCTION_ARGS);
extern Datum network_larger(PG_FUNCTION_ARGS);
extern Datum hashinet(PG_FUNCTION_ARGS);
-extern Datum network_sub(PG_FUNCTION_ARGS);
-extern Datum network_subeq(PG_FUNCTION_ARGS);
-extern Datum network_sup(PG_FUNCTION_ARGS);
-extern Datum network_supeq(PG_FUNCTION_ARGS);
-extern Datum network_overlap(PG_FUNCTION_ARGS);
+extern Datum network_subnet(PG_FUNCTION_ARGS);
+extern Datum network_contained(PG_FUNCTION_ARGS);
+extern Datum network_supernet(PG_FUNCTION_ARGS);
+extern Datum network_contains(PG_FUNCTION_ARGS);
+extern Datum network_overlaps(PG_FUNCTION_ARGS);
extern Datum network_network(PG_FUNCTION_ARGS);
extern Datum network_netmask(PG_FUNCTION_ARGS);
extern Datum network_hostmask(PG_FUNCTION_ARGS);
extern Datum network_masklen(PG_FUNCTION_ARGS);
extern Datum network_family(PG_FUNCTION_ARGS);
extern Datum network_broadcast(PG_FUNCTION_ARGS);
extern Datum network_host(PG_FUNCTION_ARGS);
extern Datum network_show(PG_FUNCTION_ARGS);
extern Datum inet_abbrev(PG_FUNCTION_ARGS);
extern Datum cidr_abbrev(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index 21676e5..9a47b57 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -173,49 +173,69 @@ INSERT INTO brinopers VALUES
'{99, 100, 1, 100, 100}'),
('float8col', 'float8',
'{>, >=, =, <=, <}',
'{0, 0, 0, 1.98, 1.98}',
'{99, 100, 1, 100, 100}'),
('macaddrcol', 'macaddr',
'{>, >=, =, <=, <}',
'{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}',
'{99, 100, 2, 100, 100}'),
('inetcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2/10, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
+ ('inetcol', 'inet', -- Deprecated operators
+ '{>>=, >>, <<=, <<}',
+ '{10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{2, 2, 100, 100}'),
+ ('inetcol', 'inet', -- Basic comparison operators
+ '{=, <, <=, >, >=}',
+ '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{1, 100, 100, 125, 125}'),
('inetcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}', -- IPv6
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
+ ('inetcol', 'cidr', -- Cross data-type
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 10.0/9}',
+ '{100, 2, 100, 2, 100}'),
('inetcol', 'cidr',
- '{&&, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{<, <=, >, >=}',
+ '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{100, 100, 125, 125}'),
('inetcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2.14.231/8, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'inet',
+ '{&&, @>, <@, =}',
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'cidr',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'cidr',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('bpcharcol', 'bpchar',
'{>, >=, =, <=, <}',
'{A, A, W, Z, Z}',
'{97, 100, 6, 100, 98}'),
('datecol', 'date',
'{>, >=, =, <=, <}',
'{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
'{100, 100, 1, 100, 100}'),
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index be9427e..9930bd4 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -172,25 +172,25 @@ SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
WHERE c = i;
six | cidr | inet
-----+----------------+----------------
| 192.168.1.0/24 | 192.168.1.0/24
| 10.1.2.3/32 | 10.1.2.3
(2 rows)
SELECT '' AS ten, i, c,
i < c AS lt, i <= c AS le, i = c AS eq,
i >= c AS ge, i > c AS gt, i <> c AS ne,
- i << c AS sb, i <<= c AS sbe,
- i >> c AS sup, i >>= c AS spe,
+ i <<@ c AS sb, i <@ c AS cnd,
+ i @>> c AS sup, i @> c AS cns,
i && c AS ovr
FROM INET_TBL;
- ten | i | c | lt | le | eq | ge | gt | ne | sb | sbe | sup | spe | ovr
+ ten | i | c | lt | le | eq | ge | gt | ne | sb | cnd | sup | cns | ovr
-----+------------------+--------------------+----+----+----+----+----+----+----+-----+-----+-----+-----
| 192.168.1.226/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t | t
| 192.168.1.226 | 192.168.1.0/26 | f | f | f | t | t | t | f | f | f | f | f
| 192.168.1.0/24 | 192.168.1.0/24 | f | t | t | t | f | f | f | t | f | t | t
| 192.168.1.0/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f | t
| 192.168.1.255/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t | t
| 192.168.1.255/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f | t
| 10.1.2.3/8 | 10.0.0.0/8 | f | f | f | t | t | t | f | t | f | t | t
| 10.1.2.3/8 | 10.0.0.0/32 | t | t | f | f | f | t | f | f | t | t | t
| 10.1.2.3 | 10.1.2.3/32 | f | t | t | t | f | f | f | t | f | t | t
@@ -267,55 +267,87 @@ DROP INDEX inet_idx1;
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(3 rows)
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(3 rows)
+
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(6 rows)
+
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
(3 rows)
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+(3 rows)
+
SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
c | i
---+---
(0 rows)
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+---+---
+(0 rows)
+
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
10.1.2.3/32 | 10.1.2.3
@@ -407,29 +439,29 @@ SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
192.168.1.0/25
192.168.1.255/25
192.168.1.226
(3 rows)
SET enable_seqscan TO on;
DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
SET enable_seqscan TO off;
-SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(3 rows)
-SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
@@ -437,29 +469,29 @@ SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
-SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
(3 rows)
-SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
c | i
---+---
(0 rows)
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
@@ -533,30 +565,30 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
::ffff:1.2.3.4/128 | ::4.3.2.1/24
10:23::f1/128 | 10:23::f1/64
10:23::8000/113 | 10:23::ffff
(16 rows)
-- test index-only scans
EXPLAIN (COSTS OFF)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
- QUERY PLAN
----------------------------------------------------
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+ QUERY PLAN
+----------------------------------------------------
Sort
Sort Key: i
-> Index Only Scan using inet_idx3 on inet_tbl
- Index Cond: (i << '192.168.1.0/24'::inet)
+ Index Cond: (i <<@ '192.168.1.0/24'::inet)
(4 rows)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
i
------------------
192.168.1.0/25
192.168.1.255/25
192.168.1.226
(3 rows)
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
-- simple tests of inet boolean and arithmetic operators
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 0bcec13..4ec0402 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -953,37 +953,38 @@ ORDER BY 1, 2;
*< | *>
*<= | *>=
*<> | *<>
*= | *=
+ | +
-|- | -|-
< | >
<-> | <->
<< | >>
<<= | >>=
+ <<@ | @>>
<= | >=
<> | <>
<@ | @>
= | =
?# | ?#
?- | ?-
?-| | ?-|
?| | ?|
?|| | ?||
@ | ~
@@ | @@
@@@ | @@@
| | |
~<=~ | ~>=~
~<~ | ~>~
~= | ~=
-(30 rows)
+(31 rows)
-- Likewise for negator pairs.
SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
FROM pg_operator o1, pg_operator o2
WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname
ORDER BY 1, 2;
op1 | op2
------+------
!~ | ~
!~* | ~*
@@ -1731,21 +1732,23 @@ ORDER BY 1, 2, 3;
783 | 6 | -|-
783 | 6 | ~=
783 | 7 | @>
783 | 8 | <@
783 | 9 | &<|
783 | 10 | <<|
783 | 10 | <^
783 | 11 | >^
783 | 11 | |>>
783 | 12 | |&>
+ 783 | 13 | @>>
783 | 13 | ~
+ 783 | 14 | <<@
783 | 14 | @
783 | 15 | <->
783 | 16 | @>
783 | 18 | =
783 | 19 | <>
783 | 20 | <
783 | 21 | <=
783 | 22 | >
783 | 23 | >=
783 | 24 | <<
@@ -1769,37 +1772,39 @@ ORDER BY 1, 2, 3;
3580 | 1 | <<
3580 | 2 | &<
3580 | 2 | <=
3580 | 3 | &&
3580 | 3 | =
3580 | 4 | &>
3580 | 4 | >=
3580 | 5 | >
3580 | 5 | >>
3580 | 6 | ~=
- 3580 | 7 | >>=
3580 | 7 | @>
- 3580 | 8 | <<=
3580 | 8 | <@
3580 | 9 | &<|
3580 | 10 | <<|
3580 | 11 | |>>
3580 | 12 | |&>
+ 3580 | 13 | @>>
+ 3580 | 14 | <<@
3580 | 16 | @>
3580 | 17 | -|-
3580 | 18 | =
3580 | 20 | <
3580 | 21 | <=
3580 | 22 | >
3580 | 23 | >=
3580 | 24 | >>
+ 3580 | 25 | >>=
3580 | 26 | <<
+ 3580 | 27 | <<=
4000 | 1 | <<
4000 | 1 | ~<~
4000 | 2 | &<
4000 | 2 | ~<=~
4000 | 3 | &&
4000 | 3 | =
4000 | 4 | &>
4000 | 4 | ~>=~
4000 | 5 | >>
4000 | 5 | ~>~
@@ -1808,34 +1813,32 @@ ORDER BY 1, 2, 3;
4000 | 7 | @>
4000 | 8 | <@
4000 | 9 | &<|
4000 | 10 | <<|
4000 | 10 | <^
4000 | 11 | <
4000 | 11 | >^
4000 | 11 | |>>
4000 | 12 | <=
4000 | 12 | |&>
+ 4000 | 13 | @>>
+ 4000 | 14 | <<@
4000 | 14 | >=
4000 | 15 | >
4000 | 16 | @>
4000 | 18 | =
4000 | 19 | <>
4000 | 20 | <
4000 | 21 | <=
4000 | 22 | >
4000 | 23 | >=
- 4000 | 24 | <<
- 4000 | 25 | <<=
- 4000 | 26 | >>
- 4000 | 27 | >>=
-(121 rows)
+(123 rows)
-- Check that all opclass search operators have selectivity estimators.
-- This is not absolutely required, but it seems a reasonable thing
-- to insist on for all standard datatypes.
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
(p2.oprrest = 0 OR p2.oprjoin = 0);
amopfamily | amopopr | oid | oprname
------------+---------+-----+---------
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index e7f6f77..939cfdb 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -178,49 +178,69 @@ INSERT INTO brinopers VALUES
'{99, 100, 1, 100, 100}'),
('float8col', 'float8',
'{>, >=, =, <=, <}',
'{0, 0, 0, 1.98, 1.98}',
'{99, 100, 1, 100, 100}'),
('macaddrcol', 'macaddr',
'{>, >=, =, <=, <}',
'{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}',
'{99, 100, 2, 100, 100}'),
('inetcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2/10, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
+ ('inetcol', 'inet', -- Deprecated operators
+ '{>>=, >>, <<=, <<}',
+ '{10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{2, 2, 100, 100}'),
+ ('inetcol', 'inet', -- Basic comparison operators
+ '{=, <, <=, >, >=}',
+ '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{1, 100, 100, 125, 125}'),
('inetcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}', -- IPv6
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
+ ('inetcol', 'cidr', -- Cross data-type
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 10.0/9}',
+ '{100, 2, 100, 2, 100}'),
('inetcol', 'cidr',
- '{&&, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{<, <=, >, >=}',
+ '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{100, 100, 125, 125}'),
('inetcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2.14.231/8, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'inet',
+ '{&&, @>, <@, =}',
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'cidr',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'cidr',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('bpcharcol', 'bpchar',
'{>, >=, =, <=, <}',
'{A, A, W, Z, Z}',
'{97, 100, 6, 100, 98}'),
('datecol', 'date',
'{>, >=, =, <=, <}',
'{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
'{100, 100, 1, 100, 100}'),
diff --git a/src/test/regress/sql/inet.sql b/src/test/regress/sql/inet.sql
index 880e115..58db04b 100644
--- a/src/test/regress/sql/inet.sql
+++ b/src/test/regress/sql/inet.sql
@@ -44,22 +44,22 @@ SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)",
SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)",
i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL
WHERE masklen(c) <= 8;
SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
WHERE c = i;
SELECT '' AS ten, i, c,
i < c AS lt, i <= c AS le, i = c AS eq,
i >= c AS ge, i > c AS gt, i <> c AS ne,
- i << c AS sb, i <<= c AS sbe,
- i >> c AS sup, i >>= c AS spe,
+ i <<@ c AS sb, i <@ c AS cnd,
+ i @>> c AS sup, i @> c AS cns,
i && c AS ovr
FROM INET_TBL;
SELECT max(i) AS max, min(i) AS min FROM INET_TBL;
SELECT max(c) AS max, min(c) AS min FROM INET_TBL;
-- check the conversion to/from text and set_netmask
SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
-- check that btree index works correctly
@@ -67,58 +67,62 @@ CREATE INDEX inet_idx1 ON inet_tbl(i);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
SET enable_seqscan TO on;
DROP INDEX inet_idx1;
-- check that gist index works correctly
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
-- test index-only scans
EXPLAIN (COSTS OFF)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SET enable_seqscan TO on;
DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
SET enable_seqscan TO off;
-SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
-- test index-only scans
EXPLAIN (COSTS OFF)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
-- simple tests of inet boolean and arithmetic operators
SELECT i, ~i AS "~i" FROM inet_tbl;
SELECT i, c, i & c AS "and" FROM inet_tbl;
SELECT i, c, i | c AS "or" FROM inet_tbl;
SELECT i, i + 500 AS "i+500" FROM inet_tbl;
SELECT i, i - 500 AS "i-500" FROM inet_tbl;
--
2.7.4 (Apple Git-66)
Re: Contains and is contained by operators of inet datatypes
Review
- Applies and passes the test suite.
- I think this is a good change since it increases the consistency of
the operators. I also like the choice of <<@ and @>> since they feel
intuitive to me.
- I tested it and both old and new operators use the brin and gist indexes.
- The new spgist index does not support the old deprecated operators,
which is intentional. I do not have a strong opinion here either way but
some people may find this surprising.
- I am not convinced that your changes to the descriptions of the
operators necessarily make things clearer. For example "is contained by
and smaller network (subnet)" only mentions subnets and not IP-addresses.
- Maybe change "deprecated and will eventually be removed." to
"deprecated and may be removed in a future release.". I prefer that
latter wording but I am fine with either.
- Won't renaming the functions which implement risk breaking people's
applications? While the new names are a bit nicer I am not sure it is
worth doing.
- The changes to the code look generally good.
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Contains and is contained by operators of inet datatypes
- I am not convinced that your changes to the descriptions of the operators
necessarily make things clearer. For example "is contained by and smaller
network (subnet)" only mentions subnets and not IP-addresses.
I was trying to avoid confusion. <@ is the "contained by" operator
which is also returning true when both sides are equal. We shouldn't
continue calling <<@ also "contained by". I removed the "(subnet)"
and "(supernet)" additions. Can you think of any better wording?
- Maybe change "deprecated and will eventually be removed." to "deprecated
and may be removed in a future release.". I prefer that latter wording but I
am fine with either.
I copied that note from the Geometric Functions and Operators page.
- Won't renaming the functions which implement risk breaking people's
applications? While the new names are a bit nicer I am not sure it is worth
doing.
You are right. I reverted that part.
- The changes to the code look generally good.
Thank you for the review. New version is attached.
Attachments:
0001-inet-contain-op-v2.patchapplication/octet-stream; name=0001-inet-contain-op-v2.patchDownload
From 9c1cb2d7284c14952eb79feb52cdb176c697c326 Mon Sep 17 00:00:00 2001
From: Emre Hasegeli <emre@hasegeli.com>
Date: Wed, 31 Aug 2016 09:58:18 +0200
Subject: [PATCH] inet-contain-op-v2
---
doc/src/sgml/brin.sgml | 14 +++++---
doc/src/sgml/func.sgml | 35 +++++++++++++-------
doc/src/sgml/gist.sgml | 16 +++++----
doc/src/sgml/spgist.sgml | 12 +++----
src/backend/utils/adt/network_gist.c | 12 +++++++
src/backend/utils/adt/network_selfuncs.c | 8 +++--
src/backend/utils/adt/network_spgist.c | 8 ++---
src/include/access/stratnum.h | 4 +--
src/include/catalog/pg_amop.h | 20 ++++++++----
src/include/catalog/pg_operator.h | 26 +++++++++++----
src/test/regress/expected/brin.out | 52 ++++++++++++++++++++---------
src/test/regress/expected/inet.out | 56 +++++++++++++++++++++++++-------
src/test/regress/expected/opr_sanity.out | 19 ++++++-----
src/test/regress/sql/brin.sql | 52 ++++++++++++++++++++---------
src/test/regress/sql/inet.sql | 20 +++++++-----
15 files changed, 244 insertions(+), 110 deletions(-)
diff --git a/doc/src/sgml/brin.sgml b/doc/src/sgml/brin.sgml
index f519285..508bbce 100644
--- a/doc/src/sgml/brin.sgml
+++ b/doc/src/sgml/brin.sgml
@@ -230,29 +230,33 @@
<entry><type>inet</type></entry>
<entry>
<literal><</literal>
<literal><=</literal>
<literal>=</literal>
<literal>>=</literal>
<literal>></literal>
</entry>
</row>
<row>
- <entry><literal>network_inclusion_ops</literal></entry>
+ <entry><literal>inet_inclusion_ops</literal></entry>
<entry><type>inet</type></entry>
<entry>
- <literal>&&</>
- <literal>>>=</>
- <literal><<=</literal>
+ <literal>&&</literal>
+ <literal>@></literal>
+ <literal><@</literal>
+ <literal>@>></literal>
+ <literal><<@</literal>
<literal>=</literal>
- <literal>>></>
<literal><<</literal>
+ <literal><<=</literal>
+ <literal>>></literal>
+ <literal>>>=</literal>
</entry>
</row>
<row>
<entry><literal>int4_minmax_ops</literal></entry>
<entry><type>integer</type></entry>
<entry>
<literal><</literal>
<literal><=</literal>
<literal>=</literal>
<literal>>=</literal>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2e64cc4..0f003ef 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8913,38 +8913,38 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<entry> <literal>></literal> </entry>
<entry>is greater than</entry>
<entry><literal>inet '192.168.1.5' > inet '192.168.1.4'</literal></entry>
</row>
<row>
<entry> <literal><></literal> </entry>
<entry>is not equal</entry>
<entry><literal>inet '192.168.1.5' <> inet '192.168.1.4'</literal></entry>
</row>
<row>
- <entry> <literal><<</literal> </entry>
- <entry>is contained by</entry>
- <entry><literal>inet '192.168.1.5' << inet '192.168.1/24'</literal></entry>
+ <entry> <literal><<@</literal> </entry>
+ <entry>is contained by and smaller network</entry>
+ <entry><literal>inet '192.168.1.5' <<@ inet '192.168.1/24'</literal></entry>
</row>
<row>
- <entry> <literal><<=</literal> </entry>
- <entry>is contained by or equals</entry>
- <entry><literal>inet '192.168.1/24' <<= inet '192.168.1/24'</literal></entry>
+ <entry> <literal><@</literal> </entry>
+ <entry>is contained by (can have the same masklen)</entry>
+ <entry><literal>inet '192.168.1/24' <@ inet '192.168.1/24'</literal></entry>
</row>
<row>
- <entry> <literal>>></literal> </entry>
- <entry>contains</entry>
- <entry><literal>inet '192.168.1/24' >> inet '192.168.1.5'</literal></entry>
+ <entry> <literal>@>></literal> </entry>
+ <entry>contains and bigger network</entry>
+ <entry><literal>inet '192.168.1/24' @>> inet '192.168.1.5'</literal></entry>
</row>
<row>
- <entry> <literal>>>=</literal> </entry>
- <entry>contains or equals</entry>
- <entry><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></entry>
+ <entry> <literal>@></literal> </entry>
+ <entry>contains (can have the same masklen)</entry>
+ <entry><literal>inet '192.168.1/24' @> inet '192.168.1/24'</literal></entry>
</row>
<row>
<entry> <literal>&&</literal> </entry>
<entry>contains or is contained by</entry>
<entry><literal>inet '192.168.1/24' && inet '192.168.1.80/28'</literal></entry>
</row>
<row>
<entry> <literal>~</literal> </entry>
<entry>bitwise NOT</entry>
<entry><literal>~ inet '192.168.1.6'</literal></entry>
@@ -8971,20 +8971,31 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</row>
<row>
<entry> <literal>-</literal> </entry>
<entry>subtraction</entry>
<entry><literal>inet '192.168.1.43' - inet '192.168.1.19'</literal></entry>
</row>
</tbody>
</tgroup>
</table>
+ <note>
+ <para>
+ Before <productname>PostgreSQL</productname> 10, the containment
+ operators <literal><@</>, <literal>@></>, <literal><<@</>,
+ and <literal>@>></> were respectively called
+ <literal><<=</>, <literal>>>=</>, <literal><<</>,
+ and <literal>>></>. These names are still available, but are
+ deprecated and will eventually be removed.
+ </para>
+ </note>
+
<para>
<xref linkend="cidr-inet-functions-table"> shows the functions
available for use with the <type>cidr</type> and <type>inet</type>
types. The <function>abbrev</function>, <function>host</function>,
and <function>text</function>
functions are primarily intended to offer alternative display
formats.
</para>
<table id="cidr-inet-functions-table">
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index b3cc347..7a2dada 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -106,30 +106,34 @@
</entry>
<entry>
<literal><-></>
</entry>
</row>
<row>
<entry><literal>inet_ops</></entry>
<entry><type>inet</>, <type>cidr</></entry>
<entry>
<literal>&&</>
- <literal>>></>
- <literal>>>=</>
+ <literal>@></>
+ <literal><@</>
+ <literal>@>></>
+ <literal><<@</>
+ <literal>=</>
+ <literal><></>
<literal>></>
<literal>>=</>
- <literal><></>
- <literal><<</>
- <literal><<=</>
<literal><</>
<literal><=</>
- <literal>=</>
+ <literal><<</>
+ <literal><<=</>
+ <literal>>></>
+ <literal>>>=</>
</entry>
<entry>
</entry>
</row>
<row>
<entry><literal>point_ops</></entry>
<entry><type>point</></entry>
<entry>
<literal>>></>
<literal>>^</>
diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml
index cd4a8d0..7867dd7 100644
--- a/doc/src/sgml/spgist.sgml
+++ b/doc/src/sgml/spgist.sgml
@@ -143,30 +143,30 @@
<literal>~<~</>
<literal>~>=~</>
<literal>~>~</>
</entry>
</row>
<row>
<entry><literal>inet_ops</></entry>
<entry><type>inet</>, <type>cidr</></entry>
<entry>
<literal>&&</>
- <literal>>></>
- <literal>>>=</>
+ <literal>@></>
+ <literal><@</>
+ <literal>@>></>
+ <literal><<@</>
+ <literal>=</>
+ <literal><></>
<literal>></>
<literal>>=</>
- <literal><></>
- <literal><<</>
- <literal><<=</>
<literal><</>
<literal><=</>
- <literal>=</>
</entry>
</row>
</tbody>
</tgroup>
</table>
<para>
Of the two operator classes for type <type>point</>,
<literal>quad_point_ops</> is the default. <literal>kd_point_ops</>
supports the same operators but uses a different index data structure which
diff --git a/src/backend/utils/adt/network_gist.c b/src/backend/utils/adt/network_gist.c
index 2caff94..c994b92a 100644
--- a/src/backend/utils/adt/network_gist.c
+++ b/src/backend/utils/adt/network_gist.c
@@ -55,23 +55,27 @@
* Operator strategy numbers used in the GiST inet_ops opclass
*/
#define INETSTRAT_OVERLAPS RTOverlapStrategyNumber
#define INETSTRAT_EQ RTEqualStrategyNumber
#define INETSTRAT_NE RTNotEqualStrategyNumber
#define INETSTRAT_LT RTLessStrategyNumber
#define INETSTRAT_LE RTLessEqualStrategyNumber
#define INETSTRAT_GT RTGreaterStrategyNumber
#define INETSTRAT_GE RTGreaterEqualStrategyNumber
#define INETSTRAT_SUB RTSubStrategyNumber
+#define INETSTRAT_SUBNET RTOldContainedByStrategyNumber
#define INETSTRAT_SUBEQ RTSubEqualStrategyNumber
+#define INETSTRAT_CONTAINED RTContainedByStrategyNumber
#define INETSTRAT_SUP RTSuperStrategyNumber
+#define INETSTRAT_SUPERNET RTOldContainsStrategyNumber
#define INETSTRAT_SUPEQ RTSuperEqualStrategyNumber
+#define INETSTRAT_CONTAINS RTContainsStrategyNumber
/*
* Representation of a GiST INET/CIDR index key. This is not identical to
* INET/CIDR because we need to keep track of the length of the common address
* prefix as well as the minimum netmask length. However, as long as it
* follows varlena header rules, the core GiST code won't know the difference.
* For simplicity we always use 1-byte-header varlena format.
*/
typedef struct GistInetKey
@@ -169,36 +173,40 @@ inet_gist_consistent(PG_FUNCTION_ARGS)
* Check 2: network bit count
*
* Network bit count (ip_bits) helps to check leaves for sub network and
* sup network operators. At non-leaf nodes, we know every child value
* has ip_bits >= gk_ip_minbits(key), so we can avoid descending in some
* cases too.
*/
switch (strategy)
{
case INETSTRAT_SUB:
+ case INETSTRAT_SUBNET:
if (GIST_LEAF(ent) && gk_ip_minbits(key) <= ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUBEQ:
+ case INETSTRAT_CONTAINED:
if (GIST_LEAF(ent) && gk_ip_minbits(key) < ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUPEQ:
+ case INETSTRAT_CONTAINS:
case INETSTRAT_EQ:
if (gk_ip_minbits(key) > ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUP:
+ case INETSTRAT_SUPERNET:
if (gk_ip_minbits(key) >= ip_bits(query))
PG_RETURN_BOOL(false);
break;
}
/*
* Check 3: common network bits
*
* Compare available common prefix bits to the query, but not beyond
* either the query's netmask or the minimum netmask among the represented
@@ -210,24 +218,28 @@ inet_gist_consistent(PG_FUNCTION_ARGS)
* network part of the address.
*/
minbits = Min(gk_ip_commonbits(key), gk_ip_minbits(key));
minbits = Min(minbits, ip_bits(query));
order = bitncmp(gk_ip_addr(key), ip_addr(query), minbits);
switch (strategy)
{
case INETSTRAT_SUB:
+ case INETSTRAT_SUBNET:
case INETSTRAT_SUBEQ:
+ case INETSTRAT_CONTAINED:
case INETSTRAT_OVERLAPS:
case INETSTRAT_SUPEQ:
+ case INETSTRAT_CONTAINS:
case INETSTRAT_SUP:
+ case INETSTRAT_SUPERNET:
PG_RETURN_BOOL(order == 0);
case INETSTRAT_LT:
case INETSTRAT_LE:
if (order > 0)
PG_RETURN_BOOL(false);
if (order < 0 || !GIST_LEAF(ent))
PG_RETURN_BOOL(true);
break;
diff --git a/src/backend/utils/adt/network_selfuncs.c b/src/backend/utils/adt/network_selfuncs.c
index 2e39687..bd369bc 100644
--- a/src/backend/utils/adt/network_selfuncs.c
+++ b/src/backend/utils/adt/network_selfuncs.c
@@ -29,21 +29,21 @@
/* Default selectivity for the inet overlap operator */
#define DEFAULT_OVERLAP_SEL 0.01
/* Default selectivity for the various inclusion operators */
#define DEFAULT_INCLUSION_SEL 0.005
/* Default selectivity for specified operator */
#define DEFAULT_SEL(operator) \
- ((operator) == OID_INET_OVERLAP_OP ? \
+ ((operator) == OID_INET_OVERLAPS_OP ? \
DEFAULT_OVERLAP_SEL : DEFAULT_INCLUSION_SEL)
/* Maximum number of items to consider in join selectivity calculations */
#define MAX_CONSIDERED_ELEMS 1024
static Selectivity networkjoinsel_inner(Oid operator,
VariableStatData *vardata1, VariableStatData *vardata2);
static Selectivity networkjoinsel_semi(Oid operator,
VariableStatData *vardata1, VariableStatData *vardata2);
static Selectivity mcv_population(float4 *mcv_numbers, int mcv_nvalues);
@@ -865,28 +865,32 @@ inet_semi_join_sel(Datum lhs_value,
* on the exact codes assigned here; but many other places in this file
* know that they can negate a code to obtain the code for the commutator
* operator.
*/
static int
inet_opr_codenum(Oid operator)
{
switch (operator)
{
case OID_INET_SUP_OP:
+ case OID_INET_SUPERNET_OP:
return -2;
case OID_INET_SUPEQ_OP:
+ case OID_INET_CONTAINS_OP:
return -1;
- case OID_INET_OVERLAP_OP:
+ case OID_INET_OVERLAPS_OP:
return 0;
+ case OID_INET_CONTAINED_OP:
case OID_INET_SUBEQ_OP:
return 1;
case OID_INET_SUB_OP:
+ case OID_INET_SUBNET_OP:
return 2;
default:
elog(ERROR, "unrecognized operator %u for inet selectivity",
operator);
}
return 0; /* unreached, but keep compiler quiet */
}
/*
* Comparison function for the subnet inclusion/overlap operators
diff --git a/src/backend/utils/adt/network_spgist.c b/src/backend/utils/adt/network_spgist.c
index a198a83..819d6fd 100644
--- a/src/backend/utils/adt/network_spgist.c
+++ b/src/backend/utils/adt/network_spgist.c
@@ -435,38 +435,38 @@ inet_spg_consistent_bitmap(const inet *prefix, int nkeys, ScanKey scankeys,
* value has greater ip_bits, so we can avoid descending in some cases
* too.
*
* This check is less expensive than checking the address bits, so we
* are doing this before, but it has to be done after for the basic
* comparison strategies, because ip_bits only affect their results
* when the common network bits are the same.
*/
switch (strategy)
{
- case RTSubStrategyNumber:
+ case RTOldContainedByStrategyNumber:
if (commonbits <= ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
break;
- case RTSubEqualStrategyNumber:
+ case RTContainedByStrategyNumber:
if (commonbits < ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
break;
- case RTSuperStrategyNumber:
+ case RTOldContainsStrategyNumber:
if (commonbits == ip_bits(argument) - 1)
bitmap &= 1 | (1 << 1);
else if (commonbits >= ip_bits(argument))
bitmap = 0;
break;
- case RTSuperEqualStrategyNumber:
+ case RTContainsStrategyNumber:
if (commonbits == ip_bits(argument))
bitmap &= 1 | (1 << 1);
else if (commonbits > ip_bits(argument))
bitmap = 0;
break;
case RTEqualStrategyNumber:
if (commonbits < ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
else if (commonbits == ip_bits(argument))
diff --git a/src/include/access/stratnum.h b/src/include/access/stratnum.h
index eabced5..2916815 100644
--- a/src/include/access/stratnum.h
+++ b/src/include/access/stratnum.h
@@ -57,19 +57,19 @@ typedef uint16 StrategyNumber;
#define RTOldContainedByStrategyNumber 14 /* for old spelling of <@ */
#define RTKNNSearchStrategyNumber 15 /* for <-> (distance) */
#define RTContainsElemStrategyNumber 16 /* for range types @> elem */
#define RTAdjacentStrategyNumber 17 /* for -|- */
#define RTEqualStrategyNumber 18 /* for = */
#define RTNotEqualStrategyNumber 19 /* for != */
#define RTLessStrategyNumber 20 /* for < */
#define RTLessEqualStrategyNumber 21 /* for <= */
#define RTGreaterStrategyNumber 22 /* for > */
#define RTGreaterEqualStrategyNumber 23 /* for >= */
-#define RTSubStrategyNumber 24 /* for inet >> */
+#define RTSubStrategyNumber 24 /* for inet << */
#define RTSubEqualStrategyNumber 25 /* for inet <<= */
-#define RTSuperStrategyNumber 26 /* for inet << */
+#define RTSuperStrategyNumber 26 /* for inet >> */
#define RTSuperEqualStrategyNumber 27 /* for inet >>= */
#define RTMaxStrategyNumber 27
#endif /* STRATNUM_H */
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index e4c3515..32f8253 100644
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -842,45 +842,49 @@ DATA(insert ( 5000 603 603 7 s 498 4000 0 ));
DATA(insert ( 5000 603 603 8 s 497 4000 0 ));
DATA(insert ( 5000 603 603 9 s 2571 4000 0 ));
DATA(insert ( 5000 603 603 10 s 2570 4000 0 ));
DATA(insert ( 5000 603 603 11 s 2573 4000 0 ));
DATA(insert ( 5000 603 603 12 s 2572 4000 0 ));
/*
* GiST inet_ops
*/
DATA(insert ( 3550 869 869 3 s 3552 783 0 ));
+DATA(insert ( 3550 869 869 7 s 3453 783 0 ));
+DATA(insert ( 3550 869 869 8 s 3451 783 0 ));
+DATA(insert ( 3550 869 869 13 s 3452 783 0 ));
+DATA(insert ( 3550 869 869 14 s 3450 783 0 ));
DATA(insert ( 3550 869 869 18 s 1201 783 0 ));
DATA(insert ( 3550 869 869 19 s 1202 783 0 ));
DATA(insert ( 3550 869 869 20 s 1203 783 0 ));
DATA(insert ( 3550 869 869 21 s 1204 783 0 ));
DATA(insert ( 3550 869 869 22 s 1205 783 0 ));
DATA(insert ( 3550 869 869 23 s 1206 783 0 ));
DATA(insert ( 3550 869 869 24 s 931 783 0 ));
DATA(insert ( 3550 869 869 25 s 932 783 0 ));
DATA(insert ( 3550 869 869 26 s 933 783 0 ));
DATA(insert ( 3550 869 869 27 s 934 783 0 ));
/*
* SP-GiST inet_ops
*/
DATA(insert ( 3794 869 869 3 s 3552 4000 0 ));
+DATA(insert ( 3794 869 869 7 s 3453 4000 0 ));
+DATA(insert ( 3794 869 869 8 s 3451 4000 0 ));
+DATA(insert ( 3794 869 869 13 s 3452 4000 0 ));
+DATA(insert ( 3794 869 869 14 s 3450 4000 0 ));
DATA(insert ( 3794 869 869 18 s 1201 4000 0 ));
DATA(insert ( 3794 869 869 19 s 1202 4000 0 ));
DATA(insert ( 3794 869 869 20 s 1203 4000 0 ));
DATA(insert ( 3794 869 869 21 s 1204 4000 0 ));
DATA(insert ( 3794 869 869 22 s 1205 4000 0 ));
DATA(insert ( 3794 869 869 23 s 1206 4000 0 ));
-DATA(insert ( 3794 869 869 24 s 931 4000 0 ));
-DATA(insert ( 3794 869 869 25 s 932 4000 0 ));
-DATA(insert ( 3794 869 869 26 s 933 4000 0 ));
-DATA(insert ( 3794 869 869 27 s 934 4000 0 ));
/* BRIN opclasses */
/* minmax bytea */
DATA(insert ( 4064 17 17 1 s 1957 3580 0 ));
DATA(insert ( 4064 17 17 2 s 1958 3580 0 ));
DATA(insert ( 4064 17 17 3 s 1955 3580 0 ));
DATA(insert ( 4064 17 17 4 s 1960 3580 0 ));
DATA(insert ( 4064 17 17 5 s 1959 3580 0 ));
/* minmax "char" */
DATA(insert ( 4062 18 18 1 s 631 3580 0 ));
@@ -1000,25 +1004,29 @@ DATA(insert ( 4074 829 829 3 s 1220 3580 0 ));
DATA(insert ( 4074 829 829 4 s 1225 3580 0 ));
DATA(insert ( 4074 829 829 5 s 1224 3580 0 ));
/* minmax inet */
DATA(insert ( 4075 869 869 1 s 1203 3580 0 ));
DATA(insert ( 4075 869 869 2 s 1204 3580 0 ));
DATA(insert ( 4075 869 869 3 s 1201 3580 0 ));
DATA(insert ( 4075 869 869 4 s 1206 3580 0 ));
DATA(insert ( 4075 869 869 5 s 1205 3580 0 ));
/* inclusion inet */
DATA(insert ( 4102 869 869 3 s 3552 3580 0 ));
-DATA(insert ( 4102 869 869 7 s 934 3580 0 ));
-DATA(insert ( 4102 869 869 8 s 932 3580 0 ));
+DATA(insert ( 4102 869 869 7 s 3453 3580 0 ));
+DATA(insert ( 4102 869 869 8 s 3451 3580 0 ));
+DATA(insert ( 4102 869 869 13 s 3452 3580 0 ));
+DATA(insert ( 4102 869 869 14 s 3450 3580 0 ));
DATA(insert ( 4102 869 869 18 s 1201 3580 0 ));
DATA(insert ( 4102 869 869 24 s 933 3580 0 ));
+DATA(insert ( 4102 869 869 25 s 934 3580 0 ));
DATA(insert ( 4102 869 869 26 s 931 3580 0 ));
+DATA(insert ( 4102 869 869 27 s 932 3580 0 ));
/* minmax character */
DATA(insert ( 4076 1042 1042 1 s 1058 3580 0 ));
DATA(insert ( 4076 1042 1042 2 s 1059 3580 0 ));
DATA(insert ( 4076 1042 1042 3 s 1054 3580 0 ));
DATA(insert ( 4076 1042 1042 4 s 1061 3580 0 ));
DATA(insert ( 4076 1042 1042 5 s 1060 3580 0 ));
/* minmax time without time zone */
DATA(insert ( 4077 1083 1083 1 s 1110 3580 0 ));
DATA(insert ( 4077 1083 1083 2 s 1111 3580 0 ));
DATA(insert ( 4077 1083 1083 3 s 1108 3580 0 ));
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index 26fa618..dc97c6e 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -1141,34 +1141,46 @@ DATA(insert OID = 1202 ( "<>" PGNSP PGUID b f f 869 869 16 1202 1201 networ
DESCR("not equal");
DATA(insert OID = 1203 ( "<" PGNSP PGUID b f f 869 869 16 1205 1206 network_lt scalarltsel scalarltjoinsel ));
DESCR("less than");
DATA(insert OID = 1204 ( "<=" PGNSP PGUID b f f 869 869 16 1206 1205 network_le scalarltsel scalarltjoinsel ));
DESCR("less than or equal");
DATA(insert OID = 1205 ( ">" PGNSP PGUID b f f 869 869 16 1203 1204 network_gt scalargtsel scalargtjoinsel ));
DESCR("greater than");
DATA(insert OID = 1206 ( ">=" PGNSP PGUID b f f 869 869 16 1204 1203 network_ge scalargtsel scalargtjoinsel ));
DESCR("greater than or equal");
DATA(insert OID = 931 ( "<<" PGNSP PGUID b f f 869 869 16 933 0 network_sub networksel networkjoinsel ));
-DESCR("is subnet");
+DESCR("deprecated, use <<@ instead");
#define OID_INET_SUB_OP 931
+DATA(insert OID = 3450 ( "<<@" PGNSP PGUID b f f 869 869 16 3452 0 network_sub networksel networkjoinsel ));
+DESCR("is subnet");
+#define OID_INET_SUBNET_OP 3450
DATA(insert OID = 932 ( "<<=" PGNSP PGUID b f f 869 869 16 934 0 network_subeq networksel networkjoinsel ));
-DESCR("is subnet or equal");
+DESCR("deprecated, use <@ instead");
#define OID_INET_SUBEQ_OP 932
+DATA(insert OID = 3451 ( "<@" PGNSP PGUID b f f 869 869 16 3453 0 network_subeq networksel networkjoinsel ));
+DESCR("is contained by");
+#define OID_INET_CONTAINED_OP 3451
DATA(insert OID = 933 ( ">>" PGNSP PGUID b f f 869 869 16 931 0 network_sup networksel networkjoinsel ));
-DESCR("is supernet");
+DESCR("deprecated, use @>> instead");
#define OID_INET_SUP_OP 933
+DATA(insert OID = 3452 ( "@>>" PGNSP PGUID b f f 869 869 16 3450 0 network_sup networksel networkjoinsel ));
+DESCR("is supernet");
+#define OID_INET_SUPERNET_OP 3452
DATA(insert OID = 934 ( ">>=" PGNSP PGUID b f f 869 869 16 932 0 network_supeq networksel networkjoinsel ));
-DESCR("is supernet or equal");
+DESCR("deprecated, use @> instead");
#define OID_INET_SUPEQ_OP 934
-DATA(insert OID = 3552 ( "&&" PGNSP PGUID b f f 869 869 16 3552 0 network_overlap networksel networkjoinsel ));
-DESCR("overlaps (is subnet or supernet)");
-#define OID_INET_OVERLAP_OP 3552
+DATA(insert OID = 3453 ( "@>" PGNSP PGUID b f f 869 869 16 3451 0 network_supeq networksel networkjoinsel ));
+DESCR("contains");
+#define OID_INET_CONTAINS_OP 3453
+DATA(insert OID = 3552 ( "&&" PGNSP PGUID b f f 869 869 16 3552 0 network_overlap networksel networkjoinsel ));
+DESCR("overlaps (contains or is contained by)");
+#define OID_INET_OVERLAPS_OP 3552
DATA(insert OID = 2634 ( "~" PGNSP PGUID l f f 0 869 869 0 0 inetnot - - ));
DESCR("bitwise not");
DATA(insert OID = 2635 ( "&" PGNSP PGUID b f f 869 869 869 0 0 inetand - - ));
DESCR("bitwise and");
DATA(insert OID = 2636 ( "|" PGNSP PGUID b f f 869 869 869 0 0 inetor - - ));
DESCR("bitwise or");
DATA(insert OID = 2637 ( "+" PGNSP PGUID b f f 869 20 869 2638 0 inetpl - - ));
DESCR("add");
DATA(insert OID = 2638 ( "+" PGNSP PGUID b f f 20 869 869 2637 0 int8pl_inet - - ));
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index 21676e5..9a47b57 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -173,49 +173,69 @@ INSERT INTO brinopers VALUES
'{99, 100, 1, 100, 100}'),
('float8col', 'float8',
'{>, >=, =, <=, <}',
'{0, 0, 0, 1.98, 1.98}',
'{99, 100, 1, 100, 100}'),
('macaddrcol', 'macaddr',
'{>, >=, =, <=, <}',
'{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}',
'{99, 100, 2, 100, 100}'),
('inetcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2/10, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
+ ('inetcol', 'inet', -- Deprecated operators
+ '{>>=, >>, <<=, <<}',
+ '{10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{2, 2, 100, 100}'),
+ ('inetcol', 'inet', -- Basic comparison operators
+ '{=, <, <=, >, >=}',
+ '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{1, 100, 100, 125, 125}'),
('inetcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}', -- IPv6
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
+ ('inetcol', 'cidr', -- Cross data-type
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 10.0/9}',
+ '{100, 2, 100, 2, 100}'),
('inetcol', 'cidr',
- '{&&, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{<, <=, >, >=}',
+ '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{100, 100, 125, 125}'),
('inetcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2.14.231/8, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'inet',
+ '{&&, @>, <@, =}',
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'cidr',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'cidr',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('bpcharcol', 'bpchar',
'{>, >=, =, <=, <}',
'{A, A, W, Z, Z}',
'{97, 100, 6, 100, 98}'),
('datecol', 'date',
'{>, >=, =, <=, <}',
'{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
'{100, 100, 1, 100, 100}'),
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index be9427e..9930bd4 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -172,25 +172,25 @@ SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
WHERE c = i;
six | cidr | inet
-----+----------------+----------------
| 192.168.1.0/24 | 192.168.1.0/24
| 10.1.2.3/32 | 10.1.2.3
(2 rows)
SELECT '' AS ten, i, c,
i < c AS lt, i <= c AS le, i = c AS eq,
i >= c AS ge, i > c AS gt, i <> c AS ne,
- i << c AS sb, i <<= c AS sbe,
- i >> c AS sup, i >>= c AS spe,
+ i <<@ c AS sb, i <@ c AS cnd,
+ i @>> c AS sup, i @> c AS cns,
i && c AS ovr
FROM INET_TBL;
- ten | i | c | lt | le | eq | ge | gt | ne | sb | sbe | sup | spe | ovr
+ ten | i | c | lt | le | eq | ge | gt | ne | sb | cnd | sup | cns | ovr
-----+------------------+--------------------+----+----+----+----+----+----+----+-----+-----+-----+-----
| 192.168.1.226/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t | t
| 192.168.1.226 | 192.168.1.0/26 | f | f | f | t | t | t | f | f | f | f | f
| 192.168.1.0/24 | 192.168.1.0/24 | f | t | t | t | f | f | f | t | f | t | t
| 192.168.1.0/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f | t
| 192.168.1.255/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t | t
| 192.168.1.255/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f | t
| 10.1.2.3/8 | 10.0.0.0/8 | f | f | f | t | t | t | f | t | f | t | t
| 10.1.2.3/8 | 10.0.0.0/32 | t | t | f | f | f | t | f | f | t | t | t
| 10.1.2.3 | 10.1.2.3/32 | f | t | t | t | f | f | f | t | f | t | t
@@ -267,55 +267,87 @@ DROP INDEX inet_idx1;
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(3 rows)
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(3 rows)
+
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(6 rows)
+
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
(3 rows)
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+(3 rows)
+
SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
c | i
---+---
(0 rows)
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+---+---
+(0 rows)
+
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
10.1.2.3/32 | 10.1.2.3
@@ -407,29 +439,29 @@ SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
192.168.1.0/25
192.168.1.255/25
192.168.1.226
(3 rows)
SET enable_seqscan TO on;
DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
SET enable_seqscan TO off;
-SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(3 rows)
-SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
@@ -437,29 +469,29 @@ SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
-SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
(3 rows)
-SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
c | i
---+---
(0 rows)
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
@@ -533,30 +565,30 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
::ffff:1.2.3.4/128 | ::4.3.2.1/24
10:23::f1/128 | 10:23::f1/64
10:23::8000/113 | 10:23::ffff
(16 rows)
-- test index-only scans
EXPLAIN (COSTS OFF)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
- QUERY PLAN
----------------------------------------------------
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+ QUERY PLAN
+----------------------------------------------------
Sort
Sort Key: i
-> Index Only Scan using inet_idx3 on inet_tbl
- Index Cond: (i << '192.168.1.0/24'::inet)
+ Index Cond: (i <<@ '192.168.1.0/24'::inet)
(4 rows)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
i
------------------
192.168.1.0/25
192.168.1.255/25
192.168.1.226
(3 rows)
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
-- simple tests of inet boolean and arithmetic operators
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 0bcec13..4ec0402 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -953,37 +953,38 @@ ORDER BY 1, 2;
*< | *>
*<= | *>=
*<> | *<>
*= | *=
+ | +
-|- | -|-
< | >
<-> | <->
<< | >>
<<= | >>=
+ <<@ | @>>
<= | >=
<> | <>
<@ | @>
= | =
?# | ?#
?- | ?-
?-| | ?-|
?| | ?|
?|| | ?||
@ | ~
@@ | @@
@@@ | @@@
| | |
~<=~ | ~>=~
~<~ | ~>~
~= | ~=
-(30 rows)
+(31 rows)
-- Likewise for negator pairs.
SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
FROM pg_operator o1, pg_operator o2
WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname
ORDER BY 1, 2;
op1 | op2
------+------
!~ | ~
!~* | ~*
@@ -1731,21 +1732,23 @@ ORDER BY 1, 2, 3;
783 | 6 | -|-
783 | 6 | ~=
783 | 7 | @>
783 | 8 | <@
783 | 9 | &<|
783 | 10 | <<|
783 | 10 | <^
783 | 11 | >^
783 | 11 | |>>
783 | 12 | |&>
+ 783 | 13 | @>>
783 | 13 | ~
+ 783 | 14 | <<@
783 | 14 | @
783 | 15 | <->
783 | 16 | @>
783 | 18 | =
783 | 19 | <>
783 | 20 | <
783 | 21 | <=
783 | 22 | >
783 | 23 | >=
783 | 24 | <<
@@ -1769,37 +1772,39 @@ ORDER BY 1, 2, 3;
3580 | 1 | <<
3580 | 2 | &<
3580 | 2 | <=
3580 | 3 | &&
3580 | 3 | =
3580 | 4 | &>
3580 | 4 | >=
3580 | 5 | >
3580 | 5 | >>
3580 | 6 | ~=
- 3580 | 7 | >>=
3580 | 7 | @>
- 3580 | 8 | <<=
3580 | 8 | <@
3580 | 9 | &<|
3580 | 10 | <<|
3580 | 11 | |>>
3580 | 12 | |&>
+ 3580 | 13 | @>>
+ 3580 | 14 | <<@
3580 | 16 | @>
3580 | 17 | -|-
3580 | 18 | =
3580 | 20 | <
3580 | 21 | <=
3580 | 22 | >
3580 | 23 | >=
3580 | 24 | >>
+ 3580 | 25 | >>=
3580 | 26 | <<
+ 3580 | 27 | <<=
4000 | 1 | <<
4000 | 1 | ~<~
4000 | 2 | &<
4000 | 2 | ~<=~
4000 | 3 | &&
4000 | 3 | =
4000 | 4 | &>
4000 | 4 | ~>=~
4000 | 5 | >>
4000 | 5 | ~>~
@@ -1808,34 +1813,32 @@ ORDER BY 1, 2, 3;
4000 | 7 | @>
4000 | 8 | <@
4000 | 9 | &<|
4000 | 10 | <<|
4000 | 10 | <^
4000 | 11 | <
4000 | 11 | >^
4000 | 11 | |>>
4000 | 12 | <=
4000 | 12 | |&>
+ 4000 | 13 | @>>
+ 4000 | 14 | <<@
4000 | 14 | >=
4000 | 15 | >
4000 | 16 | @>
4000 | 18 | =
4000 | 19 | <>
4000 | 20 | <
4000 | 21 | <=
4000 | 22 | >
4000 | 23 | >=
- 4000 | 24 | <<
- 4000 | 25 | <<=
- 4000 | 26 | >>
- 4000 | 27 | >>=
-(121 rows)
+(123 rows)
-- Check that all opclass search operators have selectivity estimators.
-- This is not absolutely required, but it seems a reasonable thing
-- to insist on for all standard datatypes.
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
(p2.oprrest = 0 OR p2.oprjoin = 0);
amopfamily | amopopr | oid | oprname
------------+---------+-----+---------
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index e7f6f77..939cfdb 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -178,49 +178,69 @@ INSERT INTO brinopers VALUES
'{99, 100, 1, 100, 100}'),
('float8col', 'float8',
'{>, >=, =, <=, <}',
'{0, 0, 0, 1.98, 1.98}',
'{99, 100, 1, 100, 100}'),
('macaddrcol', 'macaddr',
'{>, >=, =, <=, <}',
'{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}',
'{99, 100, 2, 100, 100}'),
('inetcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2/10, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
+ ('inetcol', 'inet', -- Deprecated operators
+ '{>>=, >>, <<=, <<}',
+ '{10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{2, 2, 100, 100}'),
+ ('inetcol', 'inet', -- Basic comparison operators
+ '{=, <, <=, >, >=}',
+ '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{1, 100, 100, 125, 125}'),
('inetcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}', -- IPv6
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
+ ('inetcol', 'cidr', -- Cross data-type
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 10.0/9}',
+ '{100, 2, 100, 2, 100}'),
('inetcol', 'cidr',
- '{&&, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{<, <=, >, >=}',
+ '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{100, 100, 125, 125}'),
('inetcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2.14.231/8, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'inet',
+ '{&&, @>, <@, =}',
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'cidr',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'cidr',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('bpcharcol', 'bpchar',
'{>, >=, =, <=, <}',
'{A, A, W, Z, Z}',
'{97, 100, 6, 100, 98}'),
('datecol', 'date',
'{>, >=, =, <=, <}',
'{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
'{100, 100, 1, 100, 100}'),
diff --git a/src/test/regress/sql/inet.sql b/src/test/regress/sql/inet.sql
index 880e115..58db04b 100644
--- a/src/test/regress/sql/inet.sql
+++ b/src/test/regress/sql/inet.sql
@@ -44,22 +44,22 @@ SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)",
SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)",
i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL
WHERE masklen(c) <= 8;
SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
WHERE c = i;
SELECT '' AS ten, i, c,
i < c AS lt, i <= c AS le, i = c AS eq,
i >= c AS ge, i > c AS gt, i <> c AS ne,
- i << c AS sb, i <<= c AS sbe,
- i >> c AS sup, i >>= c AS spe,
+ i <<@ c AS sb, i <@ c AS cnd,
+ i @>> c AS sup, i @> c AS cns,
i && c AS ovr
FROM INET_TBL;
SELECT max(i) AS max, min(i) AS min FROM INET_TBL;
SELECT max(c) AS max, min(c) AS min FROM INET_TBL;
-- check the conversion to/from text and set_netmask
SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
-- check that btree index works correctly
@@ -67,58 +67,62 @@ CREATE INDEX inet_idx1 ON inet_tbl(i);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
SET enable_seqscan TO on;
DROP INDEX inet_idx1;
-- check that gist index works correctly
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
-- test index-only scans
EXPLAIN (COSTS OFF)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SET enable_seqscan TO on;
DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
SET enable_seqscan TO off;
-SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
-- test index-only scans
EXPLAIN (COSTS OFF)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
-- simple tests of inet boolean and arithmetic operators
SELECT i, ~i AS "~i" FROM inet_tbl;
SELECT i, c, i & c AS "and" FROM inet_tbl;
SELECT i, c, i | c AS "or" FROM inet_tbl;
SELECT i, i + 500 AS "i+500" FROM inet_tbl;
SELECT i, i - 500 AS "i-500" FROM inet_tbl;
--
2.9.3 (Apple Git-75)
Re: Contains and is contained by operators of inet datatypes
On 11/13/2016 01:21 PM, Emre Hasegeli wrote:
Thank you for the review. New version is attached.
Nice, I am fine with this version of the patch. Setting it to ready for
committer!
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Contains and is contained by operators of inet datatypes
Andreas Karlsson <andreas@proxel.se> writes:
Emre Hasegeli wrote:
Attached patch adds <@, @>, <<@, and @>> operator symbols for inet
datatype to replace <<=, >>=, <<, and >>.
Nice, I am fine with this version of the patch. Setting it to ready for
committer!
I looked at this for awhile and TBH I am not very excited about it.
I am not sure it makes anything better, but I am sure it makes things
different. People tend not to like that.
The new names might be better if we were starting in a green field,
but in themselves they are not any more mnemonic than what we had, and
what we had has been there for a lot of years. Also, if we accept both
old names and new (which it seems like we'd have to), that creates new
opportunities for confusion, which is a cost that should not be
disregarded.
The original post proposed that we'd eventually get some benefit by
being able to repurpose << and >> to mean something else, but the
time scale over which that could happen is so long as to make it
unlikely to ever happen. I think we'd need to deprecate these names
for several years, then actually remove them and have nothing there for
a few years more, before we could safely install new operators that
take the same arguments but do something different. (For comparison's
sake, it took us five years to go from deprecating => as a user operator
to starting to use it as parameter naming syntax ... and that was a
case where conflicting use could be expected to throw an error, not
silently misbehave, so we could force it with little risk of silently
breaking peoples' applications. To repurpose << and >> in this way
we would need to move much slower.)
I'm inclined to think we should just reject this patch. I'm certainly not
going to commit it without seeing positive votes from multiple people.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Contains and is contained by operators of inet datatypes
The new names might be better if we were starting in a green field,
but in themselves they are not any more mnemonic than what we had, and
what we had has been there for a lot of years. Also, if we accept both
old names and new (which it seems like we'd have to), that creates new
opportunities for confusion, which is a cost that should not be
disregarded.
This is true for existing users of those operators. New names are
much easier to get by the new users. We are using them on all other
datatypes. Datatypes like JSON is more popular than inet. Many
people already know what <@ and @> mean.
The original post proposed that we'd eventually get some benefit by
being able to repurpose << and >> to mean something else, but the
time scale over which that could happen is so long as to make it
unlikely to ever happen.
I think we will immediately get benefit from the new operators because
of other reasons. Repurposing them in far future was a minor point.
I though having a long term plan on this minor point is better than
having no plan.
I'm inclined to think we should just reject this patch. I'm certainly not
going to commit it without seeing positive votes from multiple people.
It is a fair position. Anybody care to vote?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Contains and is contained by operators of inet datatypes
On 11/17/2016 11:14 PM, Tom Lane wrote:
The original post proposed that we'd eventually get some benefit by
being able to repurpose << and >> to mean something else, but the
time scale over which that could happen is so long as to make it
unlikely to ever happen. I think we'd need to deprecate these names
for several years, then actually remove them and have nothing there for
a few years more, before we could safely install new operators that
take the same arguments but do something different. (For comparison's
sake, it took us five years to go from deprecating => as a user operator
to starting to use it as parameter naming syntax ... and that was a
case where conflicting use could be expected to throw an error, not
silently misbehave, so we could force it with little risk of silently
breaking peoples' applications. To repurpose << and >> in this way
we would need to move much slower.)
I agree. The value in re-purposing them is pretty low given the long
time scales needed before that can be done.
I'm inclined to think we should just reject this patch. I'm certainly not
going to commit it without seeing positive votes from multiple people.
Given that I reviewed it I think you already have my vote on this.
I like the patch because it means less operators to remember for me as a
PostgreSQL user. And at least for me inet is a rarely used type compared
to hstore, json and range types which all use @> and <@.
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Contains and is contained by operators of inet datatypes
On Mon, Nov 21, 2016 at 7:57 AM, Andreas Karlsson <andreas@proxel.se> wrote:
I like the patch because it means less operators to remember for me as a
PostgreSQL user. And at least for me inet is a rarely used type compared to
hstore, json and range types which all use @> and <@.
I agree that it would be nice to make the choice of operator names
more consistent. I don't know if doing so will please more or fewer
people than it annoys. I do not like this bit from the original post:
EH> The patch removes the recently committed SP-GiST index support for the
EH> existing operator symbols to give move reason to the users to use the
EH> new symbols.
That seems like the rough equivalent of throwing a wrench into the
datacenter's backup generator to "encourage" them to maintain two
separate and independent backup generators. If we're going to add the
more-standard operator names as synonyms for the existing operator
names, let's do precisely that and no more.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Contains and is contained by operators of inet datatypes
I do not like this bit from the original post:
EH> The patch removes the recently committed SP-GiST index support for the
EH> existing operator symbols to give move reason to the users to use the
EH> new symbols.
I reverted this part. The new version of the patch is attached.
Attachments:
0001-inet-contain-op-v3.patchapplication/octet-stream; name=0001-inet-contain-op-v3.patchDownload
From 9af891b75fa14719eb9d59cf3649061e31fc3d6c Mon Sep 17 00:00:00 2001
From: Emre Hasegeli <emre@hasegeli.com>
Date: Wed, 31 Aug 2016 09:58:18 +0200
Subject: [PATCH] inet-contain-op-v3
---
doc/src/sgml/brin.sgml | 14 +++--
doc/src/sgml/func.sgml | 35 ++++++++-----
doc/src/sgml/gist.sgml | 16 +++---
doc/src/sgml/spgist.sgml | 16 +++---
src/backend/utils/adt/network_gist.c | 12 +++++
src/backend/utils/adt/network_selfuncs.c | 8 ++-
src/backend/utils/adt/network_spgist.c | 4 ++
src/include/access/stratnum.h | 4 +-
src/include/catalog/pg_amop.h | 24 ++++++---
src/include/catalog/pg_operator.h | 26 +++++++---
src/test/regress/expected/brin.out | 52 +++++++++++++------
src/test/regress/expected/inet.out | 88 +++++++++++++++++++++++++++-----
src/test/regress/expected/opr_sanity.out | 15 ++++--
src/test/regress/sql/brin.sql | 52 +++++++++++++------
src/test/regress/sql/inet.sql | 24 ++++++---
15 files changed, 288 insertions(+), 102 deletions(-)
diff --git a/doc/src/sgml/brin.sgml b/doc/src/sgml/brin.sgml
index f519285..508bbce 100644
--- a/doc/src/sgml/brin.sgml
+++ b/doc/src/sgml/brin.sgml
@@ -230,29 +230,33 @@
<entry><type>inet</type></entry>
<entry>
<literal><</literal>
<literal><=</literal>
<literal>=</literal>
<literal>>=</literal>
<literal>></literal>
</entry>
</row>
<row>
- <entry><literal>network_inclusion_ops</literal></entry>
+ <entry><literal>inet_inclusion_ops</literal></entry>
<entry><type>inet</type></entry>
<entry>
- <literal>&&</>
- <literal>>>=</>
- <literal><<=</literal>
+ <literal>&&</literal>
+ <literal>@></literal>
+ <literal><@</literal>
+ <literal>@>></literal>
+ <literal><<@</literal>
<literal>=</literal>
- <literal>>></>
<literal><<</literal>
+ <literal><<=</literal>
+ <literal>>></literal>
+ <literal>>>=</literal>
</entry>
</row>
<row>
<entry><literal>int4_minmax_ops</literal></entry>
<entry><type>integer</type></entry>
<entry>
<literal><</literal>
<literal><=</literal>
<literal>=</literal>
<literal>>=</literal>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2e64cc4..0f003ef 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8913,38 +8913,38 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<entry> <literal>></literal> </entry>
<entry>is greater than</entry>
<entry><literal>inet '192.168.1.5' > inet '192.168.1.4'</literal></entry>
</row>
<row>
<entry> <literal><></literal> </entry>
<entry>is not equal</entry>
<entry><literal>inet '192.168.1.5' <> inet '192.168.1.4'</literal></entry>
</row>
<row>
- <entry> <literal><<</literal> </entry>
- <entry>is contained by</entry>
- <entry><literal>inet '192.168.1.5' << inet '192.168.1/24'</literal></entry>
+ <entry> <literal><<@</literal> </entry>
+ <entry>is contained by and smaller network</entry>
+ <entry><literal>inet '192.168.1.5' <<@ inet '192.168.1/24'</literal></entry>
</row>
<row>
- <entry> <literal><<=</literal> </entry>
- <entry>is contained by or equals</entry>
- <entry><literal>inet '192.168.1/24' <<= inet '192.168.1/24'</literal></entry>
+ <entry> <literal><@</literal> </entry>
+ <entry>is contained by (can have the same masklen)</entry>
+ <entry><literal>inet '192.168.1/24' <@ inet '192.168.1/24'</literal></entry>
</row>
<row>
- <entry> <literal>>></literal> </entry>
- <entry>contains</entry>
- <entry><literal>inet '192.168.1/24' >> inet '192.168.1.5'</literal></entry>
+ <entry> <literal>@>></literal> </entry>
+ <entry>contains and bigger network</entry>
+ <entry><literal>inet '192.168.1/24' @>> inet '192.168.1.5'</literal></entry>
</row>
<row>
- <entry> <literal>>>=</literal> </entry>
- <entry>contains or equals</entry>
- <entry><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></entry>
+ <entry> <literal>@></literal> </entry>
+ <entry>contains (can have the same masklen)</entry>
+ <entry><literal>inet '192.168.1/24' @> inet '192.168.1/24'</literal></entry>
</row>
<row>
<entry> <literal>&&</literal> </entry>
<entry>contains or is contained by</entry>
<entry><literal>inet '192.168.1/24' && inet '192.168.1.80/28'</literal></entry>
</row>
<row>
<entry> <literal>~</literal> </entry>
<entry>bitwise NOT</entry>
<entry><literal>~ inet '192.168.1.6'</literal></entry>
@@ -8971,20 +8971,31 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</row>
<row>
<entry> <literal>-</literal> </entry>
<entry>subtraction</entry>
<entry><literal>inet '192.168.1.43' - inet '192.168.1.19'</literal></entry>
</row>
</tbody>
</tgroup>
</table>
+ <note>
+ <para>
+ Before <productname>PostgreSQL</productname> 10, the containment
+ operators <literal><@</>, <literal>@></>, <literal><<@</>,
+ and <literal>@>></> were respectively called
+ <literal><<=</>, <literal>>>=</>, <literal><<</>,
+ and <literal>>></>. These names are still available, but are
+ deprecated and will eventually be removed.
+ </para>
+ </note>
+
<para>
<xref linkend="cidr-inet-functions-table"> shows the functions
available for use with the <type>cidr</type> and <type>inet</type>
types. The <function>abbrev</function>, <function>host</function>,
and <function>text</function>
functions are primarily intended to offer alternative display
formats.
</para>
<table id="cidr-inet-functions-table">
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index b3cc347..7a2dada 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -106,30 +106,34 @@
</entry>
<entry>
<literal><-></>
</entry>
</row>
<row>
<entry><literal>inet_ops</></entry>
<entry><type>inet</>, <type>cidr</></entry>
<entry>
<literal>&&</>
- <literal>>></>
- <literal>>>=</>
+ <literal>@></>
+ <literal><@</>
+ <literal>@>></>
+ <literal><<@</>
+ <literal>=</>
+ <literal><></>
<literal>></>
<literal>>=</>
- <literal><></>
- <literal><<</>
- <literal><<=</>
<literal><</>
<literal><=</>
- <literal>=</>
+ <literal><<</>
+ <literal><<=</>
+ <literal>>></>
+ <literal>>>=</>
</entry>
<entry>
</entry>
</row>
<row>
<entry><literal>point_ops</></entry>
<entry><type>point</></entry>
<entry>
<literal>>></>
<literal>>^</>
diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml
index cd4a8d0..95ec27d 100644
--- a/doc/src/sgml/spgist.sgml
+++ b/doc/src/sgml/spgist.sgml
@@ -143,30 +143,34 @@
<literal>~<~</>
<literal>~>=~</>
<literal>~>~</>
</entry>
</row>
<row>
<entry><literal>inet_ops</></entry>
<entry><type>inet</>, <type>cidr</></entry>
<entry>
<literal>&&</>
- <literal>>></>
- <literal>>>=</>
+ <literal>@></>
+ <literal><@</>
+ <literal>@>></>
+ <literal><<@</>
+ <literal>=</>
+ <literal><></>
<literal>></>
<literal>>=</>
- <literal><></>
- <literal><<</>
- <literal><<=</>
<literal><</>
<literal><=</>
- <literal>=</>
+ <literal><<</>
+ <literal><<=</>
+ <literal>>></>
+ <literal>>>=</>
</entry>
</row>
</tbody>
</tgroup>
</table>
<para>
Of the two operator classes for type <type>point</>,
<literal>quad_point_ops</> is the default. <literal>kd_point_ops</>
supports the same operators but uses a different index data structure which
diff --git a/src/backend/utils/adt/network_gist.c b/src/backend/utils/adt/network_gist.c
index 2caff94..c994b92a 100644
--- a/src/backend/utils/adt/network_gist.c
+++ b/src/backend/utils/adt/network_gist.c
@@ -55,23 +55,27 @@
* Operator strategy numbers used in the GiST inet_ops opclass
*/
#define INETSTRAT_OVERLAPS RTOverlapStrategyNumber
#define INETSTRAT_EQ RTEqualStrategyNumber
#define INETSTRAT_NE RTNotEqualStrategyNumber
#define INETSTRAT_LT RTLessStrategyNumber
#define INETSTRAT_LE RTLessEqualStrategyNumber
#define INETSTRAT_GT RTGreaterStrategyNumber
#define INETSTRAT_GE RTGreaterEqualStrategyNumber
#define INETSTRAT_SUB RTSubStrategyNumber
+#define INETSTRAT_SUBNET RTOldContainedByStrategyNumber
#define INETSTRAT_SUBEQ RTSubEqualStrategyNumber
+#define INETSTRAT_CONTAINED RTContainedByStrategyNumber
#define INETSTRAT_SUP RTSuperStrategyNumber
+#define INETSTRAT_SUPERNET RTOldContainsStrategyNumber
#define INETSTRAT_SUPEQ RTSuperEqualStrategyNumber
+#define INETSTRAT_CONTAINS RTContainsStrategyNumber
/*
* Representation of a GiST INET/CIDR index key. This is not identical to
* INET/CIDR because we need to keep track of the length of the common address
* prefix as well as the minimum netmask length. However, as long as it
* follows varlena header rules, the core GiST code won't know the difference.
* For simplicity we always use 1-byte-header varlena format.
*/
typedef struct GistInetKey
@@ -169,36 +173,40 @@ inet_gist_consistent(PG_FUNCTION_ARGS)
* Check 2: network bit count
*
* Network bit count (ip_bits) helps to check leaves for sub network and
* sup network operators. At non-leaf nodes, we know every child value
* has ip_bits >= gk_ip_minbits(key), so we can avoid descending in some
* cases too.
*/
switch (strategy)
{
case INETSTRAT_SUB:
+ case INETSTRAT_SUBNET:
if (GIST_LEAF(ent) && gk_ip_minbits(key) <= ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUBEQ:
+ case INETSTRAT_CONTAINED:
if (GIST_LEAF(ent) && gk_ip_minbits(key) < ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUPEQ:
+ case INETSTRAT_CONTAINS:
case INETSTRAT_EQ:
if (gk_ip_minbits(key) > ip_bits(query))
PG_RETURN_BOOL(false);
break;
case INETSTRAT_SUP:
+ case INETSTRAT_SUPERNET:
if (gk_ip_minbits(key) >= ip_bits(query))
PG_RETURN_BOOL(false);
break;
}
/*
* Check 3: common network bits
*
* Compare available common prefix bits to the query, but not beyond
* either the query's netmask or the minimum netmask among the represented
@@ -210,24 +218,28 @@ inet_gist_consistent(PG_FUNCTION_ARGS)
* network part of the address.
*/
minbits = Min(gk_ip_commonbits(key), gk_ip_minbits(key));
minbits = Min(minbits, ip_bits(query));
order = bitncmp(gk_ip_addr(key), ip_addr(query), minbits);
switch (strategy)
{
case INETSTRAT_SUB:
+ case INETSTRAT_SUBNET:
case INETSTRAT_SUBEQ:
+ case INETSTRAT_CONTAINED:
case INETSTRAT_OVERLAPS:
case INETSTRAT_SUPEQ:
+ case INETSTRAT_CONTAINS:
case INETSTRAT_SUP:
+ case INETSTRAT_SUPERNET:
PG_RETURN_BOOL(order == 0);
case INETSTRAT_LT:
case INETSTRAT_LE:
if (order > 0)
PG_RETURN_BOOL(false);
if (order < 0 || !GIST_LEAF(ent))
PG_RETURN_BOOL(true);
break;
diff --git a/src/backend/utils/adt/network_selfuncs.c b/src/backend/utils/adt/network_selfuncs.c
index 2e39687..bd369bc 100644
--- a/src/backend/utils/adt/network_selfuncs.c
+++ b/src/backend/utils/adt/network_selfuncs.c
@@ -29,21 +29,21 @@
/* Default selectivity for the inet overlap operator */
#define DEFAULT_OVERLAP_SEL 0.01
/* Default selectivity for the various inclusion operators */
#define DEFAULT_INCLUSION_SEL 0.005
/* Default selectivity for specified operator */
#define DEFAULT_SEL(operator) \
- ((operator) == OID_INET_OVERLAP_OP ? \
+ ((operator) == OID_INET_OVERLAPS_OP ? \
DEFAULT_OVERLAP_SEL : DEFAULT_INCLUSION_SEL)
/* Maximum number of items to consider in join selectivity calculations */
#define MAX_CONSIDERED_ELEMS 1024
static Selectivity networkjoinsel_inner(Oid operator,
VariableStatData *vardata1, VariableStatData *vardata2);
static Selectivity networkjoinsel_semi(Oid operator,
VariableStatData *vardata1, VariableStatData *vardata2);
static Selectivity mcv_population(float4 *mcv_numbers, int mcv_nvalues);
@@ -865,28 +865,32 @@ inet_semi_join_sel(Datum lhs_value,
* on the exact codes assigned here; but many other places in this file
* know that they can negate a code to obtain the code for the commutator
* operator.
*/
static int
inet_opr_codenum(Oid operator)
{
switch (operator)
{
case OID_INET_SUP_OP:
+ case OID_INET_SUPERNET_OP:
return -2;
case OID_INET_SUPEQ_OP:
+ case OID_INET_CONTAINS_OP:
return -1;
- case OID_INET_OVERLAP_OP:
+ case OID_INET_OVERLAPS_OP:
return 0;
+ case OID_INET_CONTAINED_OP:
case OID_INET_SUBEQ_OP:
return 1;
case OID_INET_SUB_OP:
+ case OID_INET_SUBNET_OP:
return 2;
default:
elog(ERROR, "unrecognized operator %u for inet selectivity",
operator);
}
return 0; /* unreached, but keep compiler quiet */
}
/*
* Comparison function for the subnet inclusion/overlap operators
diff --git a/src/backend/utils/adt/network_spgist.c b/src/backend/utils/adt/network_spgist.c
index a198a83..95c6119 100644
--- a/src/backend/utils/adt/network_spgist.c
+++ b/src/backend/utils/adt/network_spgist.c
@@ -436,37 +436,41 @@ inet_spg_consistent_bitmap(const inet *prefix, int nkeys, ScanKey scankeys,
* too.
*
* This check is less expensive than checking the address bits, so we
* are doing this before, but it has to be done after for the basic
* comparison strategies, because ip_bits only affect their results
* when the common network bits are the same.
*/
switch (strategy)
{
case RTSubStrategyNumber:
+ case RTOldContainedByStrategyNumber:
if (commonbits <= ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
break;
case RTSubEqualStrategyNumber:
+ case RTContainedByStrategyNumber:
if (commonbits < ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
break;
case RTSuperStrategyNumber:
+ case RTOldContainsStrategyNumber:
if (commonbits == ip_bits(argument) - 1)
bitmap &= 1 | (1 << 1);
else if (commonbits >= ip_bits(argument))
bitmap = 0;
break;
case RTSuperEqualStrategyNumber:
+ case RTContainsStrategyNumber:
if (commonbits == ip_bits(argument))
bitmap &= 1 | (1 << 1);
else if (commonbits > ip_bits(argument))
bitmap = 0;
break;
case RTEqualStrategyNumber:
if (commonbits < ip_bits(argument))
bitmap &= (1 << 2) | (1 << 3);
else if (commonbits == ip_bits(argument))
diff --git a/src/include/access/stratnum.h b/src/include/access/stratnum.h
index eabced5..2916815 100644
--- a/src/include/access/stratnum.h
+++ b/src/include/access/stratnum.h
@@ -57,19 +57,19 @@ typedef uint16 StrategyNumber;
#define RTOldContainedByStrategyNumber 14 /* for old spelling of <@ */
#define RTKNNSearchStrategyNumber 15 /* for <-> (distance) */
#define RTContainsElemStrategyNumber 16 /* for range types @> elem */
#define RTAdjacentStrategyNumber 17 /* for -|- */
#define RTEqualStrategyNumber 18 /* for = */
#define RTNotEqualStrategyNumber 19 /* for != */
#define RTLessStrategyNumber 20 /* for < */
#define RTLessEqualStrategyNumber 21 /* for <= */
#define RTGreaterStrategyNumber 22 /* for > */
#define RTGreaterEqualStrategyNumber 23 /* for >= */
-#define RTSubStrategyNumber 24 /* for inet >> */
+#define RTSubStrategyNumber 24 /* for inet << */
#define RTSubEqualStrategyNumber 25 /* for inet <<= */
-#define RTSuperStrategyNumber 26 /* for inet << */
+#define RTSuperStrategyNumber 26 /* for inet >> */
#define RTSuperEqualStrategyNumber 27 /* for inet >>= */
#define RTMaxStrategyNumber 27
#endif /* STRATNUM_H */
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index e4c3515..28f4162 100644
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -842,45 +842,53 @@ DATA(insert ( 5000 603 603 7 s 498 4000 0 ));
DATA(insert ( 5000 603 603 8 s 497 4000 0 ));
DATA(insert ( 5000 603 603 9 s 2571 4000 0 ));
DATA(insert ( 5000 603 603 10 s 2570 4000 0 ));
DATA(insert ( 5000 603 603 11 s 2573 4000 0 ));
DATA(insert ( 5000 603 603 12 s 2572 4000 0 ));
/*
* GiST inet_ops
*/
DATA(insert ( 3550 869 869 3 s 3552 783 0 ));
+DATA(insert ( 3550 869 869 7 s 3453 783 0 ));
+DATA(insert ( 3550 869 869 8 s 3451 783 0 ));
+DATA(insert ( 3550 869 869 13 s 3452 783 0 ));
+DATA(insert ( 3550 869 869 14 s 3450 783 0 ));
DATA(insert ( 3550 869 869 18 s 1201 783 0 ));
DATA(insert ( 3550 869 869 19 s 1202 783 0 ));
DATA(insert ( 3550 869 869 20 s 1203 783 0 ));
DATA(insert ( 3550 869 869 21 s 1204 783 0 ));
DATA(insert ( 3550 869 869 22 s 1205 783 0 ));
DATA(insert ( 3550 869 869 23 s 1206 783 0 ));
DATA(insert ( 3550 869 869 24 s 931 783 0 ));
DATA(insert ( 3550 869 869 25 s 932 783 0 ));
DATA(insert ( 3550 869 869 26 s 933 783 0 ));
DATA(insert ( 3550 869 869 27 s 934 783 0 ));
/*
* SP-GiST inet_ops
*/
DATA(insert ( 3794 869 869 3 s 3552 4000 0 ));
+DATA(insert ( 3794 869 869 7 s 3453 4000 0 ));
+DATA(insert ( 3794 869 869 8 s 3451 4000 0 ));
+DATA(insert ( 3794 869 869 13 s 3452 4000 0 ));
+DATA(insert ( 3794 869 869 14 s 3450 4000 0 ));
DATA(insert ( 3794 869 869 18 s 1201 4000 0 ));
DATA(insert ( 3794 869 869 19 s 1202 4000 0 ));
DATA(insert ( 3794 869 869 20 s 1203 4000 0 ));
DATA(insert ( 3794 869 869 21 s 1204 4000 0 ));
DATA(insert ( 3794 869 869 22 s 1205 4000 0 ));
DATA(insert ( 3794 869 869 23 s 1206 4000 0 ));
-DATA(insert ( 3794 869 869 24 s 931 4000 0 ));
-DATA(insert ( 3794 869 869 25 s 932 4000 0 ));
-DATA(insert ( 3794 869 869 26 s 933 4000 0 ));
-DATA(insert ( 3794 869 869 27 s 934 4000 0 ));
+DATA(insert ( 3794 869 869 24 s 931 4000 0 ));
+DATA(insert ( 3794 869 869 25 s 932 4000 0 ));
+DATA(insert ( 3794 869 869 26 s 933 4000 0 ));
+DATA(insert ( 3794 869 869 27 s 934 4000 0 ));
/* BRIN opclasses */
/* minmax bytea */
DATA(insert ( 4064 17 17 1 s 1957 3580 0 ));
DATA(insert ( 4064 17 17 2 s 1958 3580 0 ));
DATA(insert ( 4064 17 17 3 s 1955 3580 0 ));
DATA(insert ( 4064 17 17 4 s 1960 3580 0 ));
DATA(insert ( 4064 17 17 5 s 1959 3580 0 ));
/* minmax "char" */
DATA(insert ( 4062 18 18 1 s 631 3580 0 ));
@@ -1000,25 +1008,29 @@ DATA(insert ( 4074 829 829 3 s 1220 3580 0 ));
DATA(insert ( 4074 829 829 4 s 1225 3580 0 ));
DATA(insert ( 4074 829 829 5 s 1224 3580 0 ));
/* minmax inet */
DATA(insert ( 4075 869 869 1 s 1203 3580 0 ));
DATA(insert ( 4075 869 869 2 s 1204 3580 0 ));
DATA(insert ( 4075 869 869 3 s 1201 3580 0 ));
DATA(insert ( 4075 869 869 4 s 1206 3580 0 ));
DATA(insert ( 4075 869 869 5 s 1205 3580 0 ));
/* inclusion inet */
DATA(insert ( 4102 869 869 3 s 3552 3580 0 ));
-DATA(insert ( 4102 869 869 7 s 934 3580 0 ));
-DATA(insert ( 4102 869 869 8 s 932 3580 0 ));
+DATA(insert ( 4102 869 869 7 s 3453 3580 0 ));
+DATA(insert ( 4102 869 869 8 s 3451 3580 0 ));
+DATA(insert ( 4102 869 869 13 s 3452 3580 0 ));
+DATA(insert ( 4102 869 869 14 s 3450 3580 0 ));
DATA(insert ( 4102 869 869 18 s 1201 3580 0 ));
DATA(insert ( 4102 869 869 24 s 933 3580 0 ));
+DATA(insert ( 4102 869 869 25 s 934 3580 0 ));
DATA(insert ( 4102 869 869 26 s 931 3580 0 ));
+DATA(insert ( 4102 869 869 27 s 932 3580 0 ));
/* minmax character */
DATA(insert ( 4076 1042 1042 1 s 1058 3580 0 ));
DATA(insert ( 4076 1042 1042 2 s 1059 3580 0 ));
DATA(insert ( 4076 1042 1042 3 s 1054 3580 0 ));
DATA(insert ( 4076 1042 1042 4 s 1061 3580 0 ));
DATA(insert ( 4076 1042 1042 5 s 1060 3580 0 ));
/* minmax time without time zone */
DATA(insert ( 4077 1083 1083 1 s 1110 3580 0 ));
DATA(insert ( 4077 1083 1083 2 s 1111 3580 0 ));
DATA(insert ( 4077 1083 1083 3 s 1108 3580 0 ));
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index 26fa618..dc97c6e 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -1141,34 +1141,46 @@ DATA(insert OID = 1202 ( "<>" PGNSP PGUID b f f 869 869 16 1202 1201 networ
DESCR("not equal");
DATA(insert OID = 1203 ( "<" PGNSP PGUID b f f 869 869 16 1205 1206 network_lt scalarltsel scalarltjoinsel ));
DESCR("less than");
DATA(insert OID = 1204 ( "<=" PGNSP PGUID b f f 869 869 16 1206 1205 network_le scalarltsel scalarltjoinsel ));
DESCR("less than or equal");
DATA(insert OID = 1205 ( ">" PGNSP PGUID b f f 869 869 16 1203 1204 network_gt scalargtsel scalargtjoinsel ));
DESCR("greater than");
DATA(insert OID = 1206 ( ">=" PGNSP PGUID b f f 869 869 16 1204 1203 network_ge scalargtsel scalargtjoinsel ));
DESCR("greater than or equal");
DATA(insert OID = 931 ( "<<" PGNSP PGUID b f f 869 869 16 933 0 network_sub networksel networkjoinsel ));
-DESCR("is subnet");
+DESCR("deprecated, use <<@ instead");
#define OID_INET_SUB_OP 931
+DATA(insert OID = 3450 ( "<<@" PGNSP PGUID b f f 869 869 16 3452 0 network_sub networksel networkjoinsel ));
+DESCR("is subnet");
+#define OID_INET_SUBNET_OP 3450
DATA(insert OID = 932 ( "<<=" PGNSP PGUID b f f 869 869 16 934 0 network_subeq networksel networkjoinsel ));
-DESCR("is subnet or equal");
+DESCR("deprecated, use <@ instead");
#define OID_INET_SUBEQ_OP 932
+DATA(insert OID = 3451 ( "<@" PGNSP PGUID b f f 869 869 16 3453 0 network_subeq networksel networkjoinsel ));
+DESCR("is contained by");
+#define OID_INET_CONTAINED_OP 3451
DATA(insert OID = 933 ( ">>" PGNSP PGUID b f f 869 869 16 931 0 network_sup networksel networkjoinsel ));
-DESCR("is supernet");
+DESCR("deprecated, use @>> instead");
#define OID_INET_SUP_OP 933
+DATA(insert OID = 3452 ( "@>>" PGNSP PGUID b f f 869 869 16 3450 0 network_sup networksel networkjoinsel ));
+DESCR("is supernet");
+#define OID_INET_SUPERNET_OP 3452
DATA(insert OID = 934 ( ">>=" PGNSP PGUID b f f 869 869 16 932 0 network_supeq networksel networkjoinsel ));
-DESCR("is supernet or equal");
+DESCR("deprecated, use @> instead");
#define OID_INET_SUPEQ_OP 934
-DATA(insert OID = 3552 ( "&&" PGNSP PGUID b f f 869 869 16 3552 0 network_overlap networksel networkjoinsel ));
-DESCR("overlaps (is subnet or supernet)");
-#define OID_INET_OVERLAP_OP 3552
+DATA(insert OID = 3453 ( "@>" PGNSP PGUID b f f 869 869 16 3451 0 network_supeq networksel networkjoinsel ));
+DESCR("contains");
+#define OID_INET_CONTAINS_OP 3453
+DATA(insert OID = 3552 ( "&&" PGNSP PGUID b f f 869 869 16 3552 0 network_overlap networksel networkjoinsel ));
+DESCR("overlaps (contains or is contained by)");
+#define OID_INET_OVERLAPS_OP 3552
DATA(insert OID = 2634 ( "~" PGNSP PGUID l f f 0 869 869 0 0 inetnot - - ));
DESCR("bitwise not");
DATA(insert OID = 2635 ( "&" PGNSP PGUID b f f 869 869 869 0 0 inetand - - ));
DESCR("bitwise and");
DATA(insert OID = 2636 ( "|" PGNSP PGUID b f f 869 869 869 0 0 inetor - - ));
DESCR("bitwise or");
DATA(insert OID = 2637 ( "+" PGNSP PGUID b f f 869 20 869 2638 0 inetpl - - ));
DESCR("add");
DATA(insert OID = 2638 ( "+" PGNSP PGUID b f f 20 869 869 2637 0 int8pl_inet - - ));
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index 21676e5..9a47b57 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -173,49 +173,69 @@ INSERT INTO brinopers VALUES
'{99, 100, 1, 100, 100}'),
('float8col', 'float8',
'{>, >=, =, <=, <}',
'{0, 0, 0, 1.98, 1.98}',
'{99, 100, 1, 100, 100}'),
('macaddrcol', 'macaddr',
'{>, >=, =, <=, <}',
'{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}',
'{99, 100, 2, 100, 100}'),
('inetcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2/10, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
+ ('inetcol', 'inet', -- Deprecated operators
+ '{>>=, >>, <<=, <<}',
+ '{10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{2, 2, 100, 100}'),
+ ('inetcol', 'inet', -- Basic comparison operators
+ '{=, <, <=, >, >=}',
+ '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{1, 100, 100, 125, 125}'),
('inetcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}', -- IPv6
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
+ ('inetcol', 'cidr', -- Cross data-type
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 10.0/9}',
+ '{100, 2, 100, 2, 100}'),
('inetcol', 'cidr',
- '{&&, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{<, <=, >, >=}',
+ '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{100, 100, 125, 125}'),
('inetcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2.14.231/8, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'inet',
+ '{&&, @>, <@, =}',
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'cidr',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'cidr',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('bpcharcol', 'bpchar',
'{>, >=, =, <=, <}',
'{A, A, W, Z, Z}',
'{97, 100, 6, 100, 98}'),
('datecol', 'date',
'{>, >=, =, <=, <}',
'{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
'{100, 100, 1, 100, 100}'),
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index be9427e..74a47cf 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -172,25 +172,25 @@ SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
WHERE c = i;
six | cidr | inet
-----+----------------+----------------
| 192.168.1.0/24 | 192.168.1.0/24
| 10.1.2.3/32 | 10.1.2.3
(2 rows)
SELECT '' AS ten, i, c,
i < c AS lt, i <= c AS le, i = c AS eq,
i >= c AS ge, i > c AS gt, i <> c AS ne,
- i << c AS sb, i <<= c AS sbe,
- i >> c AS sup, i >>= c AS spe,
+ i <<@ c AS sb, i <@ c AS cnd,
+ i @>> c AS sup, i @> c AS cns,
i && c AS ovr
FROM INET_TBL;
- ten | i | c | lt | le | eq | ge | gt | ne | sb | sbe | sup | spe | ovr
+ ten | i | c | lt | le | eq | ge | gt | ne | sb | cnd | sup | cns | ovr
-----+------------------+--------------------+----+----+----+----+----+----+----+-----+-----+-----+-----
| 192.168.1.226/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t | t
| 192.168.1.226 | 192.168.1.0/26 | f | f | f | t | t | t | f | f | f | f | f
| 192.168.1.0/24 | 192.168.1.0/24 | f | t | t | t | f | f | f | t | f | t | t
| 192.168.1.0/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f | t
| 192.168.1.255/24 | 192.168.1.0/24 | f | f | f | t | t | t | f | t | f | t | t
| 192.168.1.255/25 | 192.168.1.0/24 | f | f | f | t | t | t | t | t | f | f | t
| 10.1.2.3/8 | 10.0.0.0/8 | f | f | f | t | t | t | f | t | f | t | t
| 10.1.2.3/8 | 10.0.0.0/32 | t | t | f | f | f | t | f | f | t | t | t
| 10.1.2.3 | 10.1.2.3/32 | f | t | t | t | f | f | f | t | f | t | t
@@ -267,55 +267,87 @@ DROP INDEX inet_idx1;
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(3 rows)
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(3 rows)
+
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(6 rows)
+
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
(3 rows)
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+(3 rows)
+
SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
c | i
---+---
(0 rows)
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+---+---
+(0 rows)
+
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
10.1.2.3/32 | 10.1.2.3
@@ -407,29 +439,29 @@ SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
192.168.1.0/25
192.168.1.255/25
192.168.1.226
(3 rows)
SET enable_seqscan TO on;
DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
SET enable_seqscan TO off;
-SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(3 rows)
-SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
@@ -437,29 +469,29 @@ SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
(6 rows)
-SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
c | i
----------------+------------------
192.168.1.0/24 | 192.168.1.0/24
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
(3 rows)
-SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
c | i
---+---
(0 rows)
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
@@ -531,32 +563,64 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
192.168.1.0/24 | 192.168.1.226/24
192.168.1.0/24 | 192.168.1.255/24
192.168.1.0/24 | 192.168.1.0/25
192.168.1.0/24 | 192.168.1.255/25
192.168.1.0/26 | 192.168.1.226
::ffff:1.2.3.4/128 | ::4.3.2.1/24
10:23::f1/128 | 10:23::f1/64
10:23::8000/113 | 10:23::ffff
(16 rows)
+SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(3 rows)
+
+SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+ 192.168.1.0/24 | 192.168.1.0/25
+ 192.168.1.0/24 | 192.168.1.255/25
+ 192.168.1.0/26 | 192.168.1.226
+(6 rows)
+
+SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+----------------+------------------
+ 192.168.1.0/24 | 192.168.1.0/24
+ 192.168.1.0/24 | 192.168.1.226/24
+ 192.168.1.0/24 | 192.168.1.255/24
+(3 rows)
+
+SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+ c | i
+---+---
+(0 rows)
+
-- test index-only scans
EXPLAIN (COSTS OFF)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
- QUERY PLAN
----------------------------------------------------
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+ QUERY PLAN
+----------------------------------------------------
Sort
Sort Key: i
-> Index Only Scan using inet_idx3 on inet_tbl
- Index Cond: (i << '192.168.1.0/24'::inet)
+ Index Cond: (i <<@ '192.168.1.0/24'::inet)
(4 rows)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
i
------------------
192.168.1.0/25
192.168.1.255/25
192.168.1.226
(3 rows)
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
-- simple tests of inet boolean and arithmetic operators
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 0bcec13..47b4f6b 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -953,37 +953,38 @@ ORDER BY 1, 2;
*< | *>
*<= | *>=
*<> | *<>
*= | *=
+ | +
-|- | -|-
< | >
<-> | <->
<< | >>
<<= | >>=
+ <<@ | @>>
<= | >=
<> | <>
<@ | @>
= | =
?# | ?#
?- | ?-
?-| | ?-|
?| | ?|
?|| | ?||
@ | ~
@@ | @@
@@@ | @@@
| | |
~<=~ | ~>=~
~<~ | ~>~
~= | ~=
-(30 rows)
+(31 rows)
-- Likewise for negator pairs.
SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2
FROM pg_operator o1, pg_operator o2
WHERE o1.oprnegate = o2.oid AND o1.oprname <= o2.oprname
ORDER BY 1, 2;
op1 | op2
------+------
!~ | ~
!~* | ~*
@@ -1731,21 +1732,23 @@ ORDER BY 1, 2, 3;
783 | 6 | -|-
783 | 6 | ~=
783 | 7 | @>
783 | 8 | <@
783 | 9 | &<|
783 | 10 | <<|
783 | 10 | <^
783 | 11 | >^
783 | 11 | |>>
783 | 12 | |&>
+ 783 | 13 | @>>
783 | 13 | ~
+ 783 | 14 | <<@
783 | 14 | @
783 | 15 | <->
783 | 16 | @>
783 | 18 | =
783 | 19 | <>
783 | 20 | <
783 | 21 | <=
783 | 22 | >
783 | 23 | >=
783 | 24 | <<
@@ -1769,37 +1772,39 @@ ORDER BY 1, 2, 3;
3580 | 1 | <<
3580 | 2 | &<
3580 | 2 | <=
3580 | 3 | &&
3580 | 3 | =
3580 | 4 | &>
3580 | 4 | >=
3580 | 5 | >
3580 | 5 | >>
3580 | 6 | ~=
- 3580 | 7 | >>=
3580 | 7 | @>
- 3580 | 8 | <<=
3580 | 8 | <@
3580 | 9 | &<|
3580 | 10 | <<|
3580 | 11 | |>>
3580 | 12 | |&>
+ 3580 | 13 | @>>
+ 3580 | 14 | <<@
3580 | 16 | @>
3580 | 17 | -|-
3580 | 18 | =
3580 | 20 | <
3580 | 21 | <=
3580 | 22 | >
3580 | 23 | >=
3580 | 24 | >>
+ 3580 | 25 | >>=
3580 | 26 | <<
+ 3580 | 27 | <<=
4000 | 1 | <<
4000 | 1 | ~<~
4000 | 2 | &<
4000 | 2 | ~<=~
4000 | 3 | &&
4000 | 3 | =
4000 | 4 | &>
4000 | 4 | ~>=~
4000 | 5 | >>
4000 | 5 | ~>~
@@ -1808,34 +1813,36 @@ ORDER BY 1, 2, 3;
4000 | 7 | @>
4000 | 8 | <@
4000 | 9 | &<|
4000 | 10 | <<|
4000 | 10 | <^
4000 | 11 | <
4000 | 11 | >^
4000 | 11 | |>>
4000 | 12 | <=
4000 | 12 | |&>
+ 4000 | 13 | @>>
+ 4000 | 14 | <<@
4000 | 14 | >=
4000 | 15 | >
4000 | 16 | @>
4000 | 18 | =
4000 | 19 | <>
4000 | 20 | <
4000 | 21 | <=
4000 | 22 | >
4000 | 23 | >=
4000 | 24 | <<
4000 | 25 | <<=
4000 | 26 | >>
4000 | 27 | >>=
-(121 rows)
+(127 rows)
-- Check that all opclass search operators have selectivity estimators.
-- This is not absolutely required, but it seems a reasonable thing
-- to insist on for all standard datatypes.
SELECT p1.amopfamily, p1.amopopr, p2.oid, p2.oprname
FROM pg_amop AS p1, pg_operator AS p2
WHERE p1.amopopr = p2.oid AND p1.amoppurpose = 's' AND
(p2.oprrest = 0 OR p2.oprjoin = 0);
amopfamily | amopopr | oid | oprname
------------+---------+-----+---------
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index e7f6f77..939cfdb 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -178,49 +178,69 @@ INSERT INTO brinopers VALUES
'{99, 100, 1, 100, 100}'),
('float8col', 'float8',
'{>, >=, =, <=, <}',
'{0, 0, 0, 1.98, 1.98}',
'{99, 100, 1, 100, 100}'),
('macaddrcol', 'macaddr',
'{>, >=, =, <=, <}',
'{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}',
'{99, 100, 2, 100, 100}'),
('inetcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2/10, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
+ ('inetcol', 'inet', -- Deprecated operators
+ '{>>=, >>, <<=, <<}',
+ '{10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{2, 2, 100, 100}'),
+ ('inetcol', 'inet', -- Basic comparison operators
+ '{=, <, <=, >, >=}',
+ '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{1, 100, 100, 125, 125}'),
('inetcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}', -- IPv6
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
+ ('inetcol', 'cidr', -- Cross data-type
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 10.0/9}',
+ '{100, 2, 100, 2, 100}'),
('inetcol', 'cidr',
- '{&&, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{<, <=, >, >=}',
+ '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{100, 100, 125, 125}'),
('inetcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'inet',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14.231/24, 10.2.14.231/8, 10.2.14.231/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'inet',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'inet',
+ '{&&, @>, <@, =}',
'{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('cidrcol', 'cidr',
- '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
- '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
- '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ '{&&, @>, <@, @>>, <<@}',
+ '{10/8, 10.2.14/24, 10/8, 10.2.14/25, 0/0}',
+ '{100, 2, 100, 2, 100}'),
('cidrcol', 'cidr',
- '{&&, >>=, <<=, =}',
+ '{=, <, <=, >, >=}',
+ '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
+ '{2, 100, 100, 125, 125}'),
+ ('cidrcol', 'cidr',
+ '{&&, @>, <@, =}',
'{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
'{25, 1, 25, 1}'),
('bpcharcol', 'bpchar',
'{>, >=, =, <=, <}',
'{A, A, W, Z, Z}',
'{97, 100, 6, 100, 98}'),
('datecol', 'date',
'{>, >=, =, <=, <}',
'{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
'{100, 100, 1, 100, 100}'),
diff --git a/src/test/regress/sql/inet.sql b/src/test/regress/sql/inet.sql
index 880e115..6642d2d 100644
--- a/src/test/regress/sql/inet.sql
+++ b/src/test/regress/sql/inet.sql
@@ -44,22 +44,22 @@ SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)",
SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)",
i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL
WHERE masklen(c) <= 8;
SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL
WHERE c = i;
SELECT '' AS ten, i, c,
i < c AS lt, i <= c AS le, i = c AS eq,
i >= c AS ge, i > c AS gt, i <> c AS ne,
- i << c AS sb, i <<= c AS sbe,
- i >> c AS sup, i >>= c AS spe,
+ i <<@ c AS sb, i <@ c AS cnd,
+ i @>> c AS sup, i @> c AS cns,
i && c AS ovr
FROM INET_TBL;
SELECT max(i) AS max, min(i) AS min FROM INET_TBL;
SELECT max(c) AS max, min(c) AS min FROM INET_TBL;
-- check the conversion to/from text and set_netmask
SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
-- check that btree index works correctly
@@ -67,58 +67,66 @@ CREATE INDEX inet_idx1 ON inet_tbl(i);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
SET enable_seqscan TO on;
DROP INDEX inet_idx1;
-- check that gist index works correctly
CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops);
SET enable_seqscan TO off;
SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
-- test index-only scans
EXPLAIN (COSTS OFF)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SET enable_seqscan TO on;
DROP INDEX inet_idx2;
-- check that spgist index works correctly
CREATE INDEX inet_idx3 ON inet_tbl using spgist (i);
SET enable_seqscan TO off;
-SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <@ '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
-SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i @>> '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i;
SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i;
+SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i;
-- test index-only scans
EXPLAIN (COSTS OFF)
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
-SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
+SELECT i FROM inet_tbl WHERE i <<@ '192.168.1.0/24'::cidr ORDER BY i;
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
-- simple tests of inet boolean and arithmetic operators
SELECT i, ~i AS "~i" FROM inet_tbl;
SELECT i, c, i & c AS "and" FROM inet_tbl;
SELECT i, c, i | c AS "or" FROM inet_tbl;
SELECT i, i + 500 AS "i+500" FROM inet_tbl;
SELECT i, i - 500 AS "i-500" FROM inet_tbl;
--
2.9.3 (Apple Git-75)
Re: Contains and is contained by operators of inet datatypes
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Nov 21, 2016 at 7:57 AM, Andreas Karlsson <andreas@proxel.se> wrote:
I like the patch because it means less operators to remember for me as a
PostgreSQL user. And at least for me inet is a rarely used type compared to
hstore, json and range types which all use @> and <@.
I agree that it would be nice to make the choice of operator names
more consistent. I don't know if doing so will please more or fewer
people than it annoys.
Given the lack of support for this idea from the rest of the community,
I think it's time to reject this patch and move on. The inconsistency
is unfortunate but it does not seem worth the costs of changing.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers