🌐 AI搜索 & 代理 主页
Skip to content

Commit ad17ff9

Browse files
robertmhaasrobertmhaas
committed
Add btree_gist support for searching on "not equals".
Jeff Davis, with slight editorialization by me.
1 parent 98c2383 commit ad17ff9

File tree

5 files changed

+72
-5
lines changed

5 files changed

+72
-5
lines changed

‎contrib/btree_gist/btree_gist.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $PostgreSQL: pgsql/contrib/btree_gist/btree_gist.h,v 1.9 2009/06/11 14:48:50 momjian Exp $
2+
* $PostgreSQL: pgsql/contrib/btree_gist/btree_gist.h,v 1.10 2010/08/02 16:26:48 rhaas Exp $
33
*/
44
#ifndef __BTREE_GIST_H__
55
#define __BTREE_GIST_H__
@@ -9,6 +9,8 @@
99
#include "access/itup.h"
1010
#include "access/nbtree.h"
1111

12+
#define BtreeGistNotEqualStrategyNumber 6
13+
1214
/* indexed types */
1315

1416
enum gbtree_type

‎contrib/btree_gist/btree_gist.sql.in‎

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/contrib/btree_gist/btree_gist.sql.in,v 1.21 2009/06/11 18:30:03 tgl Exp $ */
1+
/* $PostgreSQL: pgsql/contrib/btree_gist/btree_gist.sql.in,v 1.22 2010/08/02 16:26:48 rhaas Exp $ */
22

