ora_btree_gist

ivorysql : Support for oracle indexing common datatypes in GiST

Overview

Packages

Source

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install ivorysql;		# install via package name, for the active PG version
pig install ora_btree_gist;		# install by extension name, for the current active PG version

pig install ora_btree_gist -v 18;   # install for PG 18

Create this extension with:

CREATE EXTENSION ora_btree_gist CASCADE; -- requires ivorysql_ora

Usage

ora_btree_gist: Support for oracle indexing common datatypes in GiST

The ora_btree_gist extension adds GiST (Generalized Search Tree) operator class support for Oracle-compatible data types provided by IvorySQL.

Enabling

CREATE EXTENSION ora_btree_gist;

Creating GiST Indexes on Oracle Types

This extension allows you to create GiST indexes on Oracle-compatible data types such as NUMBER, VARCHAR2, and Oracle-style DATE, similar to how the standard btree_gist extension works for native PostgreSQL types.

CREATE TABLE t (val NUMBER);
CREATE INDEX t_val_gist_idx ON t USING gist (val);

Use Cases

GiST indexes with Oracle-compatible types enable:

• Exclusion constraints using Oracle data types
• Nearest-neighbor searches on Oracle-type columns
• Range queries with GiST optimizations for Oracle-compatible types

This extension is part of the IvorySQL Oracle compatibility suite and requires ivorysql_ora to be available.