On Wed, Jul 12, 2017 at 12:53 AM, Alvaro Herrera <[email protected]>
wrote:
>
> We have one opclass for each type combination -- int4 to int2, int4 to
> int4, int4 to int8, etc. You just need to add the new strategy to all
> the opclasses.
I tried this approach by manually declaring the operator multiple of times
in pg_amop.h (src/include/catalog/pg_amop.h)
so instead of the polymorphic declaration
DATA(insert ( 2745 2277 2283 5 s 6108 2742 0 )); /* anyarray @>> anyelem
*/
multiple declarations were used, for example for int4[] :
DATA(insert ( 2745 1007 20 5 s 6108 2742 0 )); /* int4[] @>> int8 */
DATA(insert ( 2745 1007 23 5 s 6108 2742 0 )); /* int4[] @>> int4 */
DATA(insert ( 2745 1007 21 5 s 6108 2742 0 )); /* int4[] @>> int2 */
DATA(insert ( 2745 1007 1700 5 s 6108 2742 0 ));/* int4[] @>> numeric */
However, make check produced:
could not create unique index "pg_amop_opr_fam_index"
Key (amopopr, amoppurpose, amopfamily)=(6108, s, 2745) is duplicated.
Am I implementing this the wrong way or do we need to look for another
approach?
diff --git a/src/backend/access/gin/ginarrayproc.c b/src/backend/access/gin/ginarrayproc.c
index a5238c3af5..9d6447923d 100644
--- a/src/backend/access/gin/ginarrayproc.c
+++ b/src/backend/access/gin/ginarrayproc.c
@@ -24,6 +24,7 @@
#define GinContainsStrategy 2
#define GinContainedStrategy 3
#define GinEqualStrategy 4
+#define GinContainsElemStrategy 5
/*
@@ -43,7 +44,7 @@ ginarrayextract(PG_FUNCTION_ARGS)
bool *nulls;
int nelems;
- get_typlenbyvalalign(ARR_ELEMTYPE(array),
+ get_typlenbyvalalign(ARR_ELEMTYPE(array),
&elmlen, &elmbyval, &elmalign);
deconstruct_array(array,
@@ -110,6 +111,11 @@ ginqueryarrayextract(PG_FUNCTION_ARGS)
case GinOverlapStrategy:
*searchMode = GIN_SEARCH_MODE_DEFAULT;
break;
+ case GinContainsElemStrategy:
+ /* only items that match the queried element
+ are considered candidate */
+ *searchMode = GIN_SEARCH_MODE_DEFAULT;
+ break;
case GinContainsStrategy:
if (nelems > 0)
*searchMode = GIN_SEARCH_MODE_DEFAULT;
@@ -171,6 +177,7 @@ ginarrayconsistent(PG_FUNCTION_ARGS)
}
}
break;
+ case GinContainsElemStrategy:
case GinContainsStrategy:
/* result is not lossy */
*recheck = false;
@@ -258,7 +265,8 @@ ginarraytriconsistent(PG_FUNCTION_ARGS)
}
}
break;
- case GinContainsStrategy:
+ case GinContainsElemStrategy:
+ case GinContainsStrategy:
/* must have all elements in check[] true, and no nulls */
res = GIN_TRUE;
for (i = 0; i < nkeys; i++)
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index 34dadd6e19..8c9eb0c676 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -4232,6 +4232,117 @@ arraycontained(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(result);
}
+/*
+ * array_contains_elem : checks an array for a spefific element
+ */
+static bool
+array_contains_elem(AnyArrayType *array, Datum elem, Oid element_type,
+ bool element_isnull, Oid collation, void **fn_extra)
+{
+ Oid arr_type = AARR_ELEMTYPE(array);
+ TypeCacheEntry *typentry;
+ int nelems;
+ int typlen;
+ bool typbyval;
+ char typalign;
+ int i;
+ array_iter it1;
+ FunctionCallInfoData locfcinfo;
+
+ if (arr_type != element_type)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot compare different element types")));
+
+ if (element_isnull)
+ return false;
+
+ /*
+ * We arrange to look up the equality function only once per series of
+ * calls, assuming the element type doesn't change underneath us. The
+ * typcache is used so that we have no memory leakage when being used as
+ * an index support function.
+ */
+ typentry = (TypeCacheEntry *)*fn_extra;
+ if (typentry == NULL ||
+ typentry->type_id != arr_type)
+ {
+ typentry = lookup_type_cache(arr_type,
+ TYPECACHE_EQ_OPR_FINFO);
+ if (!OidIsValid(typentry->eq_opr_finfo.fn_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an equality operator for type %s",
+ format_type_be(arr_type))));
+ *fn_extra = (void *)typentry;
+ }
+ typlen = typentry->typlen;
+ typbyval = typentry->typbyval;
+ typalign = typentry->typalign;
+
+ /*
+ * Apply the comparison operator to each pair of array elements.
+ */
+ InitFunctionCallInfoData(locfcinfo, &typentry->eq_opr_finfo, 2,
+ collation, NULL, NULL);
+
+ /* Loop over source data */
+ nelems = ArrayGetNItems(AARR_NDIM(array), AARR_DIMS(array));
+ array_iter_setup(&it1, array);
+
+ for (i = 0; i < nelems; i++)
+ {
+ Datum elt1;
+ bool isnull;
+ bool oprresult;
+
+ /* Get element, checking for NULL */
+ elt1 = array_iter_next(&it1, &isnull, i, typlen, typbyval, typalign);
+
+ /*
+ * We assume that the comparison operator is strict, so a NULL can't
+ * match anything. XXX this diverges from the "NULL=NULL" behavior of
+ * array_eq, should we act like that?
+ */
+ if (isnull)
+ continue;
+
+ /*
+ * Apply the operator to the element pair
+ */
+ locfcinfo.arg[0] = elt1;
+ locfcinfo.arg[1] = elem;
+ locfcinfo.argnull[0] = false;
+ locfcinfo.argnull[1] = false;
+ locfcinfo.isnull = false;
+ oprresult = DatumGetBool(FunctionCallInvoke(&locfcinfo));
+ if (oprresult)
+ return true;
+ }
+
+ return false;
+}
+
+Datum
+arraycontainselem(PG_FUNCTION_ARGS)
+{
+ AnyArrayType *array = PG_GETARG_ANY_ARRAY(0);
+ Datum elem = PG_GETARG_DATUM(1);
+ Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ Oid collation = PG_GET_COLLATION();
+ bool element_isnull = PG_ARGISNULL(1);
+ bool result;
+
+ result = array_contains_elem(array, elem, element_type,
+ element_isnull, collation,
+ &fcinfo->flinfo->fn_extra);
+
+ /* Avoid leaking memory when handed toasted input. */
+ AARR_FREE_IF_COPY(array, 0);
+
+ PG_RETURN_BOOL(result);
+}
+
/*-----------------------------------------------------------------------------
* Array iteration functions
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 3a25ba52f3..9e7d66df7e 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -2650,7 +2650,7 @@ quoteRelationName(char *buffer, Relation rel)
* ri_GenerateQual --- generate a WHERE clause equating two variables
*
* The idea is to append " sep leftop op rightop" to buf, or if fkreftype is
- * FKCONSTR_REF_EACH_ELEMENT, append " sep leftop op ANY(rightop)" to buf.
+ * FKCONSTR_REF_EACH_ELEMENT, append " sep leftop @>> rightop" to buf.
*
* The complexity comes from needing to be sure that the parser will select
* the desired operator. We always name the operator using
@@ -2694,21 +2694,34 @@ ri_GenerateQual(StringInfo buf,
else
oprright = operform->oprright;
- appendStringInfo(buf, " %s %s", sep, leftop);
- if (leftoptype != operform->oprleft)
- ri_add_cast_to(buf, operform->oprleft);
-
- appendStringInfo(buf, " OPERATOR(%s.%s) ",
+ if (fkreftype == FKCONSTR_REF_EACH_ELEMENT){
+ appendStringInfo(buf, " %s %s", sep, rightop);
+
+ if (rightoptype != oprright)
+ ri_add_cast_to(buf, oprright);
+
+ appendStringInfo(buf, " @>> ");
+
+ appendStringInfoString(buf, leftop);
+
+ if (leftoptype != operform->oprleft)
+ ri_add_cast_to(buf, operform->oprleft);
+ }
+ else{
+ appendStringInfo(buf, " %s %s", sep, leftop);
+
+ if (leftoptype != operform->oprleft)
+ ri_add_cast_to(buf, operform->oprleft);
+
+ appendStringInfo(buf, " OPERATOR(%s.%s) ",
quote_identifier(nspname), oprname);
-
- if (fkreftype == FKCONSTR_REF_EACH_ELEMENT)
- appendStringInfoString(buf, "ANY (");
- appendStringInfoString(buf, rightop);
- if (rightoptype != oprright)
- ri_add_cast_to(buf, oprright);
- if (fkreftype == FKCONSTR_REF_EACH_ELEMENT)
- appendStringInfoChar(buf, ')');
+ appendStringInfoString(buf, rightop);
+
+ if (rightoptype != oprright)
+ ri_add_cast_to(buf, oprright);
+ }
+
ReleaseSysCache(opertup);
}
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index f850be490a..5649ecd22d 100644
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -689,7 +689,31 @@ DATA(insert ( 2745 2277 2277 1 s 2750 2742 0 ));
DATA(insert ( 2745 2277 2277 2 s 2751 2742 0 ));
DATA(insert ( 2745 2277 2277 3 s 2752 2742 0 ));
DATA(insert ( 2745 2277 2277 4 s 1070 2742 0 ));
-
+// DATA(insert ( 2745 2277 2283 5 s 6108 2742 0 )); /* anyarray @>> anyelem */
+DATA(insert ( 2745 1000 16 5 s 6108 2742 0 )); /* bool[] @>> bool */
+DATA(insert ( 2745 1001 17 5 s 6108 2742 0 )); /* char[] @>> char */
+DATA(insert ( 2745 1003 18 5 s 6108 2742 0 )); /* name[] @>> name */
+DATA(insert ( 2745 1231 20 5 s 6108 2742 0 )); /* numeric[] @>> int8 */
+DATA(insert ( 2745 1231 23 5 s 6108 2742 0 )); /* numeric[] @>> int4 */
+DATA(insert ( 2745 1231 21 5 s 6108 2742 0 )); /* numeric[] @>> int2 */
+DATA(insert ( 2745 1231 1700 5 s 6108 2742 0 ));/* numeric[] @>> numeric */
+DATA(insert ( 2745 1016 20 5 s 6108 2742 0 )); /* int8[] @>> int8 */
+DATA(insert ( 2745 1016 23 5 s 6108 2742 0 )); /* int8[] @>> int4 */
+DATA(insert ( 2745 1016 21 5 s 6108 2742 0 )); /* int8[] @>> int2 */
+DATA(insert ( 2745 1016 1700 5 s 6108 2742 0 ));/* int8[] @>> numeric */
+DATA(insert ( 2745 1007 20 5 s 6108 2742 0 )); /* int4[] @>> int8 */
+DATA(insert ( 2745 1007 23 5 s 6108 2742 0 )); /* int4[] @>> int4 */
+DATA(insert ( 2745 1007 21 5 s 6108 2742 0 )); /* int4[] @>> int2 */
+DATA(insert ( 2745 1007 1700 5 s 6108 2742 0 ));/* int4[] @>> numeric */
+DATA(insert ( 2745 1005 20 5 s 6108 2742 0 )); /* int2[] @>> int8 */
+DATA(insert ( 2745 1005 23 5 s 6108 2742 0 )); /* int2[] @>> int4 */
+DATA(insert ( 2745 1005 21 5 s 6108 2742 0 )); /* int2[] @>> int2 */
+DATA(insert ( 2745 1005 1700 5 s 6108 2742 0 ));/* int2[] @>> numeric */
+DATA(insert ( 2745 1021 700 5 s 6108 2742 0 )); /* float4[] @>> float4 */
+DATA(insert ( 2745 1021 701 5 s 6108 2742 0 )); /* float4[] @>> float8 */
+DATA(insert ( 2745 1022 700 5 s 6108 2742 0 )); /* float8[] @>> float4 */
+DATA(insert ( 2745 1022 701 5 s 6108 2742 0 )); /* float8[] @>> float8 */
+
/*
* btree enum_ops
*/
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index ffabc2003b..8a9d616d29 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -1570,6 +1570,8 @@ DESCR("contains");
DATA(insert OID = 2752 ( "<@" PGNSP PGUID b f f 2277 2277 16 2751 0 arraycontained arraycontsel arraycontjoinsel ));
DESCR("is contained by");
#define OID_ARRAY_CONTAINED_OP 2752
+DATA(insert OID = 6108 ( "@>>" PGNSP PGUID b f f 2277 2283 16 0 0 arraycontainselem arraycontsel arraycontjoinsel ));
+DESCR("containselem");
/* capturing operators to preserve pre-8.3 behavior of text concatenation */
DATA(insert OID = 2779 ( "||" PGNSP PGUID b f f 25 2776 25 0 0 textanycat - - ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 8b33b4e0ea..01b207d363 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4308,6 +4308,7 @@ DESCR("GIN array support (obsolete)");
DATA(insert OID = 2747 ( arrayoverlap PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "2277 2277" _null_ _null_ _null_ _null_ _null_ arrayoverlap _null_ _null_ _null_ ));
DATA(insert OID = 2748 ( arraycontains PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "2277 2277" _null_ _null_ _null_ _null_ _null_ arraycontains _null_ _null_ _null_ ));
DATA(insert OID = 2749 ( arraycontained PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "2277 2277" _null_ _null_ _null_ _null_ _null_ arraycontained _null_ _null_ _null_ ));
+DATA(insert OID = 6109 ( arraycontainselem PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "2277 2283" _null_ _null_ _null_ _null_ _null_ arraycontainselem _null_ _null_ _null_ ));
/* BRIN minmax */
DATA(insert OID = 3383 ( brin_minmax_opcinfo PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2281 "2281" _null_ _null_ _null_ _null_ _null_ brin_minmax_opcinfo _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index c730563f03..0a9eff4d04 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -737,6 +737,17 @@ SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno;
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)
+SELECT * FROM array_op_test WHERE i @>> 32 ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+ 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
+ 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
+(6 rows)
+
SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
@@ -761,6 +772,19 @@ SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno;
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(8 rows)
+SELECT * FROM array_op_test WHERE i @>> 17 ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
+ 53 | {38,17} | {AAAAAAAAAAA21658}
+ 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+(8 rows)
+
SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
@@ -942,6 +966,11 @@ SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
-------+---+---
(0 rows)
+SELECT * FROM array_op_test WHERE i @>> NULL ORDER BY seqno;
+ seqno | i | t
+-------+---+---
+(0 rows)
+
SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
@@ -953,6 +982,15 @@ SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
101 | {} | {}
(1 row)
+SELECT * FROM array_op_test WHERE t @>> 'AAAAAAAA72908' ORDER BY seqno;
+ seqno | i | t
+-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
+ 22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
+ 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
+ 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+(4 rows)
+
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
seqno | i | t
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
@@ -971,6 +1009,14 @@ SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(4 rows)
+SELECT * FROM array_op_test WHERE t @>> 'AAAAAAAAAA646' ORDER BY seqno;
+ seqno | i | t
+-------+------------------+--------------------------------------------------------------------
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+ 96 | {23,97,43} | {AAAAAAAAAA646,A87088}
+(3 rows)
+
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+------------------+--------------------------------------------------------------------
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index fcf8bd7565..25ef369951 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -1811,6 +1811,7 @@ ORDER BY 1, 2, 3;
2742 | 2 | @@@
2742 | 3 | <@
2742 | 4 | =
+ 2742 | 5 | @>>
2742 | 7 | @>
2742 | 9 | ?
2742 | 10 | ?|
@@ -1878,7 +1879,7 @@ ORDER BY 1, 2, 3;
4000 | 25 | <<=
4000 | 26 | >>
4000 | 27 | >>=
-(121 rows)
+(122 rows)
-- Check that all opclass search operators have selectivity estimators.
-- This is not absolutely required, but it seems a reasonable thing
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 25dd4e2c6d..f6fb507bdd 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -313,8 +313,10 @@ SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno;
+SELECT * FROM array_op_test WHERE i @>> 32 ORDER BY seqno;
SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno;
+SELECT * FROM array_op_test WHERE i @>> 17 ORDER BY seqno;
SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno;
@@ -325,11 +327,14 @@ SELECT * FROM array_op_test WHERE i && '{}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE i <@ '{}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
+SELECT * FROM array_op_test WHERE i @>> NULL ORDER BY seqno;
SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
+SELECT * FROM array_op_test WHERE t @>> 'AAAAAAAA72908' ORDER BY seqno;
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
+SELECT * FROM array_op_test WHERE t @>> 'AAAAAAAAAA646' ORDER BY seqno;
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
