Definición de los datos

Los espacios de tablas

Un espacio de tablas es un directorio de un sistema de archivos, en el que PostgreSQL escribe los archivos de las tablas y de los índices. Por defecto, PostgreSQL dispone de un espacio de tablas ubicado en el directorio del grupo de bases de datos. Es posible crear otros espacios de tablas, que permiten al administrador seleccionar de esta manera la ubicación del almacenamiento de una tabla o de un índice.

Hay varios motivos que pueden hacer que un administrador cree un espacio de tablas:

Por lo tanto, un espacio de tablas es una herramienta que se puede utilizar por el administrador del servidor de bases...

Las bases de datos

En una instancia de PostgreSQL, una base de datos es un contenedor. Contiene los esquemas, las tablas, los índices y todos los objetos útiles para una aplicación. También recibe las conexiones desde las aplicaciones cliente. En efecto, cuando se abre una conexión en una base de datos particular, no es posible utilizar directamente los objetos creados en otras bases de datos.

Por lo tanto, es importante repartir correctamente los objetos y los datos de las aplicaciones en las bases de datos, principalmente utilizando la noción de esquema. La creación de una base de datos se puede realizar con la sentencia CREATE DATABASE o con el comando del sistema operativo createdb.

Algunos argumentos permiten personalizar la creación de una base de datos:

Los esquemas

Los esquemas, también llamados espacios de nombres, existen implícitamente en todas las bases de datos. Se trata de una noción puramente lógica, que permite agrupar por nombre los objetos como las tablas, las vistas o las funciones, que definen de esta manera una ruta de acceso.

De esta manera, en una misma base de datos hay varios objetos que pueden tener el mismo nombre, pero ser físicamente distintos. El nombre del esquema utiliza como prefijo el nombre del objeto. En consecuencia, la distinción entre los objetos es clara.

Se puede hacer una analogía con la noción de paquete o namespace en diferentes lenguajes de programación. Objetos con nombres idénticos (atributos, métodos...) pero con una implementación diferente se pueden distinguir por el nombre del paquete. En PostgreSQL, la noción es idéntica, considerando el hecho de que una clase se corresponde con una tabla y la implementación, con los datos. Por lo tanto, el paquete se corresponde con un esquema. A pesar de esta analogía, observe una diferencia importante: solo existe un nivel de profundidad con los esquemas. En PostgreSQL, no es posible crear esquemas en un esquema.

Inicialmente existen varios esquemas en una base de datos:

Las tablas

Una tabla es el elemento básico de un servidor de bases de datos. En ella es donde se almacenan los datos, y la organización de las tablas determina la calidad de la base de datos.

La elección de los tipos de datos y los enlaces entre las tablas forman parte de la etapa de diseño de la base de datos para que tenga buen rendimiento y sea duradera.

La sentencia CREATE TABLE permite poner en marcha las elecciones de este diseño. 

La sinopsis mínima de este comando es la siguiente:

CREATE [ TEMP | UNLOGGED ] TABLE [ IF NOT EXISTS ] nombretabla ( [ 
 { nombrecol tipo [ COLLATE collation ] [ restriccioncolumna[...] ] 
   | restricciontabla   
   | LIKE source_table [ like_option ... ] 
 } [,...] ] )   
[ INHERITS ( parent_table [, ... ] ) ] 
[ WITH ( storage_parameter [= value] [, ... ] ) 
 | WITH OIDS | WITHOUT OIDS ]   
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]  
[ TABLESPACE tablespace_name ] 

Previo a la definición de los atributos de una tabla, se puede indicar un determinado número de características adicionales para modificar el comportamiento de una tabla en la base de datos.

En primer lugar, los modificadores TEMP y UNLOGGED permiten cambiar la naturaleza de una tabla:

Las vistas

Una vista es el equivalente de una consulta SELECT, pero almacenada en forma de una relación equivalente a una tabla, aunque en modo solo lectura. Los datos mostrados por una vista no son modificables y la consulta SELECT subyacente se lanza en cada llamada a la vista. El interés de una vista es presentar los datos de una determinada manera y estar siempre disponible.

La sentencia CREATE VIEW permite crear una vista basándose en una consulta SELECT, como en la siguiente sinopsis:

CREATE [ OR REPLACE ] [ TEMP ] VIEW nombre [ ( columna, ... ) ] 
 WITH ( nombreopción = valor] [, ... ] ) 
 AS consulta 
 WITH [ CASCADED | LOCAL ] CHECK OPTION; 

