PostgreSQL – Creando bases de datos, tablas y constraints

Saber crear bases de datos es el primer paso para estructurar un esquema de tablas y demás objetos de esquema.

Para este post utilizaremos la herramienta psql, por lo que todos los comandos y querys se presentarán como texto.

Para empezar, nos logeamos como el usuario postgres y accedemos a la herramienta psql.

Nota: los siguientes ejemplos fueron realizados en Ubuntu 16.04

$ sudo su - postgres

$ psql

Ya dentro de la herramienta, podemos proceder a ejecutar nuestros comandos SQL. Pero antes, veamos cómo es la sintaxis para la creación de bases de datos.

CREATE DATABASE name
WITH OWNER user_name
TEMPLATE template
ENCODING encoding
LC_COLLATE lc_collate
LC_CTYPE lc_ctype
TABLESPACE tablespace
CONNECTION LIMIT connlimit;

Veamos por parte lo anterior.

El primer parámetro name es el nombre que queremos darle a nuestra nueva base de datos.

user_name  indica el rol o usuario a quien estamos haciendo dueño de la base de datos.

template es la plantilla que queremos utilizar como modelo para nuestra base de datos. Por ejemplo, si al momento de crear nuestra base de datos, indicamos que utilice el template1, y éste último contiene una tabla llamada usuarios y otra productos, nuestra nueva base de datos heredará dichas tablas.

encoding es el formato de codificación de caractéres.

lc_collate determina el orden en que los strings serán organizados, por ejemplo, con un query que incluya un ORDER BY.

lc_ctype establece una serie de características relacionadas a la localización.

tablespace es la ubicación en el sistema de archivos en la cual el DBA quiere almacenar los objetos.

connection limit es el límite de conexiones concurrentes. El valor por default es -1, el cual indica que es ilimitado.

Teniendo una idea básica de la sintaxis en la creación de una base de datos, procederemos a crear una base de datos llamada my_db, y el dueño será el usuario jose. Los demás parámetros los dejaremos por default.

# CREATE DATABASE my_db
# WITH OWNER jose;

Nota:

• El punto y coma al final de la sentencia indica al intérprete que se ha terminado de escribir el comando.
• El simbolo # indica que estamos ejecutando el comando como el usuario administrador o root de PostgreSQL.

Habiendo ya creado nuestra base de datos, podemos proceder a crear nuestra primera tabla.

A continuación la sintaxis básica para la creación de una tabla.

CREATE TABLE table_name (
column_name TYPE column_constraint,
table_constraint
) INHERITS existing_table_name;

En la sentencia anterior podemos ver lo siguiente.

table_name determina el nombre de la tabla a crear.

column_name es el nombre de una de las columnas, seguido por el tipo de dato de esta columna.

column_constraint indica el constraint que queramos aplicar a la columna.

table_constraint aquí podemos definir un constraint para la tabla.

Por último, existing_table_name es el nombre de la tabla que queremos heredar.

Si decidimos heredar una tabla existente, la nueva tabla copiará la estructura de columnas y constraints de la tabla heredada.

Procedamos a crear nuestra primera tabla, utilizando la base de datos creada anteriormente.

Lo primero es acceder a nuestra base de datos utilizando el usuario jose.

$ psql my_db

Dentro de la herramienta, procedemos a crear la estructura de nuestra tabla.

my_db=> CREATE TABLE contacts (
my_db(> id INTEGER CONSTRAINT pkey PRIMARY KEY,
my_db(> name VARCHAR(100),
my_db(> vat VARCHAR(100) UNIQUE,
my_db(> phone VARCHAR(18),
my_db(> email VARCHAR(100),
my_db(> genre BOOLEAN);

Con la creación de esta tabla hemos utilizado dos puntos claves en una base de datos: los tipos de datos y los constraints. Los primeros determinan qué tipo de valores o datos serán almacenados en la columna y lo segundo es, podríamos decir, una funcionalidad que promueve la integridad referencial y de los datos.

Nota:

• my_db=> es el prompt de la base de datos a la que hemos accedido con la herramienta psql.

En el próximo post veremos a mayor profundidad la creación de tablas, constraints y tipos de datos.

Lo dejaremos aquí por el momento. Recuerden dejar sus preguntas, sugerencias y aportes en los comentarios.

Nos vemos en el próximo post.