Una vez conectados a una base de datos, la sentencia SQL create table permite crear las tablas que necesitemos:

El comando drop table permite eliminar tablas:

La tabla recién creada aparece ahora en la lista de tablas de la base de datos en uso:

Podemos consultar su descripción mediante el comando \d tabla:

 

La tabla está lista para insertar en ella algunos registros.

 

Este aspecto se explicará en detalle más adelante.

El número con el que responde el comando insert se refiere al OID del registro insertado.

Las consultas se realizan con la sentencia SQL select. En este caso solicitamos que nos muestre todas las columnas de los registros en la tabla persona:

Las tablas creadas en PostgreSQL incluyen, por defecto, varias columnas ocultas que almacenan información acerca del identificador de transacción en que pueden estar implicadas, la localización física del registro dentro de la tabla (para localizarla muy rápidamente) y, los más importantes, el OID y el TABLE OID. Estas últimas columnas están definidas con un tipo de datos especial llamado  identificador  de  objeto  (OID)  que  se  implementa  como  un  entero positivo de 32 bits. Cuando se inserta un nuevo registro en una tabla se le asigna un número consecutivo como OID, y el TABLEOID de la tabla que le corresponde.

Para observar las columnas ocultas, debemos hacer referencia a ellas específicamente en el comando select:

Ejemplo de la utilización de OID para enlazar dos tablas

Retomamos la tabla persona y construimos una nueva tabla para almacenar los teléfonos.

La tabla teléfono incluye la columna propietario de tipo OID, que almacenará la referencia a los registros de la tabla persona. Agreguemos dos teléfonos a ‘Alejandro Magno’, para ello utilizamos su OID que es 17242:

Las dos tablas están vinculadas por el OID de persona.

La operación que nos permite unir las dos tablas es join, que en este caso une teléfono y persona, utilizando para ello la igualdad de las columnas telefono.propietario y persona.oid:

Los OID de PostgreSQL presentan algunas deficiencias:

•    Todos los OID de una base de datos se generan a partir de una única secuencia centralizada, lo que provoca que en bases de datos con mucha actividad de inserción y eliminación de registros, el contador de 4 bytes se desborde y pueda entregar OID ya entregados. Esto sucede, por supuesto, con bases de datos muy grandes.

•    Las tablas enlazadas mediante OID no tienen ninguna ventaja al utilizar operadores de composición en términos de eficiencia respecto a una clave primaria convencional.

•    Los OID no mejoran el rendimiento. Son, en realidad, una columna con un número entero como valor.

Los desarrolladores de PostgreSQL proponen la siguiente alternativa para usar OID de forma absolutamente segura:

•    Crear una restricción de tabla para que el OID sea único, al menos en cada tabla. El SGBD irá incrementando secuencialmente el OID hasta encontrar uno sin usar.

•    Usar la combinación OID - TABLEOID si se necesita un identificador único para un registro válido en toda la base de datos.

Por los motivos anteriores, no es recomendable el uso de OID hasta que nuevas versiones de PostgreSQL los corrijan. En caso de usarlos, conviene seguir las recomendaciones anteriores.

 

Es posible crear tablas que no incluyan la columna OID mediante la siguiente notación:

 

PostgreSQL ofrece como característica particular la herencia entre tablas, que permite definir una tabla que herede de otra previamente definida, según la definición de herencia que hemos visto en capítulos anteriores.

Retomemos la tabla persona definida como sigue:

A partir de  esta  definición, creamos la tabla estudiante  como derivada de persona:

 

En la tabla estudiante se definen las columnas carrera, grupo y grado, pero al so- licitar información de la estructura de la tabla observamos que también inclu- ye las columnas definidas en persona:

En este caso, a la tabla persona la llamamos padre y a la tabla estudiante, hija.

Cada registro de la tabla estudiante contiene 5 valores porque tiene 5 columnas:

La consulta del contenido de la tabla estudiante mostrará, por supuesto, un solo registro. Es decir, no se heredan los datos, únicamente los campos (atributos) del objeto:

Además, la consulta de la tabla persona mostrará un nuevo registro:

 

El último registro mostrado es el que fue insertado en tabla estudiante, sin embargo la herencia define una relación conceptual en la que un estudiante es-una persona. Por lo tanto, al consultar cuántas personas están registradas en la base de datos, se incluye en el resultado a todos los estudiantes. Para consultar sólo a las personas que no son estudiantes, podemos utilizar el modificador ONLY:

Como es lógico, al borrar la fila del nuevo estudiante que hemos insertado, se borra de las dos tablas. Tanto si lo borramos desde la tabla persona, como si lo borramos desde la tabla estudiante.

