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.
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]](images/note.png)
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.
Les opérateurs fournis par le module hstore sont montrés dans Tableau F.6, « Opérateurshstore » et les fonctions sont disponibles dans Tableau F.7, « Fonctions hstore ».
Tableau F.6. 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.7. 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 | 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_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} |
| 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 |
La fonction hstore_to_json est utilisée quand une valeur hstore est convertie en valeur json.
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.
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);
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)
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
...................
À 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.
Oleg Bartunov <oleg@sai.msu.su>, Moscou, Université de Moscou, Russie
Teodor Sigaev <teodor@sigaev.ru>, Moscou, Delta-Soft Ltd., Russie
Additional enhancements by Andrew Gierth <andrew@tao11.riddles.org.uk>, United Kingdom
fuzzystrmatch
