btree_gist

support for indexing common datatypes in GiST

https://www.postgresql.org/docs/current/btree-gist.html

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	`ora_btree_gist` `btree_gin` `parray_gin` `intarray` `rum` `bloom` `db2fce` `orafce`
Depended By	`emaj` `omni_auth` `periods` `pg_lake_table` `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

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