Creación de Tablas y Tipos de Datos en PostgreSQL

Introducción

En este laboratorio, exploraremos la creación de tablas y los tipos de datos en PostgreSQL. El objetivo es comprender los tipos de datos fundamentales como los enteros, el texto, las fechas y los booleanos, que son cruciales para definir las estructuras de las tablas y garantizar la integridad de los datos.

Nos conectaremos a la base de datos PostgreSQL utilizando psql, crearemos tablas con claves primarias utilizando SERIAL, y añadiremos restricciones básicas como NOT NULL y UNIQUE. Luego inspeccionaremos la estructura de la tabla e insertaremos datos para demostrar el uso de diferentes tipos de datos como INTEGER, SMALLINT, TEXT, VARCHAR(n), y CHAR(n).

Skills Graph

Explorar los Tipos de Datos de PostgreSQL

En este paso, exploraremos algunos de los tipos de datos fundamentales disponibles en PostgreSQL. Comprender los tipos de datos es crucial para definir las estructuras de las tablas y garantizar la integridad de los datos. Cubriremos tipos comunes como enteros, texto, fechas y booleanos.

Primero, conectémonos a la base de datos PostgreSQL. Abre una terminal y usa el comando psql para conectarte a la base de datos postgres como el usuario postgres. Dado que el usuario postgres es el superusuario predeterminado, es posible que necesites usar sudo para cambiar a ese usuario primero.

sudo -u postgres psql

Ahora deberías estar en la terminal interactiva de PostgreSQL. Verás un prompt como postgres=#.

Ahora, exploremos algunos tipos de datos básicos.

1. Tipos de Enteros (Integer Types):

PostgreSQL ofrece varios tipos de enteros con diferentes rangos. Los más comunes son INTEGER (o INT) y SMALLINT.

• INTEGER: Una opción típica para la mayoría de los valores enteros.
• SMALLINT: Se utiliza para valores enteros más pequeños para ahorrar espacio.

Creemos una tabla simple para demostrar estos tipos:

CREATE TABLE integer_example (
    id SERIAL PRIMARY KEY,
    quantity INTEGER,
    small_quantity SMALLINT
);

Aquí, SERIAL es un tipo especial que genera automáticamente una secuencia de enteros, lo que lo hace adecuado para claves primarias (primary keys).

Ahora, inserta algunos datos:

INSERT INTO integer_example (quantity, small_quantity) VALUES (100, 10);
INSERT INTO integer_example (quantity, small_quantity) VALUES (2000000, 32767);

Puedes ver los datos usando:

SELECT * FROM integer_example;

Salida (Output):

 id | quantity | small_quantity
----+----------+----------------
  1 |      100 |             10
  2 |  2000000 |          32767
(2 rows)

2. Tipos de Texto (Text Types):

PostgreSQL proporciona TEXT, VARCHAR(n), y CHAR(n) para almacenar texto.

• TEXT: Almacena cadenas de longitud variable de longitud ilimitada.
• VARCHAR(n): Almacena cadenas de longitud variable con una longitud máxima de n.
• CHAR(n): Almacena cadenas de longitud fija de longitud n. Si la cadena es más corta, se rellena con espacios.

Creemos otra tabla:

CREATE TABLE text_example (
    id SERIAL PRIMARY KEY,
    name TEXT,
    short_name VARCHAR(50),
    code CHAR(5)
);

Inserta algunos datos:

INSERT INTO text_example (name, short_name, code) VALUES ('PostgreSQL Database', 'PostgreSQL', 'PG001');
INSERT INTO text_example (name, short_name, code) VALUES ('Another Database', 'Another', 'AD002');

Ver los datos:

SELECT * FROM text_example;

Salida (Output):

 id |        name        | short_name | code
----+--------------------+------------+-------
  1 | PostgreSQL Database | PostgreSQL | PG001
  2 | Another Database   | Another    | AD002
(2 rows)

3. Tipos de Fecha y Hora (Date and Time Types):

PostgreSQL ofrece DATE, TIME, TIMESTAMP, y TIMESTAMPTZ para manejar valores de fecha y hora.

