Documentation PostgreSQL 14.22 » Annexes » Modules supplémentaires fournis » hstore

Précédent | Niveau supérieur | Suivant
F.15. fuzzystrmatch | Sommaire | F.17. intagg

## F.16. hstore

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.16.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.16.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érateurs`hstore`

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 `hstore`s. `'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.16.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érateurs 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.16.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.16.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.16.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.16.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. Les extensions pour PL/Python sont appelées `hstore_plpythonu`, `hstore_plpython2u` et `hstore_plpython3u` (voir Section 46.1 pour la convention de nommage PL/Python). Si vous les 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.16.8. Auteurs

Oleg Bartunov `<oleg@sai.msu.su>`, Moscou, Université de Moscou, Russie

Teodor Sigaev `<teodor@sigaev.ru>`, Moscou, Delta-Soft Ltd., Russie

Améliorations supplémentaires par Andrew Gierth `<andrew@tao11.riddles.org.uk>`, Royaume Uni