PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 10.23 » Annexes » Modules supplémentaires fournis » hstore

F.17. hstore

Ce module code le type de données hstore pour stocker des ensembles de paires clé/valeur à l'intérieur d'une simple valeur PostgreSQL. Cela peut s'avérer utile dans divers cas, comme les lignes à attributs multiples rarement examinées ou les données semi-structurées. Les clés et les valeurs sont de simples chaînes de texte.

F.17.1. Représentation externe de hstore

La représentation textuelle d'une valeur hstore, utilisée en entrée et en sortie, inclut zéro ou plusieurs paires clé => valeur séparées par des virgules. Par exemple :

    k => v
    foo => bar, baz => whatever
    "1-a" => "anything at all"
   

L'ordre des paires n'est pas significatif (et pourrait ne pas être reproduit en sortie). Les espaces blancs entre les paires ou autour des signes => sont ignorés. Les clés et valeurs entre guillemets peuvent inclure des espaces blancs, virgules, = ou >. Pour inclure un guillemet double ou un antislash dans une clé ou une valeur, échappez-le avec un antislash.

Chaque clé dans un hstore est unique. Si vous déclarez un hstore avec des clés dupliquées, seule une sera stockée dans hstore et il n'y a pas de garantie sur celle qui sera conservée :

SELECT 'a=>1,a=>2'::hstore;
  hstore
----------
 "a"=>"1"
   

Une valeur, mais pas une clé, peut être un NULL SQL. Par exemple :

    key => NULL
   

Le mot-clé NULL est insensible à la casse. La chaîne NULL entre des guillemets doubles fait que le chaîne est traitées comme tout autre chaîne.

Note

Gardez en tête que le format texte hstore, lorsqu'il est utilisé en entrée, s'applique avant tout guillemet ou échappement nécessaire. Si vous passez une valeur litérale de type hstore via un paramètre, aucun traitement supplémentaire n'est nécessaire. par contre, si vous la passez comme constante litérale entre guillemets, alors les guillemets simples et, suivant la configuration du paramètre standard_conforming_strings, les caractères antislash doivent être échappés correctement. Voir Section 4.1.2.1 pour plus d'informations sur la gestion des chaînes constantes.

En sortie, guillemets doubles autour des clés et valeurs, en permanence, même quand cela n'est pas strictement nécessaire.

F.17.2. Opérateurs et fonctions hstore

Les opérateurs fournis par le module hstore sont montrés dans Tableau F.8 et les fonctions sont disponibles dans Tableau F.9.

Tableau F.8. Opérateurshstore

OpérateurDescriptionExempleRésultat
hstore -> textobtenir la valeur de la clé (NULL si inexistante)'a=>x, b=>y'::hstore -> 'a'x
hstore -> text[]obtenir les valeurs pour les clés (NULL si inexistant)'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']{"z","x"}
hstore || hstoreconcaténation de hstore'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore"a"=>"b", "c"=>"x", "d"=>"q"
hstore ? texthstore contient-il une clé donnée ?'a=>1'::hstore ? 'a't
hstore ?& text[]hstore contient-il toutes les clés indiquées ?'a=>1,b=>2'::hstore ?& ARRAY['a','b']t
hstore ?| text[]hstore contient-il une des clés spécifiées ?'a=>1,b=>2'::hstore ?| ARRAY['b','c']t
hstore @> hstorel'opérande gauche contient-il l'opérande droit ?'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1't
hstore <@ hstorel'opérande gauche est-il contenu dans l'opérande droit ?'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'f
hstore - textsupprimer la clé à partir de l'opérande gauche'a=>1, b=>2, c=>3'::hstore - 'b'::text"a"=>"1", "c"=>"3"
hstore - text[]supprimer les clés à partir de l'opérande gauche'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']"c"=>"3"
hstore - hstoresupprimer les paires correspondantes à partir de l'opérande gauche'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore"a"=>"1", "c"=>"3"
record #= hstoreremplacer les chanmps dans record avec des valeurs correspondantes à hstoresee Examples section 
%% hstoreconvertir hstore en un tableau de clés et valeurs alternatives%% 'a=>foo, b=>bar'::hstore{a,foo,b,bar}
%# hstoreconvertir hstore en un tableau clé/valeur à deux dimensions%# 'a=>foo, b=>bar'::hstore{{a,foo},{b,bar}}