• DATE: Almacena solo la fecha (año, mes, día).
• TIME: Almacena solo la hora (hora, minuto, segundo).
• TIMESTAMP: Almacena tanto la fecha como la hora sin información de la zona horaria.
• TIMESTAMPTZ: Almacena tanto la fecha como la hora con información de la zona horaria.

Crea una tabla:

CREATE TABLE datetime_example (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    event_timestamp TIMESTAMP,
    event_timestamptz TIMESTAMPTZ
);

Inserta datos:

INSERT INTO datetime_example (event_date, event_time, event_timestamp, event_timestamptz)
VALUES ('2023-10-27', '10:30:00', '2023-10-27 10:30:00', '2023-10-27 10:30:00+00');

Ver los datos:

SELECT * FROM datetime_example;

Salida (Output):

 id | event_date | event_time |   event_timestamp   |    event_timestamptz
----+------------+------------+---------------------+----------------------------
  1 | 2023-10-27 | 10:30:00   | 2023-10-27 10:30:00 | 2023-10-27 10:30:00+00
(1 row)

4. Tipo Booleano (Boolean Type):

El tipo BOOLEAN almacena valores verdadero/falso.

Crea una tabla:

CREATE TABLE boolean_example (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN
);

Inserta datos:

INSERT INTO boolean_example (is_active) VALUES (TRUE);
INSERT INTO boolean_example (is_active) VALUES (FALSE);

Ver los datos:

SELECT * FROM boolean_example;

Salida (Output):

 id | is_active
----+-----------
  1 | t
  2 | f
(2 rows)

Finalmente, sal de la terminal psql:

\q

Ahora has explorado algunos de los tipos de datos fundamentales en PostgreSQL. Estos tipos de datos forman los bloques de construcción para crear esquemas de bases de datos robustos y bien definidos.

Crear Tablas con Claves Primarias (Primary Keys)

En este paso, aprenderemos cómo crear tablas con claves primarias en PostgreSQL. Una clave primaria es una columna o un conjunto de columnas que identifica de forma única cada fila en una tabla. Impone la unicidad y sirve como un elemento crucial para la integridad de los datos y las relaciones entre las tablas.

Primero, conectémonos a la base de datos PostgreSQL. Abre una terminal y usa el comando psql para conectarte a la base de datos postgres como el usuario postgres.

sudo -u postgres psql

Ahora deberías estar en la terminal interactiva de PostgreSQL.

Comprendiendo las Claves Primarias (Understanding Primary Keys)

Una clave primaria tiene las siguientes características:

• Debe contener valores únicos.
• No puede contener valores NULL.
• Una tabla puede tener solo una clave primaria.

Creando una Tabla con una Clave Primaria (Creating a Table with a Primary Key)

Hay dos formas comunes de definir una clave primaria al crear una tabla:

1. Usando la restricción PRIMARY KEY dentro de la definición de la columna:

   CREATE TABLE products (
       product_id SERIAL PRIMARY KEY,
       product_name VARCHAR(100),
       price DECIMAL(10, 2)
   );

   En este ejemplo, product_id se define como la clave primaria utilizando la restricción PRIMARY KEY. La palabra clave SERIAL crea automáticamente una secuencia para generar valores enteros únicos para el product_id.

2. Usando la restricción PRIMARY KEY por separado:

   CREATE TABLE customers (
       customer_id INT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       PRIMARY KEY (customer_id)
   );

   Aquí, la restricción PRIMARY KEY se define por separado, especificando que la columna customer_id es la clave primaria.

Ejemplo: Creando una tabla users con una clave primaria

Creemos una tabla users con una clave primaria usando el tipo SERIAL para la generación automática de ID:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    registration_date DATE
);

En esta tabla:

• user_id es la clave primaria, generada automáticamente usando SERIAL.
• username es un nombre de usuario único para cada usuario.
• email es la dirección de correo electrónico del usuario.
• registration_date es la fecha en que se registró el usuario.

Ahora, insertemos algunos datos en la tabla users:

INSERT INTO users (username, email, registration_date) VALUES ('john_doe', '[email protected]', '2023-10-26');
INSERT INTO users (username, email, registration_date) VALUES ('jane_smith', '[email protected]', '2023-10-27');

Puedes ver los datos usando:

SELECT * FROM users;

