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:

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

• 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.