Por defecto, el nombre de las columnas de la vista se corresponden con el nombre de las columnas devueltas por la consulta SELECT. Es posible modificar estos nombres de columna indicándolos entre paréntesis, después del nombre de la tabla.

Además, es posible sustituir una vista existente, añadiendo las palabras clave OR REPLACE después de CREATE, con la condición de que las columnas de la vista sean identicas en número y tipo.

La sentencia TEMP modifica el alcance de la vista, haciéndola accesible únicamente durante la sesión actual y solo durante el tiempo de la sesión. Por lo tanto, la vista se elimina al final de la sesión. Cuando una vista temporal tiene el mismo nombre que un objeto existente, es prioritaria sobre este objeto durante la sesión.

Es posible actualizar los datos presentados por una vista con algunas condiciones concretas. La sentencia WITH CHECK OPTION permite verificar si las modificaciones añadidas a los datos se corresponden con las cláusulas de la vista, es...

El sistema de reglas

En PostgreSQL existe un sistema de reescritura de consultas, llamado reglas de reescritura, que permite modificar en profundidad la interpretación de las sentencias SQL. El comportamiento es muy cercano al de los triggers, pero, mientras que un trigger permite acciones antes o después de una consulta, una regla permite devolver una acción para realizarla en su lugar.

La sinopsis de la sentencia de creación de una regla es la siguiente:

CREATE [ OR REPLACE ] RULE nombre AS ON evento 
   TO table [ WHERE condición ] 
   DO [ ALSO | INSTEAD ] { NOTHING | comando | 
( comando; comando ... ) } 

Las palabras clave OR REPLACE permiten sustituir una regla existente, por lo tanto con el mismo nombre.

El evento debe ser una de las cuatro sentencias de manipulación de los datos (SELECT, UPDATE, INSERT y DELETE).

La condición permite filtrar la reescritura, en función de los valores del registro seleccionado, con la palabra clave OLD que designa al registro.

La palabra clave ALSO indica que se realiza la consulta que desencadena la reescritura, mientras que la palabra clave INSTEAD indica que esta consulta no se ejecutará más.

Para terminar, se indica la acción que se ha de realizar o de lo contrario se indica la palabra clave NOTHING. Es posible realizar varias acciones indicándolas entre paréntesis.

La herencia

PostgreSQL propone un sistema de herencia para las tablas, lo que permite a una tabla hija heredar las columnas de la tabla madre y, además, tener sus propias columnas. Cuando se inserta una tupla en la tabla hija, los datos también son visibles desde la tabla madre. Solo se almacenan físicamente en esta tabla las columnas propias de la tabla hija.

Por ejemplo, la tabla prestaciones de la base de datos clientes permite almacenar información genérica de las prestaciones. Creando una tabla formaciones, que hereda de la tabla prestaciones, es posible especializar esta tabla, añadiendo campos, como en el siguiente ejemplo:

CREATE TABLE formaciones  ( 
  num_becarios int, 
  plandeestudios varchar(25), 
  tipo varchar(5) check ( tipo in ('intra','inter') )) 
INHERITS (prestaciones); 

La descripción de la tabla por el comando \d muestra que las columnas de la tabla madre forman efectivamente parte de esta nueva tabla:

clientes=# \d formaciones   
                  Tabla "public.formaciones"   
    Columna        |          Tipo            | Modificadores 
——————————————————-+——————————————————————————+———————-...

Administración de datos externos

PostgreSQL dispone de mecanismos que permiten acceder a los datos situados en el exterior de una instancia. La implementación responde al estándar SQL/MED.

Esta implementación permite utilizar un componente externo que define los métodos útiles para conectarse a un servicio, como un servidor de base de datos, o abrir un recurso, como un archivo CSV. Estos componentes no se proporcionan con PostgreSQL, excepto el conector hacia PostgreSQL, sino por los proyectos externos. Estas implementaciones se llaman «wrappers».

Si bien este mecanismo permite utilizar datos externos en modo lectura y escritura, no todos los componentes específicos implementan la escritura de datos.

Una vez que se instala el componente externo, es posible definir una conexión hacia un servicio externo y establecer una correspondencia entre un usuario local de la instancia PostgreSQL con un usuario autorizado para conectarse al servicio externo.