Los OID permiten que se diferencien los registros de todas las tablas, aunque sean heredadas: nuestro estudiante tendrá el mismo OID en las dos tablas, ya que se trata de única instancia de la clase estudiante:

 

Dado que no se recomienda el uso de OID en bases muy grandes, y debe incluirse explícitamente en las consultas para examinar su valor, es conveniente utilizar una secuencia compartida para padres y todos sus descendientes si se requiere un identificador.

En PostgreSQL, una alternativa para no utilizar los OID es crear una columna de tipo serial en la tabla padre, así será heredada en la hija. El tipo serial define una secuencia de valores que se irá incrementando de forma automática, y por lo tanto constituye una buena forma de crear claves primarias, al igual que el tipo AUTO_INCREMENT en MySQL.

La columna id se define como un entero y se incrementará  utilizando la función nextval() tal como nos indica la información de la columna:

 

Al definir un tipo serial, hemos creado implícitamente una secuencia independiente de la tabla. Podemos consultar las secuencias de nuestra base de datos mediante el comando ‘\ds’:

Creamos nuevamente la tabla estudiante heredando de persona:

El estudiante heredará la columna id y se incrementará utilizando la misma secuencia:

Insertaremos en la tabla algunos registros de ejemplo, omitiendo el valor para la columna id:

 

La tabla estudiante contendrá un  solo registro, pero su identificador es el número 3.

Todos los registros de persona siguen una misma secuencia sin importar si son padres o hijos:

La herencia es útil para definir tablas que conceptualmente mantienen elementos en común, pero también requieren  datos que los hacen diferentes. Uno de los elementos que conviene definir como comunes son los identificadores de registro.

Como ya sabemos, las restricciones permiten especificar condiciones que deberán cumplir tablas o columnas para mantener la integridad de sus datos. Algunas de las restricciones vendrán impuestas por el modelo concreto que se esté implementando, mientras que otras tendrán su origen en las reglas de negocio del cliente, los valores que pueden tomar algunos campos, etc.

 

Ejemplo

Una columna definida como integer no puede contener cadenas de         caracteres.

Los valores que puede contener una columna están restringidos en primer lugar por el tipo de datos. Ésta no es la única restricción que se puede definir para los valores en una columna, PostgreSQL ofrece las restricciones siguientes:

•    null y not null. En múltiples ocasiones el valor de una columna es desconocido, no es aplicable o no existe. En estos casos, los valores cero, cadena vacía o falso son inadecuados, por lo que utilizamos null para especificar la ausencia de valor. Al definir una tabla podemos indicar qué columnas podrán contener valores nulos y cuáles no.

 

El nombre de una persona no puede ser nulo, y es posible que la persona no tenga trabajo. También es posible que no tenga correo, al no especificar una restricción not null, se asume que la columna puede contener valores nulos.

 

•    unique. Esta restricción se utiliza cuando no queremos que los valores contenidos en una columna puedan duplicarse.

 

cónyuge no puede contener valores duplicados, no permitiremos que dos personas tengan simultáneamente el mismo cónyuge.

 

•    primary key. Esta restricción especifica la columna o columnas que elegimos como clave primaria. Puede haber múltiples columnas unique, pero sólo debe haber una clave primaria. Los valores que son únicos pueden servir para identificar una fila de la tabla de forma unívoca, por lo que se les denomina claves candidatas.

 

create table Persona (
nss varchar(10) primary key, conyuge varchar(40) unique,
);

 

Al definir una columna como primary key, se define implícitamente con unique. El nss (número de la seguridad social) no sólo es único, sino que lo utilizamos para identificar a las personas.

 

•    references y foreign key. En el modelo relacional, establecemos las relaciones entre entidades mediante la inclusión de claves foráneas en otras relaciones. PostgreSQL y SQL ofrecen mecanismos para expresar y mantener esta  integridad  referencial.  En  el  siguiente  ejemplo,  las  Mascotas  tienen como dueño a una Persona:

 

Una referencia por defecto es a una clave primaria, por lo que dueño se refiere implícitamente al nss de Persona. Cuando se capturen los datos de una nueva mascota, PostgreSQL verificará que el valor de dueño haga referencia a un nss que exista en Persona, en caso contrario emitirá un mensaje de error. En otras palabras, no se permite asignar a una mascota un dueño que no exista.

También es posible especificar a qué columna de la tabla hace referencia:

o su equivalente:

Podría darse el caso de que la clave primaria de la tabla referenciada tuviera más de una columna, en ese caso, la clave foránea también tendría que estar formada por el mismo número de columnas:

