PostgreSQL: eseguire ricerche su colonne di tipo array

PostgreSQL permette di creare delle colonne come array di tipi di dati base. Praticamente è possibile inserire array di numeri integer o real,  testi, timestamp, boolean, ecc…
Ovviamente PostgreSQL mette a disposizione anche una serie di operatori ad hoc per la ricerca sugli array.

Oltre ai classici operatori di comparazione (uguale, maggiore, minore, …) ce ne sono 3 che possono essere molto utili: contains, is contained by, overlap.

Prima di iniziare a vedere come funzionano creiamo una tabella di test molto semplice (una colonna con id autoincrementale ed una colonna di tipo array di smallint) e inseriamo alcuni dati:

-- Creazione della tabella
CREATE TABLE test1
(
  id bigserial NOT NULL,
  parametro smallint[],
  CONSTRAINT test1_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

-- Impostazione proprietario tabella
ALTER TABLE test1 OWNER TO postgres;

-- Inserimento records per test
INSERT INTO test1(parametro) VALUES ('{1,2,3}');
INSERT INTO test1(parametro) VALUES ('{1,2,4,5,6}');
INSERT INTO test1(parametro) VALUES ('{6,8}');
INSERT INTO test1(parametro) VALUES ('{1,7,8,9}');

CONTAINS – array1 @> array2

L’operatore contains permette di trovare i record nei quali l’array2 è contenuto nell’array1, ovvero quando tutti i valori dell’array2 sono contenuti in array1.
Con la query:

SELECT * FROM test1 WHERE test1.parametro @> '{1,2}'

verranno trovate le righe con i seguenti array:

{1,2,3}
{1,2,4,5,6}

IS CONTAINED BY – array1 <@ array2

L’operatore is contained by permette di trovare i record nei quali l’array1 è contenuto nell’array2, ovvero quando tutti i valori dell’array1 sono contenuti in array2.
Con la query:

SELECT * FROM test1 WHERE test1.parametro <@ '{6,7,8,10}'

verrà trovata (in base ai valori inseriti precedentemente) solo una riga, quella con l’array:

{6,8}

in quanto entrambi i numeri 6 e 8 sono contenuti nell’array di ricerca.

OVERLAP – array1 && array2

L’operatore overlap permette di trovare i record nei quali i due array hanno almeno un elemento in comune.
Con la query:

SELECT * FROM test1 WHERE test1.parametro && '{6,7,8,10}'

trova le righe con gli array:

{1,2,4,5,6}
{6,8}
{1,7,8,9}

Per una lista completa degli operatori disponibili per la gestione degli array vedere la tabella a questo indirizzo:  http://www.postgresql.org/docs/current/static/intarray.html