PostgreSQL: eseguire ricerche su colonne di tipo array

ATTENZIONE! Il post ha più di 2 anni e le informazioni contenute potrebbero essere obsolete (ad esempio a causa di un aggiornamento di versione rispetto agli elementi descritti o links modificati da siti esterni).

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: containsis 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: [code lang="plain"] {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: [code lang="plain"] {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: [code lang="plain"] {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

Server dedicato

Condividi questo post


Commenti