Este módulo implementa el tipo de datos hstore para almacenar conjuntos de
pares clave/valor dentro de un único valor de PostgreSQL.
Esto puede ser útil en varios escenarios, como filas con muchos atributos
que rara vez se examinan, o datos semiestructurados. Las claves y los valores son
simplemente cadenas de texto.
Este módulo se considera “trusted” (confiable), es decir, puede ser
instalado por no superusuarios que tengan el privilegio CREATE
en la base de datos actual.
hstore #
La representación en texto de un hstore, utilizada para la entrada y la salida,
incluye cero o más pares clave =>
valor separados por comas. Algunos ejemplos:
k => v foo => bar, baz => whatever "1-a" => "anything at all"
El orden de los pares no es significativo (y puede que no se reproduzca en la
salida). Los espacios en blanco entre los pares o alrededor del signo => se
ignoran. Escribe entre comillas dobles las claves y valores que incluyan espacios en blanco, comas,
signos = o >. Para incluir una comilla doble o una
barra invertida en una clave o valor, escápala con una barra invertida.
Cada clave en un hstore es única. Si declaras un hstore
con claves duplicadas, solo una se almacenará en el hstore y
no hay garantía de cuál se mantendrá:
SELECT 'a=>1,a=>2'::hstore; hstore ---------- "a"=>"1"
Un valor (pero no una clave) puede ser un NULL de SQL. Por ejemplo:
key => NULL
La palabra clave NULL no distingue entre mayúsculas y minúsculas. Escribe entre comillas dobles
NULL para tratarlo como la cadena de texto ordinaria “NULL”.
Ten en cuenta que el formato de texto de hstore, cuando se usa para la entrada,
se aplica antes de cualquier entrecomillado o escape requerido. Si estás
pasando un literal de hstore a través de un parámetro, no se necesita ningún
procesamiento adicional. Pero si lo estás pasando como una constante literal entrecomillada,
entonces cualquier carácter de comilla simple y (dependiendo de la configuración del
parámetro de configuración standard_conforming_strings) los caracteres
de barra invertida deben escaparse correctamente. Consulta la
Section 4.1.2.1 para obtener más información sobre el manejo de constantes de cadena.
En la salida, las comillas dobles siempre rodean a las claves y a los valores, incluso cuando no es estrictamente necesario.
hstore #
Los operadores proporcionados por el módulo hstore se muestran en la
Table F.6, y las funciones en la Table F.7.
Table F.6. Operadores de hstore
Operador Descripción Ejemplo(s) |
|---|
Devuelve el valor asociado a la clave dada, o
|
Devuelve los valores asociados a las claves dadas, o
|
Concatena dos
|
¿Contiene el
|
¿Contiene el
|
¿Contiene el
|
¿Contiene el operando izquierdo al derecho?
|
¿Está el operando izquierdo contenido en el derecho?
|
Elimina la clave del operando izquierdo.
|
Elimina las claves del operando izquierdo.
|
Elimina del operando izquierdo los pares que coincidan con los del operando derecho.
|
Reemplaza los campos en el operando izquierdo (que debe ser un tipo compuesto)
con los valores coincidentes del
|
Convierte el
|
Convierte el
|
Table F.7. Funciones de hstore
Además de estos operadores y funciones, se pueden usar subíndices con los valores del
tipo hstore, permitiendo que actúen como arrays asociativos. Solo se
puede especificar un único subíndice de tipo text; este se interpreta
como una clave y se obtiene o almacena el valor correspondiente. Por ejemplo,
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)
Una obtención mediante subíndices devuelve NULL si el subíndice
es NULL o si esa clave no existe en el hstore. (Por
lo tanto, la obtención mediante subíndices no es muy diferente del operador
->). Una actualización mediante subíndices falla si el
subíndice es NULL; de lo contrario, reemplaza el valor de esa clave,
añadiendo una entrada al hstore si la clave aún no existe.
hstore tiene soporte para índices GiST y GIN para los operadores @>,
?, ?& y ?|. Por ejemplo:
CREATE INDEX hidx ON testhstore USING GIST (h); CREATE INDEX hidx ON testhstore USING GIN (h);
La clase de operadores GiST gist_hstore_ops aproxima un conjunto de
pares clave/valor como una firma de mapa de bits. Su parámetro entero opcional
siglen determina la longitud de la firma en bytes. La longitud
por defecto es de 16 bytes. Los valores válidos de la longitud de la firma están
entre 1 y 2024 bytes. Las firmas más largas permiten una búsqueda más precisa
(escaneando una fracción menor del índice y menos páginas del heap), a costa de
un tamaño de índice mayor.
Ejemplo de creación de este índice con una longitud de firma de 32 bytes:
CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));
hstore también admite índices btree o hash para
el operador =. Esto permite que las columnas de tipo hstore se
declaren UNIQUE, o que se utilicen en expresiones GROUP BY,
ORDER BY o DISTINCT. El orden de clasificación
para los valores hstore no es especialmente útil, pero estos índices
pueden ser de utilidad para búsquedas de equivalencia. Crea índices para comparaciones de tipo
= de la siguiente manera:
CREATE INDEX hidx ON testhstore USING BTREE (h); CREATE INDEX hidx ON testhstore USING HASH (h);
Añadir una clave o actualizar una clave existente con un nuevo valor:
UPDATE tab SET h['c'] = '3';
Otra forma de hacer lo mismo es:
UPDATE tab SET h = h || hstore('c', '3');
Si se van a añadir o cambiar varias claves en una sola operación, el enfoque de concatenación es más eficiente que el uso de subíndices:
UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);
Eliminar una clave:
UPDATE tab SET h = delete(h, 'k1');
Convertir un registro (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 hstore en un tipo de registro (record) predefinido:
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)
Modificar un registro existente utilizando los valores de 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)
El tipo hstore, debido a su flexibilidad intrínseca, puede
contener muchas claves diferentes. Verificar las claves válidas es tarea de la
aplicación. Los siguientes ejemplos muestran varias técnicas para
verificar claves y obtener estadísticas.
Ejemplo sencillo:
SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
Usar una tabla:
CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;
Estadísticas en tiempo real (online):
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
.................
A partir de PostgreSQL 9.0, hstore utiliza una representación
interna diferente a la de las versiones anteriores. Esto no presenta ningún
obstáculo para las actualizaciones mediante volcado/restauración (dump/restore),
ya que la representación en texto (utilizada en el volcado) no ha cambiado.
En caso de una actualización binaria (binary upgrade), la compatibilidad hacia
arriba se mantiene haciendo que el nuevo código reconozca los datos con el
formato antiguo. Esto supondrá una ligera penalización en el rendimiento al
procesar datos que aún no hayan sido modificados por el nuevo código. Es posible
forzar la actualización de todos los valores de una columna de una tabla
mediante una instrucción UPDATE de la siguiente manera:
UPDATE tablename SET hstorecol = hstorecol || '';
Otra forma de hacerlo es:
UPDATE tablename SET hstorecol = hstorecol || '';
Otra forma de hacerlo es:
ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
El método ALTER TABLE requiere un bloqueo
ACCESS EXCLUSIVE en la tabla,
pero no produce un crecimiento innecesario (bloat) de la tabla con versiones antiguas de las filas.
Existen extensiones adicionales que implementan transformaciones para
el tipo hstore para los lenguajes PL/Perl y PL/Python. Las
extensiones para PL/Perl se llaman hstore_plperl
y hstore_plperlu, para PL/Perl confiable y no confiable respectivamente.
Si instalas estas transformaciones y las especificas al crear una
función, los valores hstore se mapearán a hashes de Perl. Las
extensiones para PL/Python se llaman hstore_plpython3u.
Si la utilizas, los valores de tipo hstore se mapearán a diccionarios de Python.
Oleg Bartunov <[email protected]>, Moscú, Universidad de Moscú, Rusia
Teodor Sigaev <[email protected]>, Moscú, Delta-Soft Ltd., Rusia
Mejoras adicionales por Andrew Gierth <[email protected]>,
Reino Unido