Si no se especifica otra acción, por omisión la persona que tenga una mascota no puede ser eliminada, porque la mascota se quedaría sin dueño. Para poder eliminar una persona, antes se deben eliminar las mascotas que tenga. Este comportamiento no parece ser el más adecuado para el caso.

Para modificar este comportamiento disponemos de las reglas de integridad referencial del lenguaje SQL, que PostgreSQL también soporta. En el siguiente ejemplo se permite que al eliminar una persona, las mascotas simplemente se queden sin dueño.

 

En cláusula on delete se pueden especificar las siguientes acciones:

–    set null. La referencia toma el valor NULL: si se elimina Persona su Mascota se quedará sin dueño.

–    set default. La referencia toma el valor por omisión.

–    cascade. La acción se efectúa en cascada: si se elimina Persona automáticamente se elimina su Mascota.

–    restrict. No permite el borrado del registro: no se puede eliminar una Persona que tenga Mascota. Ésta es la acción que se toma por omisión.

Si se modifica la clave primaria de la tabla referenciada, se dispone de las mismas acciones que en el caso anterior, que especificaremos con la cláusula ON UPDATE.

•    check. Esta restricción realiza la evaluación previa de una expresión lógica cuando  se  intenta  realizar  una  asignación.  Si  el  resultado  es  verdadero, acepta el valor para la columna, en caso contrario, emitirá un mensaje de error y rechazará el valor.

Comparación                                         de expresiones regulares

El operador ~ realiza compara- ciones de cadenas con expre- siones regulares. Las expresiones regulares son patrones de búsqueda muy flexibles desarrollados en el mundo Unix.

 

Se han restringido los valores que se aceptarán en la columna de la manera siguiente.

•  Edad debe estar entre 11 y 79 años.
•  Ciudad no debe una cadena vacía.
•  Correo debe tener una arroba.

 

Cualquiera de esas restricciones puede tener nombre, de manera que se facilita la  referencia  a  las  restricciones  específicas  para  borrarlas,  modificarlas,  etc. pues puede hacerse por nombres. Para dar nombre a una restricción, utilizamos la sintaxis siguiente:

PostgreSQL crea índices para las llaves primarias de todas las tablas. Cuando se necesite crear índices adicionales, utilizaremos la expresión del ejemplo siguiente:

 

Como ya sabemos, el cliente psql ofrece varias alternativas para obtener información sobre la estructura de nuestra base de datos. En la siguiente tabla se muestran algunos comandos de mucha utilidad.

Comando	Descripción
\1	Lista las bases de datos
\d	Describe las tablas de la base de datos en uso
\ds	Lista las secuencias
\di	Lista los índices
\dv	Lista las vistas
\dp \z	Lista los privilegios sobre las tablas
\da	Lista las funciones de agregados
\df	Lista las funciones
\g archivo	Ejecuta los comandos de archivo
\H	Cambia el modo de salida HTML
\! comando	Ejecuta un comando del sistema operativo

Para obtener la lista de tablas de la base de datos demo hacemos lo siguiente:

 

 

La estructura de la tabla productos se solicita de la siguiente manera.

 

 

En el ejemplo anterior podemos observar que la columna clave contiene dos modificadores:

•    El primero especifica que no pueden asignarse valores nulos.

•    El segundo especifica el valor por omisión que deberá asignarse a la columna.
 

* Una secuencia es un nombre          especial que permite la producción   de series numéricas.

En este caso, el valor será automáticamente calculado por la función nextval(), que toma como argumento la secuencia* productos_clave_seq.

El siguiente comando muestra las secuencias creadas en una base de datos:

Las  secuencias  se  crean  automáticamente  cuando  se  declaran  columnas  de tipo serial.

 

En la estructura de la tabla productos encontramos también una clave primaria. PostgreSQL generará siempre un índice para cada tabla utilizando la clave primaria. La lista de los índices de la base de datos se obtiene de la siguiente forma:

El  conjunto  de  comandos  proporcionados  por  psql  que  hemos  presentado permite obtener información sobre la estructura de nuestra base de datos de una manera directa y sencilla y, también, es útil para explorar bases de datos que no conozcamos.

Para modificar la estructura de una tabla una vez construida, disponemos de la sentencia SQL alter table.

Mediante esta sentencia, podemos llevar a cabo las operaciones siguientes:

•    Agregar una columna.

•    Eliminar una columna.

•    Fijar el valor por omisión de una columna.

•    Eliminar el valor por omisión de una columna.

•    Renombrar una columna.

•    Renombrar una tabla.