33
-- Adjust this setting to control where the objects get created.
44
SET search_path = public;
@@ -143,6 +143,7 @@ AS
143143
OPERATOR 3 = ,
144144
OPERATOR 4 >= ,
145145
OPERATOR 5 > ,
146+
OPERATOR 6 <> ,
146147
FUNCTION 1 gbt_oid_consistent (internal, oid, int2, oid, internal),
147148
FUNCTION 2 gbt_oid_union (bytea, internal),
148149
FUNCTION 3 gbt_oid_compress (internal),
@@ -200,6 +201,7 @@ AS
200201
OPERATOR 3 = ,
201202
OPERATOR 4 >= ,
202203
OPERATOR 5 > ,
204+
OPERATOR 6 <> ,
203205
FUNCTION 1 gbt_int2_consistent (internal, int2, int2, oid, internal),
204206
FUNCTION 2 gbt_int2_union (bytea, internal),
205207
FUNCTION 3 gbt_int2_compress (internal),
@@ -256,6 +258,7 @@ AS
256258
OPERATOR 3 = ,
257259
OPERATOR 4 >= ,
258260
OPERATOR 5 > ,
261+
OPERATOR 6 <> ,
259262
FUNCTION 1 gbt_int4_consistent (internal, int4, int2, oid, internal),
260263
FUNCTION 2 gbt_int4_union (bytea, internal),
261264
FUNCTION 3 gbt_int4_compress (internal),
@@ -312,6 +315,7 @@ AS
312315
OPERATOR 3 = ,
313316
OPERATOR 4 >= ,
314317
OPERATOR 5 > ,
318+
OPERATOR 6 <> ,
315319
FUNCTION 1 gbt_int8_consistent (internal, int8, int2, oid, internal),
316320
FUNCTION 2 gbt_int8_union (bytea, internal),
317321
FUNCTION 3 gbt_int8_compress (internal),
@@ -369,6 +373,7 @@ AS
369373
OPERATOR 3 = ,
370374
OPERATOR 4 >= ,
371375
OPERATOR 5 > ,
376+
OPERATOR 6 <> ,
372377
FUNCTION 1 gbt_float4_consistent (internal, float4, int2, oid, internal),
373378
FUNCTION 2 gbt_float4_union (bytea, internal),
374379
FUNCTION 3 gbt_float4_compress (internal),
@@ -428,6 +433,7 @@ AS
428433
OPERATOR 3 = ,
429434
OPERATOR 4 >= ,
430435
OPERATOR 5 > ,
436+
OPERATOR 6 <> ,
431437
FUNCTION 1 gbt_float8_consistent (internal, float8, int2, oid, internal),
432438
FUNCTION 2 gbt_float8_union (bytea, internal),
433439
FUNCTION 3 gbt_float8_compress (internal),
@@ -495,6 +501,7 @@ AS
495501
OPERATOR 3 = ,
496502
OPERATOR 4 >= ,
497503
OPERATOR 5 > ,
504+
OPERATOR 6 <> ,
498505
FUNCTION 1 gbt_ts_consistent (internal, timestamp, int2, oid, internal),
499506
FUNCTION 2 gbt_ts_union (bytea, internal),
500507
FUNCTION 3 gbt_ts_compress (internal),
@@ -514,6 +521,7 @@ AS
514521
OPERATOR 3 = ,
515522
OPERATOR 4 >= ,
516523
OPERATOR 5 > ,
524+
OPERATOR 6 <> ,
517525
FUNCTION 1 gbt_tstz_consistent (internal, timestamptz, int2, oid, internal),
518526
FUNCTION 2 gbt_ts_union (bytea, internal),
519527
FUNCTION 3 gbt_tstz_compress (internal),
@@ -581,6 +589,7 @@ AS
581589
OPERATOR 3 = ,
582590
OPERATOR 4 >= ,
583591
OPERATOR 5 > ,
592+
OPERATOR 6 <> ,
584593
FUNCTION 1 gbt_time_consistent (internal, time, int2, oid, internal),
585594
FUNCTION 2 gbt_time_union (bytea, internal),
586595
FUNCTION 3 gbt_time_compress (internal),
@@ -598,6 +607,7 @@ AS
598607
OPERATOR 3 = ,
599608
OPERATOR 4 >= ,
600609
OPERATOR 5 > ,
610+
OPERATOR 6 <> ,
601611
FUNCTION 1 gbt_timetz_consistent (internal, timetz, int2, oid, internal),
602612
FUNCTION 2 gbt_time_union (bytea, internal),
603613
FUNCTION 3 gbt_timetz_compress (internal),
@@ -655,6 +665,7 @@ AS
655665
OPERATOR 3 = ,
656666
OPERATOR 4 >= ,
657667
OPERATOR 5 > ,
668+
OPERATOR 6 <> ,
658669
FUNCTION 1 gbt_date_consistent (internal, date, int2, oid, internal),
659670
FUNCTION 2 gbt_date_union (bytea, internal),
660671
FUNCTION 3 gbt_date_compress (internal),
@@ -717,6 +728,7 @@ AS
717728
OPERATOR 3 = ,
718729
OPERATOR 4 >= ,
719730
OPERATOR 5 > ,
731+
OPERATOR 6 <> ,
720732
FUNCTION 1 gbt_intv_consistent (internal, interval, int2, oid, internal),
721733
FUNCTION 2 gbt_intv_union (bytea, internal),
722734
FUNCTION 3 gbt_intv_compress (internal),
@@ -773,6 +785,7 @@ AS
773785
OPERATOR 3 = ,
774786
OPERATOR 4 >= ,
775787
OPERATOR 5 > ,
788+
OPERATOR 6 <> ,
776789
FUNCTION 1 gbt_cash_consistent (internal, money, int2, oid, internal),
777790
FUNCTION 2 gbt_cash_union (bytea, internal),
778791
FUNCTION 3 gbt_cash_compress (internal),
@@ -829,6 +842,7 @@ AS
829842
OPERATOR 3 = ,
830843
OPERATOR 4 >= ,
831844
OPERATOR 5 > ,
845+
OPERATOR 6 <> ,
832846
FUNCTION 1 gbt_macad_consistent (internal, macaddr, int2, oid, internal),
833847
FUNCTION 2 gbt_macad_union (bytea, internal),
834848
FUNCTION 3 gbt_macad_compress (internal),
@@ -897,6 +911,7 @@ AS
897911
OPERATOR 3 = ,
898912
OPERATOR 4 >= ,
899913
OPERATOR 5 > ,
914+
OPERATOR 6 <> ,
900915
FUNCTION 1 gbt_text_consistent (internal, text, int2, oid, internal),
901916
FUNCTION 2 gbt_text_union (bytea, internal),
902917
FUNCTION 3 gbt_text_compress (internal),
@@ -916,6 +931,7 @@ AS
916931
OPERATOR 3 = ,
917932
OPERATOR 4 >= ,
918933
OPERATOR 5 > ,
934+
OPERATOR 6 <> ,
919935
FUNCTION 1 gbt_bpchar_consistent (internal, bpchar , int2, oid, internal),
920936
FUNCTION 2 gbt_text_union (bytea, internal),
921937
FUNCTION 3 gbt_bpchar_compress (internal),
@@ -973,6 +989,7 @@ AS
973989
OPERATOR 3 = ,
974990
OPERATOR 4 >= ,
975991
OPERATOR 5 > ,
992+
OPERATOR 6 <> ,
976993
FUNCTION 1 gbt_bytea_consistent (internal, bytea, int2, oid, internal),
977994
FUNCTION 2 gbt_bytea_union (bytea, internal),
978995
FUNCTION 3 gbt_bytea_compress (internal),
@@ -1030,6 +1047,7 @@ AS
10301047
OPERATOR 3 = ,
10311048
OPERATOR 4 >= ,
10321049
OPERATOR 5 > ,
1050+
OPERATOR 6 <> ,
10331051
FUNCTION 1 gbt_numeric_consistent (internal, numeric, int2, oid, internal),
10341052
FUNCTION 2 gbt_numeric_union (bytea, internal),
10351053
FUNCTION 3 gbt_numeric_compress (internal),
@@ -1085,6 +1103,7 @@ AS
10851103
OPERATOR 3 = ,
10861104
OPERATOR 4 >= ,
10871105
OPERATOR 5 > ,
1106+
OPERATOR 6 <> ,
10881107
FUNCTION 1 gbt_bit_consistent (internal, bit, int2, oid, internal),
10891108
FUNCTION 2 gbt_bit_union (bytea, internal),
10901109
FUNCTION 3 gbt_bit_compress (internal),
@@ -1104,6 +1123,7 @@ AS
11041123
OPERATOR 3 = ,
11051124
OPERATOR 4 >= ,
11061125
OPERATOR 5 > ,
1126+
OPERATOR 6 <> ,
11071127
FUNCTION 1 gbt_bit_consistent (internal, bit, int2, oid, internal),
11081128
FUNCTION 2 gbt_bit_union (bytea, internal),
11091129
FUNCTION 3 gbt_bit_compress (internal),
@@ -1162,6 +1182,7 @@ AS
11621182
OPERATOR 3 = ,
11631183
OPERATOR 4 >= ,
11641184
OPERATOR 5 > ,
1185+
OPERATOR 6 <> ,
11651186
FUNCTION 1 gbt_inet_consistent (internal, inet, int2, oid, internal),
11661187
FUNCTION 2 gbt_inet_union (bytea, internal),
11671188
FUNCTION 3 gbt_inet_compress (internal),
@@ -1180,6 +1201,7 @@ AS
11801201
OPERATOR 3 = (inet, inet) ,
11811202
OPERATOR 4 >= (inet, inet) ,
11821203
OPERATOR 5 > (inet, inet) ,
1204+
OPERATOR 6 <> (inet, inet) ,
11831205
FUNCTION 1 gbt_inet_consistent (internal, inet, int2, oid, internal),
11841206
FUNCTION 2 gbt_inet_union (bytea, internal),
11851207
FUNCTION 3 gbt_inet_compress (internal),

