btree_gist

mdx: format: md

btree_gist provides GiST index operator classes that implement B-tree-equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, and all enum types.

Typically, these operator classes are no better than the equivalent standard B-tree index method, and they lack one major feature of the standard B-tree code: the ability to enforce uniqueness. However, as described below, they provide some additional features not available in a B-tree index. Additionally, these operator classes are useful when a multicolumn GiST index is needed and some columns have data types that are only indexable with GiST while others are simple data types. Finally, these operators can be used for GiST testing and as a basis for developing other GiST operator classes.

Besides the typical B-tree search operators, btree_gist also provides index support for <> ("not equal"). This can be combined with exclusion constraints as described below.

Additionally, for data types that have a natural distance metric, btree_gist defines a distance operator <->, and provides GiST index support for nearest-neighbor searches using this operator. The distance operator is also provided for: int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time without time zone, date, interval, oid, and money.

This module is considered "trusted", meaning it can be installed by non-superusers who have CREATE privilege on the current database.

1. Usage Example

A simple example of using btree_gist instead of btree:

CREATE TABLE test (a int4);

-- create index

CREATE INDEX testidx ON test USING GIST (a);

-- query

SELECT * FROM test WHERE a < 10;

-- nearest-neighbor search: find the ten entries closest to "42"

SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;

Using an exclusion constraint to enforce the rule that a cage in a zoo can only hold one kind of animal:

=> CREATE TABLE zoo (

cage INTEGER,

animal TEXT,

EXCLUDE USING GIST (cage WITH =, animal WITH <>)

);

=> INSERT INTO zoo VALUES(123, 'zebra');

INSERT 0 1

=> INSERT INTO zoo VALUES(123, 'zebra');

INSERT 0 1

=> INSERT INTO zoo VALUES(123, 'lion');

ERROR: conflicting key value violates exclusion constraint

"zoo_cage_animal_excl"

DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage,

animal)=(123, zebra).

=> INSERT INTO zoo VALUES(124, 'lion');

INSERT 0 1