Para terminar, una tabla extranjera se crea para hacer la correspondencia entre una entidad del servicio externo y una relación local a la instancia PostgreSQL.

Los índices

Un índice es una buena herramienta para mejorar el rendimiento de la base de datos. La analogía con un libro permite entender el papel que juega un índice en una base de datos. Sin índices, cuando un lector busca una información, debe leer el libro hasta encontrar lo que busca. Según el tamaño del libro y el lugar en el que se sitúa la información, inicio o fin, la búsqueda puede ser larga. En la mayor parte de los libros técnicos, el editor incorpora un índice de algunas páginas, que contiene las palabras clave consideradas susceptibles de ser buscadas para que resulte más fácil encontrar el concepto. El lector no tiene más que buscar en el índice e ir a la página indicada.

Los servidores de bases de datos almacenan los datos en las tablas y deben leer las tablas cuando un usuario busca un dato. El método más sencillo es recorrer secuencialmente una tabla hasta encontrar el dato buscado. Este método funciona bien mientras la tabla tenga un volumen que haga que los tiempos para recorrerla sean correctos. Más allá de un límite que depende del tipo de datos utilizados, del número de columnas de la tabla y del número de tuplas, el recorrido secuencial de la tabla es demasiado largo para obtener un tiempo de respuesta razonable. Se hace necesario crear uno o varios índices, en función de las búsquedas realizadas sobre la tabla.

La analogía con el libro termina aquí. En efecto, un libro tiene un contenido estático, al contrario de lo que sucede con una base de datos, cuyo contenido evoluciona con el tiempo. Esto significa que los índices se deben actualizar al mismo tiempo que la tabla. Cuantos más índices asociados tenga una tabla, más tiempo lleva la actualización durante las operaciones de inserción, actualización o eliminación de los datos. El tiempo susceptible de ganarse en la lectura se pierde durante la escritura.

Por lo tanto, la creación de un índice es un delicado equilibrio entre la mejora deseada en la lectura y la bajada de rendimiento durante la escritura. El sencillo hecho de crear muchos índices sobre una misma tabla puede penalizar.

Además, un índice ocupa un volumen no despreciable en el sistema de archivos...

Secuencias y atributos de identidad

Una secuencia es un tipo de objeto un poco particular. Se corresponde con un contador que se puede manipular con algunas funciones; principalmente la función nextval(), que permite incrementar el valor del contador y recuperarlo. Esto permite por ejemplo obtener una especie de incremento automático de una columna. Además, es posible compartir una misma secuencia entre varias tablas, creando de esta manera un identificador único intertablas.

El atributo de identidad aparece con la versión 10 de PostgreSQL. Se trata de una funcionalidad muy cercana a las secuencias, pero implementando en PostgreSQL lo que se define en la norma SQL ISO.

Los tipos de datos SERIAL y BIGSERIAL crean automáticamente una secuencia y utilizan la función nextval() en la expresión del valor por defecto de la columna.

En una tabla como la tabla prestaciones, la clave primaria se define como un entero. Es posible utilizar una secuencia para alimentarla automáticamente. El siguiente ejemplo muestra el uso más sencillo:

CREATE TABLE prestaciones (   prest_id serial primary key ,   
  [ ... ]    
); 

que es equivalente a:

CREATE SEQUENCE prest_id_seq;   
CREATE TABLE prestaciones (   
   prest_id integer default nextval( prest_id_seq )   
primary key ,   
   [ ... ]    
); 

De esta manera...

Tipos de datos

Existe muchos tipos de datos en PostgreSQL, que se corresponden con la mayor parte de los tipos de datos de la norma SQL.

Dominios

Un dominio de datos es una extensión de un tipo de datos asociado a las restricciones y verificaciones que solo permiten definir una vez este conjunto y reutilizarlo en varias tablas. Por ejemplo, un campo direccion_correo electronico se puede definir como dominio y reutilizar en toda la aplicación.

CREATE DOMAIN nombre [AS] tipodato   
   [ DEFAULT expresión ]   
   [ [ CONSTRAINT restricción ]   
{ NOT NULL | NULL | CHECK (expresión) } ] 

ALTER DOMAIN nombre { SET DEFAULT expresión | DROP DEFAULT }  
ALTER DOMAIN nombre { SET | DROP } NOT NULL  
ALTER DOMAIN nombre ADD restricción [ NOT VALID ]  
ALTER DOMAIN nombre DROP CONSTRAINT [ IF EXISTS ] restricción  
[ RESTRICT | CASCADE ]  
ALTER DOMAIN nombre RENAME CONSTRAINT restricción TO restricción2  ...

