PostgreSQL può avere un vincolo di unicità sugli elementi dell’array?

Sto cercando di creare uno schema PostgreSQL per i dati host attualmente in un archivio LDAP. Parte di questi dati è l’elenco di nomi host che una macchina può avere e quell’attributo è generalmente la chiave che la maggior parte delle persone usa per trovare i record dell’host.

Una cosa che mi piacerebbe ottenere spostando questi dati su un RDBMS è la possibilità di impostare un vincolo di unicità sulla colonna nome host in modo che non possano essere assegnati nomi host duplicati. Sarebbe facile se gli host potessero avere un solo nome, ma dal momento che possono avere più di uno è più complicato.

Mi rendo conto che il modo completamente normalizzato per farlo sarebbe quello di avere una tabella degli host con una chiave esterna che punta alla tabella degli host, ma vorrei evitare che tutti debbano fare join anche per la query più semplice:

select hostnames.name,hosts.* from hostnames,hosts where hostnames.name = 'foobar' and hostnames.host_id = hosts.id; 

Ho pensato che usare gli array di PostgreSQL potesse funzionare per questo, e certamente rendono semplici le semplici query:

 select * from hosts where names @> '{foobar}'; 

Quando imposto un vincolo di univocità sull’attributo hostnames, tuttavia, considera l’intero elenco di nomi come il valore univoco anziché ciascun nome. C’è un modo per rendere ogni nome unico su ogni riga invece?

In caso contrario, qualcuno sa di un altro approccio di modellazione dei dati che avrebbe più senso?

Il percorso virtuoso

Potresti voler riconsiderare la normalizzazione dello schema. Non è necessario che tutti “si uniscano anche alla domanda più semplice”. Crea una VIEW per questo.

La tabella potrebbe avere questo aspetto:

 CREATE TABLE hostname ( hostname_id serial PRIMARY KEY ,host_id int REFERENCES host(host_id) ON UPDATE CASCADE ON DELETE CASCADE ,hostname text UNIQUE ); 

La chiave primaria surrogata hostname_id è facoltativa . Preferisco averne uno Nel tuo caso l’ hostname potrebbe essere la chiave primaria. Ma molte operazioni sono più veloci con una semplice chiave integer piccola. Creare un vincolo di chiave esterna da colbind host tabella.
Crea una vista come questa:

 CREATE VIEW v_host AS SELECT h.* ,array_agg(hn.hostname) AS hostnames -- ,string_agg(hn.hostname, ', ') AS hostnames -- text instead of array FROM host h JOIN hostname hn USING (host_id) GROUP BY h.host_id; -- works in v9.1+ 

A partire da pg 9.1 , la chiave primaria in GROUP BY copre tutte le colonne di quella tabella nell’elenco SELECT . Le note sulla versione per la versione 9.1 :

Consenti colonne non GROUP BY nell’elenco di destinazione della query quando la chiave primaria è specificata nella clausola GROUP BY

Le query possono utilizzare la vista come un tavolo. La ricerca di un nome host sarà molto più veloce in questo modo:

 SELECT * FROM host h JOIN hostname hn USING (host_id) WHERE hn.hostname = 'foobar'; 

A condizione di avere un indice host(host_id) , che dovrebbe essere il caso in cui dovrebbe essere la chiave primaria. Inoltre, il vincolo UNIQUE su hostname(hostname) implementa automaticamente l’altro indice necessario.

In Postgres 9.2+ un indice a più colonne sarebbe ancora meglio se si potesse ottenere una scansione solo indice di esso:

 CREATE INDEX hn_multi_idx ON hostname (hostname, host_id) 

A partire da Postgres 9.3 , è ansible utilizzare una MATERIALIZED VIEW , a seconda delle circostanze. Soprattutto se leggi molto più spesso di quanto scrivi sul tavolo.

Il lato oscuro (quello che hai effettivamente chiesto)

Se non riesco a convincerti della retta via, aiuterò anche il lato oscuro. Sono flessibile. 🙂

Ecco una demo su come rafforzare l’unicità degli hostname. Io uso una tabella hostname per raccogliere nomi host e un trigger host della tabella per tenerlo aggiornato. Violazioni uniche sollevano un errore e interrompono l’operazione.

 CREATE TABLE host(hostnames text[]); CREATE TABLE hostname(hostname text PRIMARY KEY); -- pk enforces uniqueness 

Funzione di trigger

 CREATE OR REPLACE FUNCTION trg_host_insupdelbef() RETURNS trigger AS $func$ BEGIN -- split UPDATE into DELETE & INSERT IF TG_OP = 'UPDATE' THEN IF OLD.hostnames IS DISTINCT FROM NEW.hostnames THEN -- keep going ELSE RETURN NEW; -- exit, nothing to do END IF; END IF; IF TG_OP IN ('DELETE', 'UPDATE') THEN DELETE FROM hostname h USING unnest(OLD.hostnames) d(x) WHERE h.hostname = dx; IF TG_OP = 'DELETE' THEN RETURN OLD; -- exit, we are done END IF; END IF; -- control only reaches here for INSERT or UPDATE (with actual changes) INSERT INTO hostname(hostname) SELECT h FROM unnest(NEW.hostnames) h; RETURN NEW; END $func$ LANGUAGE plpgsql; 

trigger:

 CREATE TRIGGER host_insupdelbef BEFORE INSERT OR DELETE OR UPDATE OF hostnames ON host FOR EACH ROW EXECUTE PROCEDURE trg_host_insupdelbef(); 

SQL Fiddle con esecuzione di prova.

Utilizzare un indice GIN sulla colonna dell’array host.hostnames e gli operatori di array per lavorare con esso:

  • Perché il mio indice di array PostgreSQL non viene utilizzato (Rails 4)?
  • Controlla se uno o più array di valori sono presenti in un array Postgres

Nel caso in cui qualcuno abbia ancora bisogno di ciò che era nella domanda originale:

 CREATE TABLE testtable( id serial PRIMARY KEY, refs integer[], EXCLUDE USING gist( refs WITH && ) ); INSERT INTO testtable( refs ) VALUES( ARRAY[100,200] ); INSERT INTO testtable( refs ) VALUES( ARRAY[200,300] ); 

e questo ti darebbe:

 ERROR: conflicting key value violates exclusion constraint "testtable_refs_excl" DETAIL: Key (refs)=({200,300}) conflicts with existing key (refs)=({100,200}). 

Controllato in Postgres 9.5 su Windows.

Si noti che questo creerebbe un indice usando l’operatore && . Quindi, quando si lavora con testtable , sarebbe più veloce controllare ARRAY[x] && refs di x = ANY( refs ) causa degli interni di indicizzazione di Postgres.

PS In genere sono d’accordo con la risposta di cui sopra, ma questo approccio è solo una buona opzione quando non devi preoccuparti veramente delle prestazioni e cose del genere.