Text search and indexingversion 1.7

btree_gist

GiST indexes over plain columns, for exclusion constraints.

What it is for

btree_gist lets GiST indexes include scalar columns. Its main use is exclusion constraints that combine equality with range overlap, such as "the same room cannot be booked twice for overlapping times", enforced by the database itself.

Enable it

sql

create extension if not exists btree_gist with schema extensions;

Example

sql

create table bookings (
  room_id int not null,
  during tstzrange not null,
  exclude using gist (room_id with =, during with &&)
);

-- the second insert fails with SQLSTATE 23P01 (exclusion_violation)
insert into bookings values (1, '[2026-10-08 10:00, 2026-10-08 11:00)');
insert into bookings values (1, '[2026-10-08 10:30, 2026-10-08 12:00)');

Questions

How do I enable btree_gist?

Run create extension if not exists btree_gist with schema extensions; in the SQL editor, or switch it on from the Extensions page in the console. You do not need superuser access.

Which version of btree_gist is installed?

1.7, on Postgres 17.6, as read from the running engine on 2026-10-08. Check yours with: select extversion from pg_extension where extname = 'btree_gist';

Upstream project: www.postgresql.org/docs/17/btree-gist.html

More text search and indexing extensions

pg_trgm Fuzzy text search and fast LIKE and ILIKE with trigram indexes.
unaccent Remove accents from text for search.
fuzzystrmatch Soundex, Metaphone and Levenshtein distance.
btree_gin GIN indexes over plain columns, for mixed indexes.

Try btree_gist on Scribase All extensions