btree_gist
btree_gist
btree_gist : support for indexing common datatypes in GiST
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 4940 | btree_gist
|
btree_gist
|
1.7 |
FUNC
|
PostgreSQL
|
C
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-dt-
|
No
|
Yes
|
No
|
Yes
|
no
|
yes
|
| Relationships | |
|---|---|
| Need By | emaj
omni_auth
periods
pgautofailover
powa
|
| See Also | btree_gin
unaccent
fuzzystrmatch
pg_trgm
prefix
citext
|
Packages
| PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|
1.7
|
1.7
|
1.7
|
1.7
|
1.7
|
This is a built-in contrib extension ship with the PostgreSQL kernel
Install
Create this extension with:
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 &&)
);Last updated on