This patch adds support to btree_gist for searching on <> ("not
equals").
This allows an interesting use of exclusion constraints:
Say you have a table:
create table zoo
(
cage int,
animal text,
exclude using gist (cage with =, animal with <>)
);
That will permit you to add as many zebras as you want to a given cage,
and as many lions as you want to another cage, but will not allow you to
mix zebras and lions in the same cage.
It also allows you to enforce the constraint that only one tuple exists
in a table by doing something like:
create table a
(
i int,
exclude using gist (i with <>),
unique (i)
);
Regards,
Jeff Davis
*** a/contrib/btree_gist/btree_gist.h
--- b/contrib/btree_gist/btree_gist.h
***************
*** 9,14 ****
--- 9,16 ----
#include "access/itup.h"
#include "access/nbtree.h"
+ #define BTNotEqualStrategyNumber 6
+
/* indexed types */
enum gbtree_type
*** a/contrib/btree_gist/btree_gist.sql.in
--- b/contrib/btree_gist/btree_gist.sql.in
***************
*** 143,148 **** AS
--- 143,149 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_oid_consistent (internal, oid, int2, oid, internal),
FUNCTION 2 gbt_oid_union (bytea, internal),
FUNCTION 3 gbt_oid_compress (internal),
***************
*** 200,205 **** AS
--- 201,207 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_int2_consistent (internal, int2, int2, oid, internal),
FUNCTION 2 gbt_int2_union (bytea, internal),
FUNCTION 3 gbt_int2_compress (internal),
***************
*** 256,261 **** AS
--- 258,264 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_int4_consistent (internal, int4, int2, oid, internal),
FUNCTION 2 gbt_int4_union (bytea, internal),
FUNCTION 3 gbt_int4_compress (internal),
***************
*** 312,317 **** AS
--- 315,321 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_int8_consistent (internal, int8, int2, oid, internal),
FUNCTION 2 gbt_int8_union (bytea, internal),
FUNCTION 3 gbt_int8_compress (internal),
***************
*** 369,374 **** AS
--- 373,379 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_float4_consistent (internal, float4, int2, oid, internal),
FUNCTION 2 gbt_float4_union (bytea, internal),
FUNCTION 3 gbt_float4_compress (internal),
***************
*** 428,433 **** AS
--- 433,439 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_float8_consistent (internal, float8, int2, oid, internal),
FUNCTION 2 gbt_float8_union (bytea, internal),
FUNCTION 3 gbt_float8_compress (internal),
***************
*** 495,500 **** AS
--- 501,507 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_ts_consistent (internal, timestamp, int2, oid, internal),
FUNCTION 2 gbt_ts_union (bytea, internal),
FUNCTION 3 gbt_ts_compress (internal),
***************
*** 514,519 **** AS
--- 521,527 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_tstz_consistent (internal, timestamptz, int2, oid, internal),
FUNCTION 2 gbt_ts_union (bytea, internal),
FUNCTION 3 gbt_tstz_compress (internal),
***************
*** 581,586 **** AS
--- 589,595 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_time_consistent (internal, time, int2, oid, internal),
FUNCTION 2 gbt_time_union (bytea, internal),
FUNCTION 3 gbt_time_compress (internal),
***************
*** 598,603 **** AS
--- 607,613 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_timetz_consistent (internal, timetz, int2, oid, internal),
FUNCTION 2 gbt_time_union (bytea, internal),
FUNCTION 3 gbt_timetz_compress (internal),
***************
*** 655,660 **** AS
--- 665,671 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_date_consistent (internal, date, int2, oid, internal),
FUNCTION 2 gbt_date_union (bytea, internal),
FUNCTION 3 gbt_date_compress (internal),
***************
*** 717,722 **** AS
--- 728,734 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_intv_consistent (internal, interval, int2, oid, internal),
FUNCTION 2 gbt_intv_union (bytea, internal),
FUNCTION 3 gbt_intv_compress (internal),
***************
*** 773,778 **** AS
--- 785,791 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_cash_consistent (internal, money, int2, oid, internal),
FUNCTION 2 gbt_cash_union (bytea, internal),
FUNCTION 3 gbt_cash_compress (internal),
***************
*** 829,834 **** AS
--- 842,848 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_macad_consistent (internal, macaddr, int2, oid, internal),
FUNCTION 2 gbt_macad_union (bytea, internal),
FUNCTION 3 gbt_macad_compress (internal),
***************
*** 897,902 **** AS
--- 911,917 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_text_consistent (internal, text, int2, oid, internal),
FUNCTION 2 gbt_text_union (bytea, internal),
FUNCTION 3 gbt_text_compress (internal),
***************
*** 916,921 **** AS
--- 931,937 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bpchar_consistent (internal, bpchar , int2, oid, internal),
FUNCTION 2 gbt_text_union (bytea, internal),
FUNCTION 3 gbt_bpchar_compress (internal),
***************
*** 973,978 **** AS
--- 989,995 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bytea_consistent (internal, bytea, int2, oid, internal),
FUNCTION 2 gbt_bytea_union (bytea, internal),
FUNCTION 3 gbt_bytea_compress (internal),
***************
*** 1030,1035 **** AS
--- 1047,1053 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_numeric_consistent (internal, numeric, int2, oid, internal),
FUNCTION 2 gbt_numeric_union (bytea, internal),
FUNCTION 3 gbt_numeric_compress (internal),
***************
*** 1085,1090 **** AS
--- 1103,1109 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bit_consistent (internal, bit, int2, oid, internal),
FUNCTION 2 gbt_bit_union (bytea, internal),
FUNCTION 3 gbt_bit_compress (internal),
***************
*** 1104,1109 **** AS
--- 1123,1129 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_bit_consistent (internal, bit, int2, oid, internal),
FUNCTION 2 gbt_bit_union (bytea, internal),
FUNCTION 3 gbt_bit_compress (internal),
***************
*** 1162,1167 **** AS
--- 1182,1188 ----
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
+ OPERATOR 6 <> ,
FUNCTION 1 gbt_inet_consistent (internal, inet, int2, oid, internal),
FUNCTION 2 gbt_inet_union (bytea, internal),
FUNCTION 3 gbt_inet_compress (internal),
***************
*** 1180,1185 **** AS
--- 1201,1207 ----
OPERATOR 3 = (inet, inet) ,
OPERATOR 4 >= (inet, inet) ,
OPERATOR 5 > (inet, inet) ,
+ OPERATOR 6 <> (inet, inet) ,
FUNCTION 1 gbt_inet_consistent (internal, inet, int2, oid, internal),
FUNCTION 2 gbt_inet_union (bytea, internal),
FUNCTION 3 gbt_inet_compress (internal),
*** a/contrib/btree_gist/btree_utils_num.c
--- b/contrib/btree_gist/btree_utils_num.c
***************
*** 225,230 **** gbt_num_consistent(
--- 225,233 ----
case BTGreaterEqualStrategyNumber:
retval = (*tinfo->f_le) (query, key->upper);
break;
+ case BTNotEqualStrategyNumber:
+ retval = ! ((*tinfo->f_eq) (query, key->lower) && (*tinfo->f_eq) (query, key->upper));
+ break;
default:
retval = FALSE;
}
*** a/contrib/btree_gist/btree_utils_var.c
--- b/contrib/btree_gist/btree_utils_var.c
***************
*** 596,601 **** gbt_var_consistent(
--- 596,604 ----
retval = (*tinfo->f_cmp) ((bytea *) query, key->upper) <= 0
|| gbt_var_node_pf_match(key, query, tinfo);
break;
+ case BTNotEqualStrategyNumber:
+ retval = ! ((*tinfo->f_eq) (query, key->lower) && (*tinfo->f_eq) (query, key->upper));
+ break;
default:
retval = FALSE;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
