IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

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]

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, « Constantes de chaînes » 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, « Opérateurshstore » et les fonctions sont disponibles dans Tableau F.9, « Fonctions hstore ».

Tableau F.8. Opérateurshstore

Opérateur Description Exemple Résultat
hstore -> text obtenir 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 || hstore concaténation de hstore 'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore "a"=>"b", "c"=>"x", "d"=>"q"
hstore ? text hstore 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 @> hstore l'opérande gauche contient-il l'opérande droit ? 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' t
hstore <@ hstore l'opérande gauche est-il contenu dans l'opérande droit ? 'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' f
hstore - text supprimer 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 - hstore supprimer 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 #= hstore remplacer les chanmps dans record avec des valeurs correspondantes à hstore see Examples section  
%% hstore convertir hstore en un tableau de clés et valeurs alternatives %% 'a=>foo, b=>bar'::hstore {a,foo,b,bar}
%# hstore convertir 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

Fonction Type en retour Description Exemple Résultat
hstore(record) hstore construire un hstore à partir d'un RECORD ou d'un ROW hstore(ROW(1,2)) f1=>1,f2=>2
hstore(text[]) hstore construire un hstore à partir d'un tableau, qui peut être soit un tableau clé/valeur soit un tableau à deux dimensions hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']]) a=>1, b=>2, c=>3, d=>4
hstore(text[], text[]) hstore construire un hstore à partir des tableaux séparés pour les clés et valeurs hstore(ARRAY['a','b'], ARRAY['1','2']) "a"=>"1","b"=>"2"
hstore(text, text) hstore construire un hstore à un seul élément hstore('a', 'b') "a"=>"b"
akeys(hstore) text[] récupérer les clés du hstore dans un tableau akeys('a=>1,b=>2') {a,b}
skeys(hstore) setof text récupérer les clés du hstore dans un ensemble skeys('a=>1,b=>2')
a
b
       
avals(hstore) text[] récupérer les valeurs du hstore dans un tableau avals('a=>1,b=>2') {1,2}
svals(hstore) setof text récupérer les valeurs du hstore dans un ensemble svals('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ées hstore_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 dimensions hstore_to_matrix('a=>1,b=>2') {{a,1},{b,2}}
hstore_to_json(hstore) json obtenir une valeur json à partir d'un hstore, convertissant toutes les valeurs non NULL en chaînes JSON hstore_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) jsonb obtenir une valeur jsonb à partir d'un hstore, convertissant toutes les valeurs non NULL en chaînes JSON hstore_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) json obtenir 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 JSON hstore_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) jsonb obtenir 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 JSON hstore_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[]) hstore extraire un sous-ensemble d'un hstore slice('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 ensemble select * from each('a=>1,b=>2')
 key | value
-----+-------
 a   | 1
 b   | 2
       
exist(hstore,text) boolean le hstore contient-il une clé donnée ? exist('a=>1','a') t
defined(hstore,text) boolean le hstore contient-il une valeur non NULL pour la clé ? defined('a=>NULL','a') f
delete(hstore,text) hstore supprimer toute paire correspondant à une clé donnée delete('a=>1,b=>2','b') "a"=>"1"
delete(hstore,text[]) hstore supprimer toute paire de clés correspondante delete('a=>1,b=>2,c=>3',ARRAY['a','b']) "c"=>"3"
delete(hstore,hstore) hstore supprimer les paires correspondant à celle du second argument delete('a=>1,b=>2','a=>4,b=>2'::hstore) "a"=>"1"
populate_record(record,hstore) record remplacer les champs dans record avec les valeurs correspondant au hstore voir la section Exemples  

[Note]

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]

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 exclusif sur la table mais n'a pas pour résultat une fragmentation de la table avec d'anciennes versions des lignes.

F.17.7. Transforms

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, « Python 2 et Python 3 » pour la convention de nommage PL/Python). Si vous les utilisez, les valeurs hstore sont converties en dictionnaires Python.

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