parray_gin

parray_gin : GIN index operator class and partial-match operators for text arrays

Overview

Packages

Source

pig build pkg parray_gin;		# build rpm/deb

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 parray_gin;		# install via package name, for the active PG version

pig install parray_gin -v 18;   # install for PG 18
pig install parray_gin -v 17;   # install for PG 17
pig install parray_gin -v 16;   # install for PG 16
pig install parray_gin -v 15;   # install for PG 15
pig install parray_gin -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION parray_gin;

Usage

• Sources: README, reference doc

parray_gin adds a GIN operator class for text[] plus strict and partial-match operators. Upstream describes it as trigram-based array indexing built on pg_trgm trigram decomposition.

Create The Extension And Index

CREATE EXTENSION parray_gin;

CREATE TABLE test_table (
  id  bigserial,
  val text[]
);

CREATE INDEX test_tags_idx
ON test_table
USING gin (val parray_gin_ops);

Indexed Operators

The reference doc says parray_gin_ops can support these operators:

• @>: strict contains.
• <@: strict contained-by.
• @@>: partial contains, where array elements may use LIKE patterns.
• <@@: partial contained-by.

Examples:

SELECT * FROM test_table WHERE val @> ARRAY['must','contain'];
SELECT * FROM test_table WHERE val @@> ARRAY['what%like%'];
SELECT * FROM test_table WHERE val <@ ARRAY['galaxy','ago','vader'];
SELECT * FROM test_table WHERE val <@@ ARRAY['%ar%','vader'];

Matching Behavior

Strict matching requires array-item equality. Partial matching allows patterns such as 'foo%' or '%oo%'. Because the trigram index can return false positives, the docs note that matches are rechecked after index lookup.

Caveats

The README says support extends through PostgreSQL 18, while the reference document still says 9.1-14. The operator and opclass behavior is consistent across both docs, but the version note is not fully synchronized upstream.