Salida (Output):

 user_id |  username  |        email        | registration_date
---------+------------+---------------------+---------------------
       1 | john_doe   | [email protected] | 2023-10-26
       2 | jane_smith | [email protected] | 2023-10-27
(2 rows)

Intentando insertar una clave primaria duplicada (Trying to insert a duplicate primary key)

Si intentas insertar una fila con una clave primaria duplicada, PostgreSQL generará un error:

INSERT INTO users (user_id, username, email, registration_date) VALUES (1, 'duplicate_user', '[email protected]', '2023-10-28');

Salida (Output):

ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (user_id)=(1) already exists.

Esto demuestra la restricción de clave primaria en acción, evitando valores duplicados.

Finalmente, sal de la terminal psql:

\q

Ahora has creado con éxito una tabla con una clave primaria y has observado cómo impone la unicidad. Este es un concepto fundamental en el diseño de bases de datos.

Agregar Restricciones Básicas (NOT NULL, UNIQUE)

En este paso, aprenderemos cómo agregar restricciones básicas a las tablas en PostgreSQL. Las restricciones son reglas que imponen la integridad y la consistencia de los datos. Nos centraremos en dos restricciones fundamentales: NOT NULL y UNIQUE.

Primero, conectémonos a la base de datos PostgreSQL. Abre una terminal y usa el comando psql para conectarte a la base de datos postgres como el usuario postgres.

sudo -u postgres psql

Ahora deberías estar en la terminal interactiva de PostgreSQL.

Comprendiendo las Restricciones (Understanding Constraints)

Las restricciones se utilizan para limitar el tipo de datos que se pueden insertar en una tabla. Esto garantiza la precisión y la fiabilidad de los datos en la base de datos.

1. Restricción NOT NULL

La restricción NOT NULL garantiza que una columna no pueda contener valores NULL. Esto es útil cuando una información particular es esencial para cada fila de la tabla.

2. Restricción UNIQUE

La restricción UNIQUE garantiza que todos los valores en una columna sean distintos. Esto es útil para las columnas que deben tener identificadores o valores únicos, como nombres de usuario o direcciones de correo electrónico (además de la clave primaria).

Agregando Restricciones Durante la Creación de la Tabla (Adding Constraints During Table Creation)

Puedes agregar restricciones cuando creas una tabla. Creemos una tabla llamada employees con restricciones NOT NULL y UNIQUE:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

En esta tabla:

• employee_id es la clave primaria.
• first_name y last_name se declaran como NOT NULL, lo que significa que deben tener un valor para cada empleado.
• email se declara como UNIQUE, lo que garantiza que cada empleado tenga una dirección de correo electrónico única.

Ahora, intentemos insertar algunos datos que violen estas restricciones.

Intentando insertar un valor NULL en una columna NOT NULL:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES (NULL, 'Smith', '[email protected]', '2023-10-27');

Salida (Output):

ERROR:  null value in column "first_name" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (1, null, Smith, [email protected], 2023-10-27).

Este error indica que no puedes insertar un valor NULL en la columna first_name debido a la restricción NOT NULL.

Intentando insertar un valor duplicado en una columna UNIQUE:

Primero, inserta una fila válida:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Smith', '[email protected]', '2023-10-27');

Ahora, intenta insertar otra fila con el mismo correo electrónico:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', '[email protected]', '2023-10-28');

Salida (Output):

ERROR:  duplicate key value violates unique constraint "employees_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

Este error indica que no puedes insertar una dirección de correo electrónico duplicada debido a la restricción UNIQUE.

Insertando datos válidos:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Doe', '[email protected]', '2023-10-28');

Ver los datos:

SELECT * FROM employees;

Salida (Output):

 employee_id | first_name | last_name |        email        | hire_date
-------------+------------+-----------+---------------------+------------
           1 | John       | Smith     | [email protected] | 2023-10-27
           2 | Jane       | Doe       | [email protected]  | 2023-10-28
(2 rows)

Finalmente, sal de la terminal psql:

\q

Ahora has creado con éxito una tabla con restricciones NOT NULL y UNIQUE y has observado cómo imponen la integridad de los datos.

Inspeccionar la Estructura de la Tabla (Inspect Table Structure)

