btree_gist

support for indexing common datatypes in 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 pg_lake_table pgautofailover powa
See Also	ora_btree_gist btree_gin parray_gin intarray rum bloom db2fce orafce

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

btree_gist: B-tree equivalent GiST operator classes

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 &&)
);