Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitad17ff9

Browse files
committed
Add btree_gist support for searching on "not equals".
Jeff Davis, with slight editorialization by me.
1 parent98c2383 commitad17ff9

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+
#defineBtreeGistNotEqualStrategyNumber 6
13+
1214
/* indexed types */
1315

1416
enumgbtree_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
OPERATOR3= ,
144144
OPERATOR4>= ,
145145
OPERATOR5> ,
146+
OPERATOR6<> ,
146147
FUNCTION1gbt_oid_consistent (internal, oid, int2, oid, internal),
147148
FUNCTION2gbt_oid_union (bytea, internal),
148149
FUNCTION3gbt_oid_compress (internal),
@@ -200,6 +201,7 @@ AS
200201
OPERATOR3= ,
201202
OPERATOR4>= ,
202203
OPERATOR5> ,
204+
OPERATOR6<> ,
203205
FUNCTION1gbt_int2_consistent (internal, int2, int2, oid, internal),
204206
FUNCTION2gbt_int2_union (bytea, internal),
205207
FUNCTION3gbt_int2_compress (internal),
@@ -256,6 +258,7 @@ AS
256258
OPERATOR3= ,
257259
OPERATOR4>= ,
258260
OPERATOR5> ,
261+
OPERATOR6<> ,
259262
FUNCTION1gbt_int4_consistent (internal, int4, int2, oid, internal),
260263
FUNCTION2gbt_int4_union (bytea, internal),
261264
FUNCTION3gbt_int4_compress (internal),
@@ -312,6 +315,7 @@ AS
312315
OPERATOR3= ,
313316
OPERATOR4>= ,
314317
OPERATOR5> ,
318+
OPERATOR6<> ,
315319
FUNCTION1gbt_int8_consistent (internal, int8, int2, oid, internal),
316320
FUNCTION2gbt_int8_union (bytea, internal),
317321
FUNCTION3gbt_int8_compress (internal),
@@ -369,6 +373,7 @@ AS
369373
OPERATOR3= ,
370374
OPERATOR4>= ,
371375
OPERATOR5> ,
376+
OPERATOR6<> ,
372377
FUNCTION1gbt_float4_consistent (internal, float4, int2, oid, internal),
373378
FUNCTION2gbt_float4_union (bytea, internal),
374379
FUNCTION3gbt_float4_compress (internal),
@@ -428,6 +433,7 @@ AS
428433
OPERATOR3= ,
429434
OPERATOR4>= ,
430435
OPERATOR5> ,
436+
OPERATOR6<> ,
431437
FUNCTION1gbt_float8_consistent (internal, float8, int2, oid, internal),
432438
FUNCTION2gbt_float8_union (bytea, internal),
433439
FUNCTION3gbt_float8_compress (internal),
@@ -495,6 +501,7 @@ AS
495501
OPERATOR3= ,
496502
OPERATOR4>= ,
497503
OPERATOR5> ,
504+
OPERATOR6<> ,
498505
FUNCTION1gbt_ts_consistent (internal, timestamp, int2, oid, internal),
499506
FUNCTION2gbt_ts_union (bytea, internal),
500507
FUNCTION3gbt_ts_compress (internal),
@@ -514,6 +521,7 @@ AS
514521
OPERATOR3= ,
515522
OPERATOR4>= ,
516523
OPERATOR5> ,
524+
OPERATOR6<> ,
517525
FUNCTION1gbt_tstz_consistent (internal, timestamptz, int2, oid, internal),
518526
FUNCTION2gbt_ts_union (bytea, internal),
519527
FUNCTION3gbt_tstz_compress (internal),
@@ -581,6 +589,7 @@ AS
581589
OPERATOR3= ,
582590
OPERATOR4>= ,
583591
OPERATOR5> ,
592+
OPERATOR6<> ,
584593
FUNCTION1gbt_time_consistent (internal, time, int2, oid, internal),
585594
FUNCTION2gbt_time_union (bytea, internal),
586595
FUNCTION3gbt_time_compress (internal),
@@ -598,6 +607,7 @@ AS
598607
OPERATOR3= ,
599608
OPERATOR4>= ,
600609
OPERATOR5> ,
610+
OPERATOR6<> ,
601611
FUNCTION1gbt_timetz_consistent (internal, timetz, int2, oid, internal),
602612
FUNCTION2gbt_time_union (bytea, internal),
603613
FUNCTION3gbt_timetz_compress (internal),
@@ -655,6 +665,7 @@ AS
655665
OPERATOR3= ,
656666
OPERATOR4>= ,
657667
OPERATOR5> ,
668+
OPERATOR6<> ,
658669
FUNCTION1gbt_date_consistent (internal, date, int2, oid, internal),
659670
FUNCTION2gbt_date_union (bytea, internal),
660671
FUNCTION3gbt_date_compress (internal),
@@ -717,6 +728,7 @@ AS
717728
OPERATOR3= ,
718729
OPERATOR4>= ,
719730
OPERATOR5> ,
731+
OPERATOR6<> ,
720732
FUNCTION1gbt_intv_consistent (internal, interval, int2, oid, internal),
721733
FUNCTION2gbt_intv_union (bytea, internal),
722734
FUNCTION3gbt_intv_compress (internal),
@@ -773,6 +785,7 @@ AS
773785
OPERATOR3= ,
774786
OPERATOR4>= ,
775787
OPERATOR5> ,
788+
OPERATOR6<> ,
776789
FUNCTION1gbt_cash_consistent (internal, money, int2, oid, internal),
777790
FUNCTION2gbt_cash_union (bytea, internal),
778791
FUNCTION3gbt_cash_compress (internal),
@@ -829,6 +842,7 @@ AS
829842
OPERATOR3= ,
830843
OPERATOR4>= ,
831844
OPERATOR5> ,
845+
OPERATOR6<> ,
832846
FUNCTION1gbt_macad_consistent (internal, macaddr, int2, oid, internal),
833847
FUNCTION2gbt_macad_union (bytea, internal),
834848
FUNCTION3gbt_macad_compress (internal),
@@ -897,6 +911,7 @@ AS
897911
OPERATOR3= ,
898912
OPERATOR4>= ,
899913
OPERATOR5> ,
914+
OPERATOR6<> ,
900915
FUNCTION1gbt_text_consistent (internal, text, int2, oid, internal),
901916
FUNCTION2gbt_text_union (bytea, internal),
902917
FUNCTION3gbt_text_compress (internal),
@@ -916,6 +931,7 @@ AS
916931
OPERATOR3= ,
917932
OPERATOR4>= ,
918933
OPERATOR5> ,
934+
OPERATOR6<> ,
919935
FUNCTION1gbt_bpchar_consistent (internal, bpchar , int2, oid, internal),
920936
FUNCTION2gbt_text_union (bytea, internal),
921937
FUNCTION3gbt_bpchar_compress (internal),
@@ -973,6 +989,7 @@ AS
973989
OPERATOR3= ,
974990
OPERATOR4>= ,
975991
OPERATOR5> ,
992+
OPERATOR6<> ,
976993
FUNCTION1gbt_bytea_consistent (internal, bytea, int2, oid, internal),
977994
FUNCTION2gbt_bytea_union (bytea, internal),
978995
FUNCTION3gbt_bytea_compress (internal),
@@ -1030,6 +1047,7 @@ AS
10301047
OPERATOR3= ,
10311048
OPERATOR4>= ,
10321049
OPERATOR5> ,
1050+
OPERATOR6<> ,
10331051
FUNCTION1gbt_numeric_consistent (internal, numeric, int2, oid, internal),
10341052
FUNCTION2gbt_numeric_union (bytea, internal),
10351053
FUNCTION3gbt_numeric_compress (internal),
@@ -1085,6 +1103,7 @@ AS
10851103
OPERATOR3= ,
10861104
OPERATOR4>= ,
10871105
OPERATOR5> ,
1106+
OPERATOR6<> ,
10881107
FUNCTION1gbt_bit_consistent (internal, bit, int2, oid, internal),
10891108
FUNCTION2gbt_bit_union (bytea, internal),
10901109
FUNCTION3gbt_bit_compress (internal),
@@ -1104,6 +1123,7 @@ AS
11041123
OPERATOR3= ,
11051124
OPERATOR4>= ,
11061125
OPERATOR5> ,
1126+
OPERATOR6<> ,
11071127
FUNCTION1gbt_bit_consistent (internal, bit, int2, oid, internal),
11081128
FUNCTION2gbt_bit_union (bytea, internal),
11091129
FUNCTION3gbt_bit_compress (internal),
@@ -1162,6 +1182,7 @@ AS
11621182
OPERATOR3= ,
11631183
OPERATOR4>= ,
11641184
OPERATOR5> ,
1185+
OPERATOR6<> ,
11651186
FUNCTION1gbt_inet_consistent (internal, inet, int2, oid, internal),
11661187
FUNCTION2gbt_inet_union (bytea, internal),
11671188
FUNCTION3gbt_inet_compress (internal),
@@ -1180,6 +1201,7 @@ AS
11801201
OPERATOR3= (inet, inet) ,
11811202
OPERATOR4>= (inet, inet) ,
11821203
OPERATOR5> (inet, inet) ,
1204+
OPERATOR6<> (inet, inet) ,
11831205
FUNCTION1gbt_inet_consistent (internal, inet, int2, oid, internal),
11841206
FUNCTION2gbt_inet_union (bytea, internal),
11851207
FUNCTION3gbt_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
caseBTGreaterEqualStrategyNumber:
226226
retval= (*tinfo->f_le) (query,key->upper);
227227
break;
228+
caseBtreeGistNotEqualStrategyNumber:
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+
caseBtreeGistNotEqualStrategyNumber:
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)
[8]ページ先頭
©2009-2025 Movatter.jp