PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 15.10 » Annexes » Modules supplémentaires fournis » hstore
Précédent Niveau supérieur Suivant
F.17. fuzzystrmatch Sommaire F.19. intagg

F.18. hstore

F.18.1. Représentation externe de hstore
F.18.2. Opérateurs et fonctions hstore
F.18.3. Index
F.18.4. Exemples
F.18.5. Statistiques
F.18.6. Compatibilité
F.18.7. Transformations
F.18.8. Auteurs

Ce module code le type de données hstore, qui permet de stocker des ensembles de paires clé/valeur dans une simple valeur PostgreSQL. Ce peut être utile dans divers cas, comme des lignes avec de nombreux attributs rarement examinés ou des données semi-structurées. Les clés et les valeurs sont de simples chaînes de texte.

Ce module est considéré comme « trusted », c'est-à-dire qu'il peut être installé par des utilisateurs simples (sans attribut SUPERUSER) ayant le droit CREATE sur la base de données courante.

F.18.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 peut ne pas être reproduit en sortie). Les espaces blancs entre les paires ou autour des signes => sont ignorés. Ajoutez des guillemets pour les clés et valeurs contenant des espaces, 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. Ajoutez des guillemets autour du NULL pour qu'il soit traité comme toute 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 littérale de type hstore via un paramètre, aucun traitement supplémentaire n'est nécessaire. Par contre, si vous la passez comme constante litté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, mettez toujours des guillemets doubles autour des clés et valeurs, même quand cela n'est pas strictement nécessaire.

F.18.2. Opérateurs et fonctions hstore

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

Tableau F.7. Opérateurshstore

Operator

Description

Exemple(s)

hstore -> text → text

Renvoie la valeur associée à une clé donnée (NULL si inexistante).

'a=>x, b=>y'::hstore -> 'a' → x

hstore -> text[] → text[]

Retourne la valeur associée à des clés (NULL si inexistantes).

'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] → {"z","x"}

hstore || hstore → hstore

Concatène deux hstores.

'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore → "a"=>"b", "c"=>"x", "d"=>"q"

hstore ? text → boolean

Ce hstore contient-il une clé donnée ?

'a=>1'::hstore ? 'a' → t

hstore ?& text[] → boolean

Ce hstore contient-il toutes les clés indiquées ?

'a=>1,b=>2'::hstore ?& ARRAY['a','b'] → t

hstore ?| text[] → boolean

Ce hstore contient-il une des clés indiquées ?

'a=>1,b=>2'::hstore ?| ARRAY['b','c'] → t

hstore @> hstore → boolean

L'opérande gauche contient-il le droit ?

'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' → t

hstore <@ hstore → boolean

L'opérande gauche est-il contenu dans le droit ?

'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' → f

hstore - text → hstore

Supprime la clé dans l'opérande gauche.

'a=>1, b=>2, c=>3'::hstore - 'b'::text → "a"=>"1", "c"=>"3"

hstore - text[] → hstore

Supprime les clés dans l'opérande gauche.

'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] → "c"=>"3"

hstore - hstore → hstore

Supprime les clés de l'opérande gauche qui correspondent à des paires de l'opérande droit.

'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore → "a"=>"1", "c"=>"3"

anyelement #= hstore → anyelement

Remplace les champs de l'opérande gauche (qui doit être un type composite) avec les valeurs correspondantes du hstore.

ROW(1,3) #= 'f1=>11'::hstore → (11,3)

%% hstore → text[]

Convertit un hstore en tableau alternant les clés et les valeurs.

%% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar}

%# hstore → text[]

Convertit hstore en un tableau à deux dimensions clé/valeur.

%# 'a=>foo, b=>bar'::hstore → {{a,foo},{b,bar}}


Tableau F.8. hstore Functions

Fonction

Description

Exemple(s)

hstore ( record ) → hstore

Construit un hstore à partir d'un enregistrement ou d'une ligne.

hstore(ROW(1,2)) → "f1"=>"1", "f2"=>"2"

hstore ( text[] ) → hstore

Construit 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']) → "a"=>"1", "b"=>"2"

hstore(ARRAY[['c','3'],['d','4']]) → "c"=>"3", "d"=>"4"

hstore ( text[], text[] ) → hstore

Construit un hstore à partir de tableaux séparés pour les clés et les valeurs.

hstore(ARRAY['a','b'], ARRAY['1','2']) → "a"=>"1", "b"=>"2"

hstore ( text, text ) → hstore

Construit un hstore à un seul élément.

hstore('a', 'b') → "a"=>"b"

akeys ( hstore ) → text[]

Extrait les clés du hstore dans un tableau.

akeys('a=>1,b=>2') → {a,b}

skeys ( hstore ) → setof text

Extrait les clés du hstore dans un ensemble.

skeys('a=>1,b=>2') → 

 a
 b

avals ( hstore ) → text[]

Extrait les valeurs du hstore dans un tableau.