‎contrib/btree_gist/btree_utils_num.c‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $PostgreSQL: pgsql/contrib/btree_gist/btree_utils_num.c,v 1.12 2009/06/11 14:48:50 momjian Exp $
2+
* $PostgreSQL: pgsql/contrib/btree_gist/btree_utils_num.c,v 1.13 2010/08/02 16:26:48 rhaas Exp $
33
*/
44
#include "btree_gist.h"
55
#include "btree_utils_num.h"
@@ -225,6 +225,10 @@ gbt_num_consistent(
225225
case BTGreaterEqualStrategyNumber:
226226
retval = (*tinfo->f_le) (query, key->upper);
227227
break;
228+
case BtreeGistNotEqualStrategyNumber:
229+
retval = ! ((*tinfo->f_eq) (query, key->lower) &&
230+
(*tinfo->f_eq) (query, key->upper));
231+
break;
228232
default:
229233
retval = FALSE;
230234
}

‎contrib/btree_gist/btree_utils_var.c‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $PostgreSQL: pgsql/contrib/btree_gist/btree_utils_var.c,v 1.23 2010/02/26 02:00:32 momjian Exp $
2+
* $PostgreSQL: pgsql/contrib/btree_gist/btree_utils_var.c,v 1.24 2010/08/02 16:26:48 rhaas Exp $
33
*/
44
#include "btree_gist.h"
55