Búsqueda textual

La búsqueda textual en PostgreSQL es un conjunto de funciones que permiten el análisis y la indexación de textos a partir de las raíces de las palabras de este texto, proporcionando los elementos de ordenación en función de la proximidad del resultado con la búsqueda.

Al contrario de lo que sucedía con las expresiones normales o con el operador LIKE, este tipo de búsqueda permite la utilización de índices que mejoran el rendimiento y facilita la búsqueda de las raíces de palabras en función de un idioma, lo que posibilita efectuar búsquedas sobre palabras similares.

Las palabras se convierten en raíces o lexemas, que permiten la cercanía de palabras similares con la misma raíz.

Una búsqueda en un texto es la correspondencia entre un documento de tipo tsvector y una consulta de tipo tsquery. Esta correspondencia es verdadera o falsa y se determina por el operador @@.

Búsqueda en una tabla

La búsqueda en una o varias columnas de tipo text en una tabla pasa por la conversión a un documento tsvector, lo que permite asociarlo a una búsqueda tsquery. Por ejemplo, la siguiente consulta permite buscar la palabra clave ’Linux’ en el campo plandeestudios de la tabla formaciones:

SELECT * FROM formaciones WHERE to_tsvector(plandeestudios) @@  
to_tsquery( 'Linux'); 

Para mejorar...

Extensiones

Las extensiones permiten añadir funcionalidades a PostgreSQL.

Una extensión es un conjunto coherente de funciones, tipos de datos, operadores o cualquier otro objeto útil, que ofrece una nueva funcionalidad a PostgreSQL. Una extensión se puede proporcionar por PostgreSQL o cualquier otro proveedor.

Estas extensiones pueden facilitar nuevos tipos de datos, como ltree o prefix, así como herramientas de administración, como pg_stat_statement o pg_buffercache.

Cuando se instala PostgreSQL mediante un sistema de paquetes, como en las distribuciones GNU/Linux Debian, Ubuntu, Red Hat o CentOS, un paquete específico contiene las extensiones proporcionadas por PostgreSQL: postgresql-contrib-10 para Debian y Ubuntu o postgresql10-contrib para Red Hat y CentOS.

Las extensiones no proporcionadas por PostgreSQL se instalan por el sistema de paquetes. Entonces, cada extensión es el objeto de un paquete específico; por ejemplo, postgresql-10-ip4r para Debian y Ubuntu o ip4r10 para Red Hat o CentOS.

Estos paquetes hacen referencia a una versión principal específica de PostgreSQL. Por una parte, porque la mayor parte de ellas se escriben en lenguaje C y, por lo tanto, se deben compilar en función de la versión elegida y, por otra parte, porque los archivos se deben instalar en la arborescencia de la versión principal de PostgreSQL.

Cuando se instalan los archivos que contienen el código de la extensión, esta extensión no está disponible inmediatamente. Falta crear los objetos en la base de datos deseada para permitir su utilización, por ejemplo en la creación de una tabla para un tipo de datos o en una consulta SELECT para una función.

CREATE EXTENSION [ IF NOT EXISTS ] extensión  
   [ WITH ] [ SCHEMA nombresquema ]  
   [ VERSION versión ] [ FROM antiguaversión ]  
   [ CASCADE ] 

Operadores y funciones

Manipulación de los datos

[ WITH [ RECURSIVE ] consulta_cte [, ...] ]   
INSERT INTO tabla    
   [ ( columna [, ...] ) ]    
   {    
   DEFAULT VALUES |    
        VALUES ( { expresión | DEFAULT } [, ...] ) |    
        consulta    
         }   
   [ ON CONFLICT [ ON CONSTRAINT restricción ]   
     DO NOTHING | DO UPDATE SET { columna = expresión } [, ...] 
[ WHERE condición ] ]   
   [ RETURNING * | expr ] 

INSERT INTO clientes (cl_nombre, cl_direccion) VALUES ('S.T.E.R.E.G',  
'Islas Pitiusas 2, 25290 Las Rozas'); 

INSERT INTO prestaciones VALUES ( 1 , 'nombre' ,   
,  '' , '13/06/2006', '12/06/2006', true ,'');