PDF de programación - Bases de datos en PostgreSQL

Bases de datos
en PostgreSQL

Marc Gibert Ginestà
Oscar Pérez Mora

P06/M2109/02152

© FUOC • P06/M2109/02152

Índice

Bases de datos en PostgreSQL

Introducción .............................................................................................. 5

Objetivos ..................................................................................................... 6

1. Características de PostgreSQL .......................................................... 7
1.1. Breve historia .................................................................................... 7
1.2. Prestaciones ...................................................................................... 7
1.3. Limitaciones ..................................................................................... 8

2. Introducción a la orientación a objetos ....................................... 9
2.1. El modelo orientado a objetos .......................................................... 9
2.2. Objetos: clase frente a instancia ....................................................... 10
2.3. Propiedades: atributo frente a operación ......................................... 11
2.4. Encapsulamiento: implementación frente a interfaz ....................... 11
2.4.1. Atributo frente a variable de instancia ................................... 13
2.5. Herencia: jerarquía de clases ............................................................ 14
2.5.1. Tipo y clase ............................................................................. 17
2.6. Agregación: jerarquía de objetos ...................................................... 17
2.7. Persistencia ....................................................................................... 18
2.8. PostgreSQL y la orientación a objetos .............................................. 18

3. Acceso a un servidor PostgreSQL .................................................... 20
3.1. La conexión con el servidor ............................................................. 20
3.2. El cliente psql .................................................................................... 20
3.3. Introducción de sentencias .............................................................. 21
3.3.1. Expresiones y variables .......................................................... 22
3.4. Proceso por lotes y formatos de salida ............................................. 23
3.5. Usar bases de datos ........................................................................... 27

4. Creación y manipulación de tablas ................................................ 29
4.1. Creación de tablas ............................................................................ 29
4.2. Herencia ............................................................................................ 32
4.3. Herencia y OID ................................................................................. 34
4.4. Restricciones ..................................................................................... 37
4.4.1. Restricciones de tabla ............................................................. 40
4.5. Indexación ........................................................................................ 42
4.6. Consulta de información de bases de datos y tablas ........................ 42
4.7. Tipos de datos ................................................................................... 44
4.7.1. Tipos lógicos ........................................................................... 44
4.7.2. Tipos numéricos ..................................................................... 44
4.7.3. Operadores numéricos ........................................................... 45

© FUOC • P06/M2109/02152

Bases de datos en PostgreSQL

4.7.4. Tipos de caracteres ................................................................. 46
4.7.5. Operadores ............................................................................. 47
4.7.6. Fechas y horas ........................................................................ 48
4.7.7. Arrays ...................................................................................... 48
4.7.8. BLOB ....................................................................................... 50
4.8. Modificación de la estructura de una tabla ...................................... 53

5. Manipulación de datos ...................................................................... 54
5.1. Consultas .......................................................................................... 54
5.2. Actualizaciones e inserciones ........................................................... 55
5.3. Transacciones .................................................................................... 56

6. Funciones y disparadores .................................................................. 57
6.1. Primer programa ............................................................................... 57
6.2. Variables ........................................................................................... 58
6.3. Sentencias ......................................................................................... 58
6.4. Disparadores ..................................................................................... 61

7. Administración de PostgreSQL ........................................................ 63
7.1. Instalación ........................................................................................ 63
7.1.1. Internacionalización .............................................................. 64
7.2. Arquitectura de PostgreSQL .............................................................. 65
7.3. El administrador de postgres ............................................................ 66
7.3.1. Privilegios ............................................................................... 67
7.4. Creación de tipos de datos ............................................................... 68
7.5. Plantilla de creación de bases de datos ............................................. 70
7.6. Copias de seguridad .......................................................................... 71
7.7. Mantenimiento rutinario de la base de datos .................................. 72
7.7.1. vacuum .................................................................................... 72
7.7.2. Reindexación .......................................................................... 73
7.7.3. Ficheros de registro ................................................................ 73

8. Cliente gráfico: pgAdmin3 ............................................................... 74

Resumen ...................................................................................................... 76

Bibliografía ................................................................................................ 77

© FUOC • P06/M2109/02152

Introducción

5

Bases de datos en PostgreSQL

PostgreSQL es un gestor de bases de datos orientadas a objetos (SGBDOO o
ORDBMS en sus siglas en inglés) muy conocido y usado en entornos de software
libre porque cumple los estándares SQL92 y SQL99, y también por el conjunto
de funcionalidades avanzadas que soporta, lo que lo sitúa al mismo o a un mejor
nivel que muchos SGBD comerciales.

El origen de PostgreSQL se sitúa en el gestor de bases de datos POSTGRES desarro-
llado en la Universidad de Berkeley y que se abandonó en favor de PostgreSQL a
partir de 1994. Ya entonces, contaba con prestaciones que lo hacían único en el
mercado y que otros gestores de bases de datos comerciales han ido añadiendo
durante este tiempo.

PostgreSQL se distribuye bajo licencia BSD, lo que permite su uso, redistribución,
modificación con la única restricción de mantener el copyright del software a sus
autores, en concreto el PostgreSQL Global Development Group y la Universidad
de California.

PostgreSQL puede funcionar en múltiples plataformas (en general, en todas las
modernas basadas en Unix) y, a partir de la próxima versión 8.0 (actualmente
en su segunda beta), también en Windows de forma nativa. Para las versiones
anteriores existen versiones binarias para este sistema operativo, pero no tie-
nen respaldo oficial.

Para el seguimiento de los ejemplos y la realización de las actividades, es im-
prescindible disponer de los datos de acceso del usuario administrador del ges-
tor de bases de datos. Aunque en algunos de ellos los privilegios necesarios
serán menores, para los capítulos que tratan la administración del SGBDOO
será imprescindible disponer de las credenciales de administrador.

Las sentencias o comandos escritos por el usuario estarán en fuente monoes-
paciada, y las palabras que tienen un significado especial en PostgreSQL esta-
rán en negrita. Es importante hacer notar que estas últimas no siempre son
palabras reservadas, sino comandos o sentencias de psql (el cliente interactivo
de PostgreSQL).

La versión de PostgreSQL que se ha utilizado durante la redacción de este ma-
terial, y en los ejemplos, es la 7.4, la última versión estable en ese momento,
aunque no habrá ningún problema en ejecutarlos en versiones anteriores,
hasta la 7.0.

© FUOC • P06/M2109/02152

6

Bases de datos en PostgreSQL

Objetivos

El objetivo principal de esta unidad es conocer el gestor de bases de datos relacio-
nales con soporte para objetos PostgreSQL, y comentar tanto sus características
comunes a otros gestores de bases de datos, como las que lo distinguen de sus
competidores de código abierto.

Además, se ofrece la oportunidad de aplicar los conocimientos aprendidos en
el módulo referido a SQL sobre un sistema gestor de base de datos real y exa-
minar las diferencias entre el estándar y la implementación que hace de él el
SGBD.

Por último, se presentan las tareas de administración del SGBD más habituales
que un usuario debe llevar a cabo como administrador de Post-greSQL.

© FUOC • P06/M2109