btree_gist
support for indexing common datatypes in GiST
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
btree_gist | 1.7 | FUNC | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 4940 | btree_gist | No | Yes | No | Yes | Yes | No | - |
| Related | btree_gin unaccent fuzzystrmatch pg_trgm prefix citext |
|---|---|
| Depended By | emaj omni_auth periods pgautofailover powa |
Version
| PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|
| 1.7 | 1.7 | 1.7 | 1.7 | 1.7 |
Install
Note: This is a built-in contrib extension of PostgreSQL
CREATE EXTENSION btree_gist;
Usage
Provides GiST index operator classes for data types that normally only support B-tree indexing. Enables exclusion constraints combining equality with range operators.
CREATE EXTENSION btree_gist;
Supported 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, bool, and all enum types.
Distance Operator
The <-> operator is provided for nearest-neighbor searches on numeric and temporal types.
Examples
-- GiST index on integer column
CREATE INDEX idx ON test USING GIST (a);
SELECT * FROM test WHERE a < 10;
-- Nearest-neighbor search
SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;
-- Exclusion constraint: each cage can only contain one type of animal
CREATE TABLE zoo (
cage integer,
animal text,
EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);
INSERT INTO zoo VALUES (1, 'lion'); -- OK
INSERT INTO zoo VALUES (1, 'tiger'); -- ERROR: conflicting key value
INSERT INTO zoo VALUES (2, 'tiger'); -- OK
-- Exclusion constraint for non-overlapping time ranges per room
CREATE TABLE reservations (
room int,
during tsrange,
EXCLUDE USING GIST (room WITH =, during WITH &&)
);
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.