@@ -596,6 +596,9 @@ gbt_var_consistent(
596596
retval = (*tinfo->f_cmp) ((bytea *) query, key->upper) <= 0
597597
|| gbt_var_node_pf_match(key, query, tinfo);
598598
break;
599+
case BtreeGistNotEqualStrategyNumber:
600+
retval = ! ((*tinfo->f_eq) (query, key->lower) && (*tinfo->f_eq) (query, key->upper));
601+
break;
599602
default:
600603
retval = FALSE;
601604
}

‎doc/src/sgml/btree-gist.sgml‎

Lines changed: 37 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/btree-gist.sgml,v 1.5 2010/03/17 17:12:31 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/btree-gist.sgml,v 1.6 2010/08/02 16:26:48 rhaas Exp $ -->
22

33
<sect1 id="btree-gist">
44
<title>btree_gist</title>
@@ -27,15 +27,51 @@
2727
GiST operator classes.
2828
</para>
2929

30+
<para>
31+
In addition to the typical btree search operators, btree_gist also
32+
provides search operators for <literal>&lt;&gt;</literal> ("not
33+
equals"). This may be useful in combination with an
34+
<link linkend="SQL-CREATETABLE-EXCLUDE">Exclusion Constraint</link>,
35+
as descibed below.
36+
</para>
37+
3038
<sect2>
3139
<title>Example usage</title>
3240

41+
<para>
42+
Simple example using btree_gist instead of btree:
43+
</para>
44+
3345
<programlisting>
3446
CREATE TABLE test (a int4);
3547
-- create index
3648
CREATE INDEX testidx ON test USING gist (a);
3749
-- query
3850
SELECT * FROM test WHERE a &lt; 10;
51+
</programlisting>
52+
53+
<para>
54+
Example using an <link linkend="SQL-CREATETABLE-EXCLUDE">Exclusion
55+
Constraint</link> to enforce the constraint that a cage at a zoo
56+
can contain only one kind of animal:
57+
</para>
58+
59+
<programlisting>
60+
=> CREATE TABLE zoo (
61+
cage INTEGER,
62+
animal TEXT,
63+
EXCLUDE USING gist (cage WITH =, animal WITH <>)
64+
);
65+
66+
=> INSERT INTO zoo VALUES(123, 'zebra');
67+
INSERT 0 1
68+
=> INSERT INTO zoo VALUES(123, 'zebra');
69+
INSERT 0 1
70+
=> INSERT INTO zoo VALUES(123, 'lion');
71+
ERROR: conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
72+
DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
73+
=> INSERT INTO zoo VALUES(124, 'lion');
74+
INSERT 0 1
3975
</programlisting>
4076

4177
</sect2>

0 commit comments

Comments
 (0)