Avant PostgreSQL 8.2, les opérateurs de contenance @> et <@ étaient appelés respectivement @ et ~. Ces noms sont toujours disponibles mais sont devenus obsolètes et pourraient éventuellement être supprimés. Les anciens noms sont inversés par rapport à la convention suivie par les types de données géométriques.

Tableau F.9. Fonctions hstore

FonctionType en retourDescriptionExempleRésultat
hstore(record)hstoreconstruire un hstore à partir d'un RECORD ou d'un ROWhstore(ROW(1,2))f1=>1,f2=>2
hstore(text[])hstoreconstruire un hstore à partir d'un tableau, qui peut être soit un tableau clé/valeur soit un tableau à deux dimensionshstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']])a=>1, b=>2, c=>3, d=>4
hstore(text[], text[])hstoreconstruire un hstore à partir des tableaux séparés pour les clés et valeurshstore(ARRAY['a','b'], ARRAY['1','2'])"a"=>"1","b"=>"2"
hstore(text, text)hstoreconstruire un hstore à un seul élémenthstore('a', 'b')"a"=>"b"
akeys(hstore)text[]récupérer les clés du hstore dans un tableauakeys('a=>1,b=>2'){a,b}
skeys(hstore)setof textrécupérer les clés du hstore dans un ensembleskeys('a=>1,b=>2')
a
b
       
avals(hstore)text[]récupérer les valeurs du hstore dans un tableauavals('a=>1,b=>2'){1,2}
svals(hstore)setof textrécupérer les valeurs du hstore dans un ensemblesvals('a=>1,b=>2')
1
2
       
hstore_to_array(hstore)text[]récupérer les clés et les valeurs du hstore sous la forme d'un tableau de clés et valeurs alternéeshstore_to_array('a=>1,b=>2'){a,1,b,2}
hstore_to_matrix(hstore)text[]récupérer les clés et valeurs hstore sous la forme d'un tableau à deux dimensionshstore_to_matrix('a=>1,b=>2'){{a,1},{b,2}}
hstore_to_json(hstore)jsonobtenir une valeur json à partir d'un hstore, convertissant toutes les valeurs non NULL en chaînes JSONhstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'){"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_jsonb(hstore)jsonbobtenir une valeur jsonb à partir d'un hstore, convertissant toutes les valeurs non NULL en chaînes JSONhstore_to_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'){"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_json_loose(hstore)jsonobtenir une valeur json à partir d'un hstore, mais en essayant de distinguer les valeurs numériques et booléennes pour qu'elles ne soient pas entre guillemets dans le JSONhstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'){"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
hstore_to_jsonb_loose(hstore)jsonbobtenir une valeur jsonb à partir d'un hstore, mais essaie de distinguer les valeurs numériques et booléenne pour qu'elles soient sans guillemets dans le JSONhstore_to_jsonb_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4'){"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
slice(hstore, text[])hstoreextraire un sous-ensemble d'un hstoreslice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])"b"=>"2", "c"=>"3"
each(hstore)setof (key text, value text)récupérer les clés et valeurs du hstore dans un ensembleselect * from each('a=>1,b=>2')
 key | value
-----+-------
 a   | 1
 b   | 2
       
exist(hstore,text)booleanle hstore contient-il une clé donnée ?exist('a=>1','a')t
defined(hstore,text)booleanle hstore contient-il une valeur non NULL pour la clé ?defined('a=>NULL','a')f
delete(hstore,text)hstoresupprimer toute paire correspondant à une clé donnéedelete('a=>1,b=>2','b')"a"=>"1"
delete(hstore,text[])hstoresupprimer toute paire de clés correspondantedelete('a=>1,b=>2,c=>3',ARRAY['a','b'])"c"=>"3"
delete(hstore,hstore)hstoresupprimer les paires correspondant à celle du second argumentdelete('a=>1,b=>2','a=>4,b=>2'::hstore)"a"=>"1"
populate_record(record,hstore)recordremplacer les champs dans record avec les valeurs correspondant au hstorevoir la section Exemples 

Note

La fonction hstore_to_json est utilisée quand une valeur hstore est convertie en valeur json. De la même façon, hstore_to_jsonb est utilisée quand une valeur hstore est convertie en valeur jsonb.

Note

La fonction populate_record est en fait déclarée avec anyelement, et non pas record, en tant que premier argument mais elle rejettera les types qui ne sont pas des RECORD avec une erreur d'exécution.

F.17.3. Index

hstore dispose du support pour les index GiST et GIN pour les opérateurs @>, ?, ?& et ?|. Par exemple :

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);
  