En este paso, aprenderemos cómo inspeccionar la estructura de las tablas en PostgreSQL. Comprender la estructura de una tabla, incluidos los nombres de las columnas, los tipos de datos, las restricciones y los índices, es esencial para consultar y manipular los datos de manera efectiva.

Primero, conectémonos a la base de datos PostgreSQL. Abre una terminal y usa el comando psql para conectarte a la base de datos postgres como el usuario postgres.

sudo -u postgres psql

Ahora deberías estar en la terminal interactiva de PostgreSQL.

El comando \d

La herramienta principal para inspeccionar la estructura de la tabla en psql es el comando \d (describe - describir). Este comando proporciona información detallada sobre una tabla, incluyendo:

• Nombres de las columnas y tipos de datos
• Restricciones (claves primarias, restricciones únicas, restricciones not-null)
• Índices

Inspeccionando la tabla employees

Inspeccionemos la estructura de la tabla employees que creamos en el paso anterior:

\d employees

Salida (Output):

                                           Table "public.employees"
   Column    |          Type          | Collation | Nullable |                    Default
-------------+------------------------+-----------+----------+------------------------------------------------
 employee_id | integer                |           | not null | nextval('employees_employee_id_seq'::regclass)
 first_name  | character varying(50)  |           | not null |
 last_name   | character varying(50)  |           | not null |
 email       | character varying(100) |           |          |
 hire_date   | date                   |           |          |
Indexes:
    "employees_pkey" PRIMARY KEY, btree (employee_id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)

La salida proporciona la siguiente información:

• Table "public.employees": Indica el nombre de la tabla y el esquema (schema).
• Column: Enumera los nombres de las columnas (employee_id, first_name, last_name, email, hire_date).
• Type: Muestra el tipo de datos de cada columna (integer, character varying, date).
• Nullable: Indica si una columna puede contener valores NULL (not null o en blanco).
• Default: Muestra el valor predeterminado para una columna (si existe).
• Indexes: Enumera los índices definidos en la tabla, incluida la clave primaria (employees_pkey) y la restricción única en la columna email (employees_email_key).

Inspeccionando otras tablas

Puedes usar el comando \d para inspeccionar cualquier tabla en la base de datos. Por ejemplo, para inspeccionar la tabla users creada en el paso 2:

\d users

Salida (Output):

                                            Table "public.users"
      Column       |          Type          | Collation | Nullable |                Default
-------------------+------------------------+-----------+----------+----------------------------------------
 user_id           | integer                |           | not null | nextval('users_user_id_seq'::regclass)
 username          | character varying(50)  |           |          |
 email             | character varying(100) |           |          |
 registration_date | date                   |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)

Listando todas las tablas

Para listar todas las tablas en la base de datos actual, puedes usar el comando \dt:

\dt

Salida (Output) (variará según las tablas que hayas creado):

              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | boolean_example  | table | postgres
 public | customers        | table | postgres
 public | datetime_example | table | postgres
 public | employees        | table | postgres
 public | integer_example  | table | postgres
 public | products         | table | postgres
 public | text_example     | table | postgres
 public | users            | table | postgres
(8 rows)

Finalmente, sal de la terminal psql:

\q

Ahora has aprendido cómo inspeccionar la estructura de las tablas en PostgreSQL usando los comandos \d y \dt. Esta es una habilidad fundamental para comprender y trabajar con bases de datos.

Resumen (Summary)

En este laboratorio, exploramos los tipos de datos fundamentales de PostgreSQL, centrándonos en los enteros y el texto. Aprendimos sobre INTEGER y SMALLINT para almacenar valores enteros, comprendiendo sus diferentes rangos y casos de uso. También examinamos TEXT, VARCHAR(n) y CHAR(n) para manejar datos de texto, observando las distinciones entre cadenas de longitud variable y longitud fija.

Además, practicamos la creación de tablas utilizando estos tipos de datos, incluido el uso de SERIAL para generar automáticamente secuencias de clave primaria. Insertamos datos de muestra en las tablas y verificamos los datos utilizando sentencias SELECT, solidificando nuestra comprensión de cómo se comportan estos tipos de datos en un contexto práctico de base de datos.