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

Syntax:

CREATE EXTENSION parray_gin;
CREATE INDEX test_tags_idx ON test_table USING gin (val parray_gin_ops);
SELECT * FROM test_table WHERE val @> ARRAY['must','contain'];
SELECT * FROM test_table WHERE val @@> ARRAY['what%like%'];

Sources: README, Reference

parray_gin adds GIN indexing and operators for text[] arrays with both strict and partial matching. The upstream docs describe it as trigram-based array indexing built on pg_trgm’s trigram implementation.

Indexing Arrays

The extension provides the parray_gin_ops operator class for GIN indexes on text arrays:

CREATE TABLE test_table(id bigserial, val text[]);
CREATE INDEX test_tags_idx ON test_table USING gin (val parray_gin_ops);

According to the reference docs, indexed values and queries are split into trigrams. Because the trigram index can return false positives, operator matches are rechecked after index lookup.

Operators

Strict Matching

@> (text[], text[]) -> bool

Returns true when the left-hand array contains all items from the right-hand array.

SELECT * FROM test_table WHERE val @> ARRAY['far'];

<@ (text[], text[]) -> bool

Returns true when the left-hand array is contained by the right-hand array.

SELECT * FROM test_table WHERE val <@ ARRAY['galaxy','ago','vader'];

Partial Matching

@@> (text[], text[]) -> bool

Returns true when the left-hand array contains all right-hand items using partial matching, for example 'foobar' ~~ 'foo%' or 'foobar' ~~ '%oo%'.

SELECT * FROM test_table WHERE val @@> ARRAY['%ar%'];

<@@ (text[], text[]) -> bool

Returns true when the left-hand array is contained by the right-hand patterns using partial matching.

SELECT * FROM test_table WHERE val <@@ ARRAY['%ar%','vader'];

Notes

The upstream docs say GIN can be used with @>, <@, @@>, and <@@. They also mention successful use on JSON arrays extracted from JSON text fields via the json_accessors extension.