hstore supporte aussi les index btree ou hash pour l'opérateur =. Cela permet aux colonnes hstore d'être déclarées UNIQUE et d'être utilisées dans des expressions GROUP BY, ORDER BY et DISTINCT. L'ordre de tri pour les valeurs hstore n'est pas particulièrement utile mais ces index pourraient l'être pour des recherches d'équivalence. Créer des index de comparaisons = de la façon suivante :

CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);
  

F.17.4. Exemples

Ajouter une clé, ou mettre à jour une clé existante avec une nouvelle valeur :

UPDATE tab SET h = h || hstore('c', '3');
  

Supprimer une clé :

UPDATE tab SET h = delete(h, 'k1');
  

Convertiir un type record en un hstore :

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT hstore(t) FROM test AS t;
                   hstore
---------------------------------------------
 "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)
  

Convertir un type hstore en un type record prédéfini :

CREATE TABLE test (col1 integer, col2 text, col3 text);

SELECT * FROM populate_record(null::test,
                              '"col1"=>"456", "col2"=>"zzz"');
 col1 | col2 | col3
------+------+------
  456 | zzz  |
(1 row)
  

Modifier une enregistrement existant en utilisant les valeurs provenant d'un hstore :

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
 col1 | col2 | col3
------+------+------
  123 | foo  | baz
(1 row)
  

F.17.5. Statistiques

Le type hstore, du fait de sa libéralité intrinsèque, peut contenir beaucoup de clés différentes. C'est à l'application de vérifier la validité des clés. Les exemples ci-dessous présentent plusieurs techniques pour vérifier les clés et obtenir des statistiques.

Exemple simple :

SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
  

En utilisant une table :

SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
  

Statistiques en ligne :

SELECT key, count(*) FROM
  (SELECT (each(h)).key FROM testhstore) AS stat
  GROUP BY key
  ORDER BY count DESC, key;
    key    | count
-----------+-------
 line      |   883
 query     |   207
 pos       |   203
 node      |   202
 space     |   197
 status    |   195
 public    |   194
 title     |   190
 org       |   189
...................
  

F.17.6. Compatibilité

À partir de PostgreSQL 9.0, hstore utilise une représentation interne différente des anciennes versions. Cela ne présente aucun obstacle pour les mises à jour par sauvegarde/restauration car la représentation textuelle utilisée dans la sauvegarde n'est pas changée.

Dans le cas d'une mise à jour binaire, la compatibilité ascendante est maintenue en faisant en sorte que le nouveau code reconnaisse les données dans l'ancien format. Ceci aura pour conséquence une légère pénalité au niveau des performances lors du traitement de données qui n'aura pas été modifiée par le nouveau code. Il est possible de forcer une mise à jour de toutes les valeurs d'une colonne de la table en réalisant la requête UPDATE suivante :

UPDATE nom_table SET col_hstore = col_hstore || '';
  

Une autre façon de le faire :

ALTER TABLE nom_table ALTER col_hstore TYPE col_hstore USING hstorecol || '';
   

La méthode ALTER TABLE requiert un verrou de type ACCESS EXCLUSIVE sur la table mais n'a pas pour résultat une fragmentation de la table avec d'anciennes versions des lignes.

F.17.7. Transformations

Des extensions supplémentaires sont disponibles pour implémenter des transformations pour le type hstore et les langages PL/Perl et PL/Python. Les extensions pour PL/Perl sont appelés hstore_plperl et hstore_plperlu, pour les deux versions de PL/Perl. Si vous installez ces transformations et si vous les spécifiez lors de la création d'une fonction, les valeurs hstore sont converties en hachage Perl. Les extensions pour PL/Python sont appelées hstore_plpythonu, hstore_plpython2u et hstore_plpython3u (voir Section 45.1 pour la convention de nommage PL/Python). Si vous les utilisez, les valeurs hstore sont converties en dictionnaires Python.

Attention

Il est fortement recommandé que les extensions de transformation soient installées dans le même schéma que hstore. Sinon, il existe un risque de sécurité si le schéma d'une extension de transformation contient des objets définis par un utilisateur hostile.

F.17.8. Auteurs

Oleg Bartunov , Moscou, Université de Moscou, Russie

Teodor Sigaev , Moscou, Delta-Soft Ltd., Russie

Additional enhancements by Andrew Gierth , United Kingdom