avals('a=>1,b=>2') → {1,2}

svals ( hstore ) → setof text

Extrait les valeurs du hstore dans un ensemble.

svals('a=>1,b=>2') → 

 1
 2

hstore_to_array ( hstore ) → text[]

Extrait 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[]

Extrait 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

Convertit une valeur hstore en json, en convertissant toutes les valeurs non NULL en chaînes JSON.

Cette fonction est appelée implicitement quand une valeur hstore est convertie vers du 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

Convertit une valeur hstore en jsonb, en convertissant toutes les valeurs non NULL en chaînes JSON.

Cette fonction est appelée implicitement quand une valeur hstore est convertie vers du jsonb.

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

Convertit un hstore en une valeur json, mais en essayant de repérer 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

Convertit un hstore en une valeur jsonb, mais en essayant de repérer les valeurs numériques et booléennes pour qu'elles ne soient pas entre 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

Extrait un sous-ensemble d'un hstore contenant seulement les clés indiquées.

slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x']) → "b"=>"2", "c"=>"3"

each ( hstore ) → setof record ( key text, value text )

Extrait les clés et valeurs d'un hstore sous la forme d'un ensemble d'enregistrements.

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 comme clé ?

defined('a=>NULL', 'a') → f

delete ( hstore, text ) → hstore

Supprime la paire correspondant à une clé donnée.

delete('a=>1,b=>2', 'b') → "a"=>"1"

delete ( hstore, text[] ) → hstore

Supprime toutes les paires correspondant à ces clés.

delete('a=>1,b=>2,c=>3', ARRAY['a','b']) → "c"=>"3"

delete ( hstore, hstore ) → hstore

Supprime les paires correspondant à celles du second argument.

delete('a=>1,b=>2', 'a=>4,b=>2'::hstore) → "a"=>"1"

populate_record ( anyelement, hstore ) → anyelement

Remplacer tous les champs de l'opérande gauche (qui doit être un type composite) avec les valeurs correspondantes du hstore.

populate_record(ROW(1,2), 'f1=>42'::hstore) → (42,2)


En plus de ces opérateurs et fonctions, les valeurs du type hstore peuvent utiliser des indices, leur permettant ainsi d'agir comme des tableaux associatifs. Seul un indice simple de type text peut être indiqué ; il est interprété comme la clé, et la valeur correspondante est récupérée ou stockée. Par exemple, 

CREATE TABLE mytable (h hstore);
INSERT INTO mytable VALUES ('a=>b, c=>d');
SELECT h['a'] FROM mytable;
 h
---
 b
(1 row)

UPDATE mytable SET h['c'] = 'new';
SELECT h FROM mytable;
          h
----------------------
 "a"=>"b", "c"=>"new"
(1 row)

Une lecture par indice renvoie NULL si l'indice est NULL ou si la clé n'existe pas dans ce hstore. (De ce fait, une lecture par indice n'est pas fortement différente de l'opérateur ->.) Une mise à jour par indice échoue si l'indice est NULL ; sinon elle remplace la valeur pour cette clé, ajoutant une entrée au hstore si la clé n'existe pas déjà.

F.18.3. Index

hstore est supporté par 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);

La classe d'opérateur GiST gist_hstore_ops opère une approximation d'un ensemble de clés/valeurs avec une signature bitmap. Son paramètre optionnel entier, siglen, détermine la longueur de la signature en octets. La longueur par défaut est 16 octets. Les valeurs valides de longueur de signature vont de 1 à 2024 octets. Des signatures plus longues mènent à des recherches plus précises (scannant une partie plus courte de l'index et moins de pages de la table), au prix d'un index plus gros.

Exemple de création d'un tel index avec une longueur de signature de 32 octets : 

CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));

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 peuvent servir à des recherches d'équivalence. Créez 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.18.4. Exemples

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

UPDATE tab SET h['c'] = '3';

Voici une autre façon de faire la même chose : 

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

Si plusieurs clés doivent être ajoutées ou modifiées en une seule opération, l'approche par concaténation est plus efficace que par indice : 

UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);

Supprimer une clé : 

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

Convertir un enregistrement (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 un 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.18.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 : 

CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value 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.18.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'auront pas été modifiées 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 un gonflement de la table à cause d'anciennes versions des lignes.

F.18.7. Transformations

Des extensions supplémentaires sont disponibles, qui implémentent des transformations pour le type hstore pour les langages PL/Perl et PL/Python. Les extensions pour PL/Perl sont appelées 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. L'extension pour PL/Python s'appelle hstore_plpython3u. Si vous l'utilisez, les valeurs hstore sont converties en dictionnaires Python.

De ces extensions additionnelles, hstore_plperl est considéré « trusted » ; pas les autres.

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.18.8. Auteurs

Oleg Bartunov , Moscou, Université de Moscou, Russie

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

Améliorations supplémentaires par Andrew Gierth , Royaume Uni