parray_gin

Overview

PGXN dist name and PostgreSQL extension name are both parray_gin; Pigsty packages are built for PG 14-18.

Version

Build

You can build the RPM / DEB packages for parray_gin using pig build:

pig build pkg parray_gin         # build RPM / DEB packages

Install

You can install parray_gin directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install parray_gin;          # Install for current active PG version

pig ext install -y parray_gin -v 18  # PG 18
pig ext install -y parray_gin -v 17  # PG 17
pig ext install -y parray_gin -v 16  # PG 16
pig ext install -y parray_gin -v 15  # PG 15
pig ext install -y parray_gin -v 14  # PG 14

dnf install -y parray_gin_18       # PG 18
dnf install -y parray_gin_17       # PG 17
dnf install -y parray_gin_16       # PG 16
dnf install -y parray_gin_15       # PG 15
dnf install -y parray_gin_14       # PG 14

apt install -y postgresql-18-parray-gin   # PG 18
apt install -y postgresql-17-parray-gin   # PG 17
apt install -y postgresql-16-parray-gin   # PG 16
apt install -y postgresql-15-parray-gin   # PG 15
apt install -y postgresql-14-parray-gin   # PG 14

Create Extension:

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.