PostgreSQL 17 · bases de datos de cero a experto

Antes del motor, el problema. Guardar datos "en cualquier lado" funciona hasta que deja de funcionar: duplicados, datos perdidos, respuestas lentas y nadie sabe quién cambió qué. Este capítulo nombra ese dolor y presenta al responsable de resolverlo.

• Reconocer los 4 dolores de guardar datos sin un motor.
• Saber qué aporta exactamente un sistema gestor de base de datos.
• Distinguir base de datos, motor y modelo.
• Presentar el dominio del curso: una clínica real.

El dolor de la hoja de cálculo

Imagina la agenda de la clínica en un archivo compartido: pacientes en una hoja, citas en otra, pagos en una tercera. Funciona... hasta el primer lunes ocupado:

Qué aporta el motor (SGBD)

Un sistema gestor de base de datos (SGBD o DBMS) es el software que guarda los datos y hace cumplir las reglas. Sus cuatro promesas:

• Integridad: las reglas viven EN la base — tipos, obligatorios, unicidad, relaciones. Ni un cliente descuidado las rompe.
• Concurrencia: cientos de usuarios leen y escriben a la vez sin pisarse (transacciones, cap 25).
• Consultas declarativas: describes QUÉ quieres en SQL, no CÓMO buscarlo — el motor decide el camino (cap 32).
• Seguridad y auditoría: usuarios con permisos mínimos y registro de quién hizo qué — el hilo rojo de este curso.

Tres palabras que no son sinónimos

"Base de datos" coloquialmente nombra a las tres; en el manual seremos precisos porque cada una tiene su propio capítulo.

El dominio del curso: la clínica

Toda la serie webcode aprende sobre un caso real (Pedidos en la fila web). Aquí será una clínica: pacientes, médicos, especialidades, citas, recetas y pagos. Elegimos este dominio porque obliga a las preguntas serias: la historia clínica no puede perderse, dos citas no pueden caer en el mismo horario del mismo médico, y CADA cambio sensible debe quedar auditado — ¿quién lo hizo y desde qué cuenta? Esa auditoría dual es el caso estrella de la Parte V.

Este curso recorre cuatro motores con EL MISMO modelo de clínica y EL MISMO esqueleto de capítulos: así cada diferencia que encuentres es del motor, no del ejemplo. Hoy los conocemos a fondo y elegimos el orden.

• Entender la historia y filosofía de PostgreSQL.
• Comparar los 4 motores: licencia, modelo y caso ideal.
• Saber por qué SQLite juega en otra liga (y por qué lo incluimos).
• Conocer la versión objetivo del curso: PostgreSQL 17.

PostgreSQL: la historia en 30 segundos

PostgreSQL nació en 1986 en la Universidad de Berkeley como proyecto de investigación (POSTGRES). En 1996 se relanzó como PostgreSQL con licencia BSD-like (una de las más libres que existen). Su filosofía siempre fue corrección antes que velocidad: implementa el estándar SQL con más fidelidad que nadie, y su lenguaje procedural PL/pgSQL es extremadamente potente.

Hoy es el motor de elección para datos complejos, integridad referencial seria y aplicaciones que necesitan tipos avanzados (JSONB, arrays, hstores, geoespacial con PostGIS). Versión objetivo del curso: PostgreSQL 17 (17.11, soporte hasta Nov 2028; PostgreSQL 18 es la serie "current" y 19 está en beta).

La comparativa honesta

SQLite: el caso especial

SQLite no es un "PostgreSQL pequeño": es otra categoría. No hay servidor, ni usuarios, ni red — la base completa es UN archivo que tu programa abre directamente. Potencia la lectura hasta límites increíbles y administra cero. El precio: sin procedures, sin roles, y varias restricciones de ALTER TABLE.

Por eso su manual usará un esquema algo más simplificado y documentará con honestidad cada cosa que "los otros tres tienen y este no" — esas ausencias SON su lección de pros y contras.

El orden del curso y por qué

PostgreSQL primero (el más riguroso con el estándar SQL, nuestro motor protagonista), MariaDB después (el más difundido en web), SQL Server tercero (el dialecto corporativo T-SQL) y SQLite al final (el contrapunto embebido). Cada manual marcará con etiquetas las diferencias frente a los otros: al terminar tendrás la tabla comparativa definitiva hecha con tu propio código.

PostgreSQL en dos sistemas operativos, con verificación incluida. Al terminar tendrás el servidor corriendo como servicio y el usuario postgres listo para el capítulo siguiente.

• Instalar en Windows con el instalador EDB oficial.
• Instalar en Ubuntu desde los repositorios de la distribución.
• Verificar con pg_isready y pg_version.
• Entender pg_hba.conf: el archivo de autenticación.

Windows: el instalador EDB

1. Descarga el instalador desde postgresql.org/download/windows — elige la versión 17 (la estable) y el instalador x86_64.
2. Ejecuta el asistente: acepta la ruta por defecto (C:\Program Files\PostgreSQL\17).
3. En el paso "password" DEFINE la clave del usuario postgres (el administrador). Guárdala: la necesitarás en cada conexión.
4. Deja marcado el puerto 5432 (el estándar de PostgreSQL) y "Install as service" con nombre PostgreSQL.
5. Opcional: desmarca "Stack Builder" si solo quieres el servidor básico.

Ubuntu: desde los repositorios

Ubuntu trae PostgreSQL en sus repos oficiales — pero la versión del repo puede no ser la última. Para PostgreSQL 17 usamos el repositorio oficial:

# Instalar el paquete de repositorios de PostgreSQL (PGDG):
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt \
$(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Importar la clave GPG:
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg

sudo apt update
sudo apt install postgresql-17

# Verificar que el servicio arrancó:
systemctl status postgresql

En Ubuntu, PostgreSQL crea un usuario del sistema postgres (y NO un usuario de BD con clave). Para entrar:

# Entrar como usuario postgres (peer auth — sin clave):
sudo -u postgres psql

# Salir:
\q

Verificación en ambos sistemas

El puerto 5432

PostgreSQL escucha por defecto en el puerto 5432 (el estándar de la industria para PG). Solo te importará cuando conectes desde PHP (cap 31) o si algún día instalas DOS motores en la misma máquina — en ese caso el segundo deberá usar otro puerto. Anótalo: host localhost, puerto 5432.

pg_hba.conf: el guardián de las conexiones

A diferencia de MySQL/MariaDB que tiene un usuario con host ('user'@'localhost'), PostgreSQL usa un archivo separado para decidir QUIÉN puede conectarse y CÓMO. Se llama pg_hba.conf y vive en la carpeta de datos del cluster.

# Formato: TIPO  BASE  USUARIO  DIRECCIÓN  MÉTODO
# Ejemplos típicos:
local   all   postgres   peer        -- Unix socket, usuario del SO
local   all   all        peer        -- cualquier usuario local
host    all   all        127.0.0.1/32  scram-sha-256  -- TCP con clave

peer significa: "el nombre de usuario del sistema debe coincidir con el nombre de usuario de PostgreSQL" (por eso sudo funciona). scram-sha-256 es el método moderno con clave (reemplaza al viejo md5). No lo toques por ahora; lo profundizaremos en la Parte V.

El cliente de consola (psql) es el laboratorio del curso: ahí probaremos cada consulta antes de llevarla a código. Hoy aprendemos a entrar, orientarnos y crear nuestra base de práctica con su usuario propio.

• Entrar al cliente como postgres (peer auth y clave).
• Leer el prompt y ejecutar los primeros comandos de orientación.
• Crear la base clinica con UTF-8.
• Crear un usuario de práctica con permisos SOLO sobre esa base.

Entrar al cliente

# Ubuntu (peer auth — entra como usuario postgres del sistema):
sudo -u postgres psql

# Windows (usa la clave definida en el instalador):
psql -U postgres

El prompt cambia a postgres=#: estás DENTRO del servidor y conectado a la base postgres (la base por defecto). Todo lo que escribas aquí termina en ; — el punto y coma es lo que envía la sentencia.

Orientarse: comandos de psql

psql tiene comandos que empiezan con \ (barra invertida) — estos NO son SQL, son del cliente. Los más útiles hoy:

\conninfo          -- ¿a dónde estoy conectado?
\l                 -- listar bases de datos
\du                -- listar roles/usuarios
\dn                -- listar esquemas
\dt                -- listar tablas del esquema actual
\q                 -- salir del cliente

Y los comandos SQL que usaremos siempre:

-- Versión del servidor y usuario actual:
SELECT version();
SELECT current_user, session_user;

-- Hoy no hay tablas todavía; eso cambia en el cap 6.

Crear la base del curso

CREATE DATABASE clinica
  ENCODING 'UTF8'
  LC_COLLATE 'es_PE.UTF-8'
  LC_CTYPE 'es_PE.UTF-8'
  TEMPLATE template0;

\c clinica    -- conectar a la base recién creada
SELECT current_database();   -- confirma dónde estás parado

UTF-8 es el estándar moderno para guardar español con tildes, ñ — y emojis si algún día hiciera falta. Las opciones LC_COLLATE y LC_CTYPE configuran el ordenamiento y clasificación de caracteres según tu locale. Si no tienes es_PE.UTF-8 instalado, puedes usar en_US.UTF-8 (el importante es que sea UTF-8).

El usuario de práctica

Trabajar SIEMPRE como postgres es el mal hábito más común de los tutoriales. Creemos un usuario con permisos solo sobre clinica — el mismo patrón que usará PHP en el cap 31:

-- Crear el rol con login y clave:
CREATE ROLE curso WITH LOGIN PASSWORD 'Clinica.2026';

-- Dar permisos SOLO sobre la base clinica:
GRANT ALL PRIVILEGES ON DATABASE clinica TO curso;

-- Dentro de clinica, dar permisos sobre el esquema público:
\c clinica
GRANT ALL ON SCHEMA public TO curso;

-- Probar la identidad del nuevo usuario (salir y volver a entrar):
\q
psql -U curso -d clinica    -- pedirá la clave
SELECT current_user, current_database();

En PostgreSQL no hay el concepto de 'user'@'host' como en MySQL. La restricción de conexión por IP se maneja en pg_hba.conf (cap 3). Aquí curso es un rol con LOGIN — puede conectarse, pero solo a la base clinica y solo con las credenciales correctas.

information_schema y pg_catalog

Al igual que MySQL, PostgreSQL tiene bases del sistema que NO debes tocar:

Cierre de Parte I. El CLI es el laboratorio, pero el trabajo diario se apoya en herramientas gráficas para explorar, diseñar y depurar. Conocer cuál usar para cada tarea ahorra horas.

• Conocer pgAdmin, DBeaver y DBGate y su rol.
• Conectar DBeaver a nuestra base clinica.
• Decidir cuándo gráfico y cuándo CLI.
• Cerrar Parte I con el entorno completo listo.

El ecosistema: cinco herramientas

Dato clave para este curso: pgAdmin, DBeaver, DBGate y Adminer hablan los CUATRO motores que veremos (PostgreSQL, MariaDB, SQL Server y SQLite) — una sola herramienta te acompaña por toda la categoría. pgAdmin, en cambio, se queda en la familia PostgreSQL.

Recomendación del curso: DBeaver Community como gráfica principal — cuando lleguemos a MariaDB y SQL Server no cambiarás de herramienta, solo de conexión. DBGate es su primo ligero: la misma filosofía multi-motor, con versión web en Docker para equipos remotos. pgAdmin es el oficial: incluido en el instalador EDB, con dashboard de monitoreo y herramienta de consultas que muestra planes de ejecución. Y Adminer merece una nota aparte: es EL cliente de emergencia — sube un archivo al servidor y administra la base desde el navegador.

Conectar DBeaver a clinica

1. Nueva conexión → elegir PostgreSQL.
2. Servidor: localhost · Puerto: 5432.
3. Usuario: curso · Clave: la que definiste en el cap 4.
4. Base de datos: clinica. Probar conexión → Finalizar.

Verás el árbol del esquema: tablas, vistas, columnas con sus tipos, índices y llaves. Haz clic en cualquier tabla vacía y DBeaver te muestra el CREATE TABLE que la generó — útil para aprender sintaxis leyendo.

¿Gráfico o CLI?

Arranca Parte II. Antes de escribir un solo CREATE TABLE, entendamos QUÉ vamos a construir y POR QUÉ cada tabla existe. Un modelo entendido se diseña bien; un modelo copiado se sufre.

• Recorrer las 8 tablas agrupadas por función.
• Entender las relaciones 1:N que las unen.
• Saber por qué usuarios_app NO es un usuario del motor.
• Identificar las reglas de negocio que el modelo debe impedir.

Las cuatro familias

El mapa de relaciones

especialidades 1---N medicos
pacientes       1---N citas N---1 medicos
citas           1---1 recetas
citas           1---N pagos
auditoria       (independiente: referencia por nombre de tabla + id)

Léelo en voz alta: "una especialidad tiene muchos médicos; cada cita pertenece a UN paciente y UN médico; una cita genera a lo sumo una receta; puede tener varios pagos (uno fallido y uno exitoso, por ejemplo)". Si puedes leer el modelo como frases, lo entiendes.

La tabla que no es del motor: usuarios_app

Aquí está la distinción que ordena todo el curso:

• roles del MOTOR (curso, cap 4): definen QUIÉN SE CONECTA al servidor y qué puede hacer ahí. Viven en pg_roles (catálogo del sistema).
• usuarios_app (tabla nuestra): definen QUIÉN USA LA APLICACIÓN — la recepcionista Rosa, el doctor Torres. La aplicación los autentica con su propia lógica (PHP, cap 30).

¿Por qué separarlos? Porque cientos de usuarios de la app comparten UNA cuenta de conexión al motor (menos conexiones, permisos mínimos, sin claves de BD repartidas). El precio: el motor ya no sabe "quién es Rosa" — y ese vacío lo llena la auditoría dual.

auditoria: la memoria de quién hizo qué

Cada cambio sensible registrará la dupla completa:

usuario_app : 'rrosa'          <-- la persona, llega como parámetro/variable de sesión
usuario_bd  : 'curso'           <-- el rol de conexión, lo da current_user
tabla_afectada, operacion, registro_id, datos_anteriores, datos_nuevos

Así respondemos las DOS preguntas de un incidente: "¿qué cuenta de la app hizo esto?" y "¿con qué rol de BD?". La Parte V lo implementa con triggers y current_setting().

Reglas de negocio que el modelo debe impedir

Elegir mal un tipo de dato es la deuda técnica más silenciosa: la app funciona... hasta que el monto sale con centavos inventados o la fecha explota. Hoy, los tipos de PostgreSQL con sus rangos reales y la diferencia fundamental: PostgreSQL tiene BOOLEAN de verdad.

• Dominar los tipos enteros y BOOLEAN real (no alias).
• Usar NUMERIC para dinero — y saber por qué NUNCA FLOAT.
• Elegir entre VARCHAR y TEXT con criterio (PostgreSQL: casi idénticos).
• Crear ENUM propio con CREATE TYPE ... AS ENUM.
• Crear las tres tablas de personas del modelo.

Enteros: del SMALLINT al BIGINT

PostgreSQL NO tiene TINYINT — para valores pequeños se usa SMALLINT. Si necesitas un booleano puro, usa BOOLEAN (de verdad, no un alias).

BOOLEAN: real, no alias

A diferencia de MySQL/MariaDB donde BOOLEAN es "sinónimo de TINYINT(1)", PostgreSQL tiene un tipo BOOLEAN nativo. Acepta true, false y null. Internamente almacena 1 byte.

activo BOOLEAN NOT NULL DEFAULT true

-- PostgreSQL acepta estas formas:
INSERT INTO test (activo) VALUES (true);
INSERT INTO test (activo) VALUES (false);
INSERT INTO test (activo) VALUES (NULL);  -- si no tiene NOT NULL

Dinero: NUMERIC, jamás FLOAT

monto NUMERIC(10,2)   -- hasta 99 999 999.99, exacto

-- FLOAT/DOUBLE guardan en binario: 0.1 + 0.2 NO es 0.3
SELECT 0.1 + 0.2;     -- 0.30000000000000004 (con DOUBLE PRECISION)

NUMERIC guarda exactamente los dígitos que declaras: NUMERIC(10,2) son 10 dígitos totales, 2 decimales. Para montos de clínica, NUMERIC(10,2) sobra y es exacto. DECIMAL es un sinónimo de NUMERIC en PostgreSQL.

Texto: VARCHAR vs TEXT

Diferencia clave con MySQL: en PostgreSQL, VARCHAR y TEXT tienen rendimiento idéntico — no hay "off-page" ni "puntero". La única diferencia es que VARCHAR valida la longitud. Para ids cortos y campos con límite conocido, VARCHAR(n); para textos libres, TEXT.

Fechas y horas

Regla práctica: fechas del negocio → TIMESTAMP (la cita fue a las 10:00, punto); marcas técnicas de registro → TIMESTAMPTZ (la auditoría sabe en qué zona horaria se hizo).

ENUM propio: CREATE TYPE ... AS ENUM

PostgreSQL no tiene ENUM inline como MySQL. En su lugar, creas un tipo enumerado como objeto separado de la base:

-- Crear el tipo ENUM:
CREATE TYPE estado_cita AS ENUM (
  'PROGRAMADA', 'ATENDIDA', 'CANCELADA', 'NO_ASISTIO'
);

-- Usarlo en una columna:
CREATE TABLE citas (
  id        SERIAL PRIMARY KEY,
  estado    estado_cita NOT NULL DEFAULT 'PROGRAMADA'
);

-- Agregar un valor futuro (sin ALTER TABLE):
ALTER TYPE estado_cita ADD VALUE 'EN_ESPERA' BEFORE 'ATENDIDA';

El ENUM de PostgreSQL es estricto: solo acepta los valores declarados, y si intentas insertar uno inválido obtienes un error claro. La ventaja sobre CHECK + IN es que el tipo es reutilizable (múltiples tablas pueden compartir el mismo ENUM).

IDENTITY: el AUTO_INCREMENT de PostgreSQL

PostgreSQL no tiene AUTO_INCREMENT. En su lugar, hay dos caminos:

-- SERIAL (atajo, 创建 sequence implícita):
id SERIAL PRIMARY KEY
-- Equivale a: INTEGER + SEQUENCE + DEFAULT nextval()

-- IDENTITY (estándar SQL, preferido desde PG10):
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- Más explícito: el motor controla la secuencia

Las reglas son las mismas que en MySQL: una columna generada por secuencia por tabla, debe ser clave, y los valores borrados nunca se reutilizan.

Las tres tablas de personas

CREATE TABLE especialidades (
  id          SERIAL PRIMARY KEY,
  nombre      VARCHAR(80)  NOT NULL,
  descripcion TEXT
);

CREATE TABLE medicos (
  id             SERIAL PRIMARY KEY,
  nombre         VARCHAR(60) NOT NULL,
  apellido       VARCHAR(60) NOT NULL,
  colegiatura    VARCHAR(20) NOT NULL,
  especialidad_id INTEGER NOT NULL,
  email          VARCHAR(120),
  telefono       VARCHAR(20),
  activo         BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE pacientes (
  id              SERIAL PRIMARY KEY,
  documento       VARCHAR(15) NOT NULL,
  nombre          VARCHAR(60) NOT NULL,
  apellido        VARCHAR(60) NOT NULL,
  fecha_nacimiento DATE NOT NULL,
  sexo            CHAR(1),
  telefono        VARCHAR(20),
  email           VARCHAR(120),
  direccion       VARCHAR(160),
  tipo_sangre     CHAR(3),
  alergias        TEXT,
  activo          BOOLEAN NOT NULL DEFAULT true
);

Nota las diferencias con MySQL: SERIAL en lugar de INT AUTO_INCREMENT, BOOLEAN real en lugar de alias, y TEXT para alergias (sin preocuparse por el límite de VARCHAR).

Las constraints convierten las reglas de negocio en leyes físicas: ni un INSERT descuidado, ni un bug a las 3 AM, ni un script torpe pueden violarlas. Hoy completamos las 8 tablas del modelo con todas sus leyes.

• Aplicar NOT NULL, DEFAULT, UNIQUE y CHECK.
• Declarar FOREIGN KEY con la sintaxis COMPLETA.
• Elegir ON DELETE con criterio: RESTRICT, CASCADE, SET NULL.
• Verificar el resultado con \d+ y la prueba de fuego.

La forma completa de FOREIGN KEY

-- FORMA CORRECTA (constraint de tabla):
especialidad_id INTEGER NOT NULL,
CONSTRAINT fk_medicos_especialidad
  FOREIGN KEY (especialidad_id) REFERENCES especialidades(id)

-- PostgreSQL RECHAZA la forma inline sin constraint con error claro.
-- Pero siempre nombra la FK: te lo agradecerás al depurar.

PostgreSQL es más estricto que MySQL: la forma REFERENCES inline sin nombre de constraint funciona, pero no le pone nombre — y cuando la FK falle, el error no te dirá qué columna falló. Siempre con CONSTRAINT nombre FOREIGN KEY.

Las tablas de operación, con todas sus leyes

CREATE TABLE citas (
  id           SERIAL PRIMARY KEY,
  paciente_id  INTEGER NOT NULL,
  medico_id    INTEGER NOT NULL,
  fecha_hora   TIMESTAMP NOT NULL,
  estado       estado_cita NOT NULL DEFAULT 'PROGRAMADA',
  motivo       VARCHAR(255) NOT NULL,
  diagnostico  TEXT,
  CONSTRAINT uq_medico_fecha
    UNIQUE (medico_id, fecha_hora),
  CONSTRAINT fk_citas_paciente
    FOREIGN KEY (paciente_id) REFERENCES pacientes(id),
  CONSTRAINT fk_citas_medico
    FOREIGN KEY (medico_id) REFERENCES medicos(id)
);

CREATE TABLE recetas (
  id          SERIAL PRIMARY KEY,
  cita_id     INTEGER NOT NULL,
  medicamento VARCHAR(120) NOT NULL,
  dosis       VARCHAR(80)  NOT NULL,
  indicaciones TEXT,
  duracion_dias SMALLINT NOT NULL,
  CONSTRAINT uq_receta_cita
    UNIQUE (cita_id),
  CONSTRAINT fk_recetas_cita
    FOREIGN KEY (cita_id) REFERENCES citas(id)
);

CREATE TABLE pagos (
  id      SERIAL PRIMARY KEY,
  cita_id INTEGER NOT NULL,
  monto   NUMERIC(10,2) NOT NULL,
  metodo  metodo_pago NOT NULL,
  estado  estado_pago NOT NULL DEFAULT 'PENDIENTE',
  fecha   TIMESTAMP NOT NULL,
  CONSTRAINT fk_pagos_cita
    FOREIGN KEY (cita_id) REFERENCES citas(id),
  CONSTRAINT chk_monto_positivo
    CHECK (monto >= 0)
);

Nota las diferencias con MySQL: SERIAL en lugar de AUTO_INCREMENT, INTEGER en lugar de INT, NUMERIC en lugar de DECIMAL, TEXT en lugar de VARCHAR(500), y los tipos ENUM propios (estado_cita, metodo_pago, estado_pago) creados con CREATE TYPE ... AS ENUM en el cap 7.

Qué hace cada constraint

Fíjate en uq_medico_fecha: UNIQUE sobre DOS columnas — la combinación no se repite, aunque cada columna sola sí. Es la regla de la doble agenda hecha ley.

ON DELETE: qué pasa cuando se borra el padre

NO ACTION es el default en PostgreSQL (equivalente a RESTRICT en MySQL — ambos rechazan el borrado). Es exactamente lo que una clínica necesita: nadie borra un paciente que tiene historial — primero se desactiva (activo = false, borrado lógico).

Verificar lo construido

# Listar tablas del esquema público:
\dt

# Ver la estructura completa de una tabla (equivalente a SHOW CREATE TABLE):
\d+ citas

# Ver solo las constraints:
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'citas'::regclass;

-- Prueba de fuego: la FK debe rechazar esto:
INSERT INTO citas (paciente_id, medico_id, fecha_hora, motivo)
VALUES (9999, 1, '2026-09-01 10:00:00', 'prueba');
-- ERROR: insert or update on table "citas" violates
--   foreign key constraint "fk_citas_paciente"

Si el INSERT falló con error de foreign key, las leyes están vivas. Ese mensaje es el sonido de la base defendiéndose.

Las tablas están vacías y las leyes armadas. Hoy las poblamos con datos deterministas — fechas y valores FIJOS, elegidos a propósito para que las consultas de los próximos capítulos den siempre el mismo resultado en tu máquina y en la mía.

• INSERT de una fila y de muchas en una sola sentencia.
• Obtener el id generado con RETURNING.
• Cargar la clínica completa con patrones de negocio reales.
• Entender por qué datos deterministas ayudan a aprender.

INSERT: una fila y varias

INSERT INTO especialidades (nombre, descripcion)
VALUES ('Medicina General', 'Atención primaria y derivaciones');

-- varias filas en UNA sentencia (más eficiente que N inserts):
INSERT INTO especialidades (nombre, descripcion) VALUES
  ('Cardiología',   'Corazón y sistema circulatorio'),
  ('Pediatría',     'Atención de niños y adolescentes'),
  ('Dermatología',  'Piel, cabello y uñas'),
  ('Traumatología', 'Huesos, articulaciones y músculos'),
  ('Oftalmología',  'Ojos y visión');

Con las 6 especialidades cargadas, los médicos referencian por id — pero ¿qué ids les tocó? En PostgreSQL, el truco es RETURNING:

INSERT INTO medicos (nombre, apellido, colegiatura,
                     especialidad_id, email, telefono, activo)
VALUES ('Ana', 'Torres', 'CT-12045', 2, 'atorres@clinica.pe', '987001001', true)
RETURNING id;   -- el id que ACABA de generarse para tu INSERT

RETURNING es el equivalente de PostgreSQL a LAST_INSERT_ID() de MySQL — pero más potente: puede devolver cualquier columna, no solo el id. En un script, lo capturas así:

-- En psql, RETURNING se muestra como resultado del INSERT.
-- En PHP (cap 31), PDO devuelve el id directamente con $pdo->lastInsertId().

Los datos deterministas del curso

El script completo bd_postgresql_clinica.sql acompaña el manual y carga todo de una vez. Sus patrones NO son al azar — cada uno existe para que una consulta futura tenga algo interesante que encontrar:

Muestra del script

INSERT INTO pacientes
  (documento, nombre, apellido, fecha_nacimiento, sexo, telefono, tipo_sangre)
VALUES
  ('45781234', 'María',  'Quispe',  '1990-03-15', 'F', '987111222', 'O+'),
  ('41234567', 'Juan',   'Mamani',  '1985-07-22', 'M', '987222333', 'A+'),
  ('47112233', 'Rosa',   'Huamán',  '1978-11-02', 'F', NULL,        'O-'),
  ('40998877', 'Carlos', 'Sánchez', '2001-01-30', 'M', '987333444', 'B+');

INSERT INTO usuarios_app (usuario, nombre, rol, hash_clave, activo) VALUES
  ('admin',  'Administrador',   'ADMIN',       '$2y$10$...', true),
  ('rrosa',  'Rosa Reyes',      'RECEPCION',   '$2y$10$...', true),
  ('atorres','Dra. Ana Torres', 'MEDICO',      '$2y$10$...', true);

INSERT INTO citas (paciente_id, medico_id, fecha_hora, estado, motivo) VALUES
  (1, 2, '2026-07-06 09:00:00', 'ATENDIDA',   'Control de presión'),
  (2, 2, '2026-07-06 09:30:00', 'ATENDIDA',   'Dolor torácico'),
  (3, 3, '2026-07-07 10:00:00', 'CANCELADA',  'Chequeo anual'),
  (4, 2, '2026-07-08 11:00:00', 'NO_ASISTIO', 'Seguimiento');

El hash $2y$10$... representa un hash bcrypt real generado desde PHP (password_hash) — las claves NUNCA viajan en texto plano, ni siquiera en un curso (cap 30).

¿Por qué deterministas?

Si las fechas fueran NOW() o los datos aleatorios, "los pacientes deudores" daría 5 en mi máquina y 7 en la tuya — y ninguna consulta se podría verificar. Con datos fijos, cuando el cap 17 diga "esta consulta devuelve 4 filas", TÚ debes ver 4. Ese es el contrato del curso: mismo modelo, mismos datos, mismos resultados.

Cierre de Parte II. PostgreSQL es estricto por naturaleza — no tiene "modo indulgente" como MySQL. Hoy destapamos las trampas clásicas: NULL vs cadena vacía, comparaciones que no comparan, y por qué COUNT no cuenta lo que tú crees.

• Entender por qué PostgreSQL es siempre estricto (no hay sql_mode).
• Dominar NULL: qué es, qué NO es, y cómo se compara.
• Evitar las trampas: '' vs NULL, COUNT vs COUNT(*), TRIM.
• Cerrar Parte II con el esquema completo y poblado.

PostgreSQL: estricto por naturaleza

A diferencia de MySQL/MariaDB que tiene un sql_mode configurable (STRICT_TRANS_TABLES, etc.), PostgreSQL siempre es estricto. No existe un modo indulgente que convierta 'abc' en 0 o acepte fechas inválidas. Si insertas un valor incompatible con el tipo, obtienes un error. Punto.

-- PostgreSQL RECHAZA esto siempre:
INSERT INTO pacientes (documento, nombre, apellido,
                       fecha_nacimiento, sexo)
VALUES ('99999999', 'Prueba', 'Trampa', '2026-02-30', 'X');
-- ERROR: date value out of range: "2026-02-30"

-- También rechaza esto:
INSERT INTO medicos (nombre, apellido, colegiatura,
                     especialidad_id, activo)
VALUES ('X', 'Y', 'Z', 'NO_ES_NUMERO', true);
-- ERROR: invalid input syntax for type integer

No necesitas configurar nada: la base de datos protege tus datos desde el primer día. Es una de las razones por las que PostgreSQL es popular en entornos donde la integridad no es negociable.

NULL: la ausencia que rompe lógicas

NULL no es cero, no es cadena vacía: es "NO SABEMOS / NO APLICA". Y tiene una regla que sorprende a todos:

SELECT NULL = NULL;      -- NULL (¡no es TRUE!)
SELECT NULL != NULL;     -- NULL
SELECT 1 = NULL;         -- NULL

-- la única comparación válida contra NULL:
SELECT * FROM pacientes WHERE telefono IS NULL;
SELECT * FROM pacientes WHERE telefono IS NOT NULL;

En el modelo, Rosa no tiene teléfono registrado (NULL) y Juan sí dejó uno vacío en la recepción... ¿cómo se guarda eso? Dos opciones con significados DISTINTOS:

Regla del modelo clinica: si el dato se desconoce → NULL; si se sabe que no hay → cadena vacía o mejor aún, un valor explícito. Elegir UNA convención y no mezclar.

Trampas clásicas (y su antidoto)

Prueba de las trampas, en vivo

-- 1) NULL vs vacío en acción:
SELECT COUNT(*) FROM pacientes;                       -- 20 (filas)
SELECT COUNT(telefono) FROM pacientes;                -- menos (excluye NULLs)

-- 2) la comparación que no compara:
SELECT COUNT(*) FROM pacientes WHERE telefono = NULL; -- 0 (¡siempre!)
SELECT COUNT(*) FROM pacientes WHERE telefono IS NULL; -- la correcta

-- 3) strict mode defendiendo el tipo:
INSERT INTO pacientes (documento, nombre, apellido,
                       fecha_nacimiento, sexo)
VALUES ('99999999', 'Prueba', 'Trampa', '2026-02-30', 'X');
-- ERROR: date value out of range

Arranca Parte III, la más larga del curso: consultar. Todo lo demás (insertar, procedures, PHP) existe para que ESTO sea posible. Empezamos por la anatomía completa de la sentencia más importante del SQL.

• Escribir la anatomía de un SELECT: qué va en cada cláusula.
• Elegir columnas con alias legibles.
• Calcular expresiones y eliminar duplicados con DISTINCT.
• Saber el ORDEN real de ejecución (y por qué importa).

La anatomía

SELECT columna1, columna2        -- 1. QUÉ columnas devolver
FROM tabla                       -- 2. DE DÓNDE salen las filas
WHERE condición                  -- 3. QUÉ filas (filtro de filas)
GROUP BY columna                 -- 4. agrupación (cap 14)
HAVING condición_de_grupo        -- 5. filtro de grupos (cap 14)
ORDER BY columna                 -- 6. orden del resultado
LIMIT n;                         -- 7. cuántas filas

No todas las cláusulas aparecen siempre — SELECT y FROM son los únicos obligatorios — pero el orden NO es negociable: es sintaxis.

Primeros pasos sobre pacientes

-- todo (solo para explorar tablas pequeñas):
SELECT * FROM especialidades;

-- columnas explícitas (la forma profesional):
SELECT nombre, apellido, fecha_nacimiento
FROM pacientes;

-- con alias para leer mejor el resultado:
SELECT nombre AS nombre_paciente,
       apellido AS apellido_paciente,
       fecha_nacimiento AS nacimiento
FROM pacientes;

SELECT * en producción es mala señal: traes columnas que no usas (red y memoria), y si mañana la tabla gana una columna pesada, tu consulta la arrastra sin que la pidieras. En el curso la usaremos para explorar; en código real, siempre columnas nombradas.

Expresiones: la consulta como calculadora

SELECT nombre, apellido,
       EXTRACT(YEAR FROM AGE(fecha_nacimiento)) AS edad,
       nombre || ' ' || apellido AS nombre_completo
FROM pacientes
LIMIT 5;

Las expresiones crean columnas calculadas al vuelo: AGE() calcula la diferencia entre dos fechas, EXTRACT(YEAR FROM ...) saca el año de esa diferencia. Para concatenar texto, PostgreSQL usa el operador || en lugar de CONCAT() — ambos funcionan, pero || es el estándar SQL.

DISTINCT: los valores únicos

-- ¿qué estados de cita EXISTEN realmente en los datos?
SELECT DISTINCT estado FROM citas;

-- ¿qué combinaciones médico+estado han ocurrido?
SELECT DISTINCT medico_id, estado FROM citas ORDER BY medico_id;

DISTINCT elimina duplicados del RESULTADO — opera sobre la fila completa seleccionada, no sobre una columna aislada (en la segunda consulta, la combinación es lo que se hace único).

El orden de ejecución que nadie te cuenta

Tú ESCRIBES SELECT...FROM...WHERE..., pero el motor EVALÚA en otro orden: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Parece pedantería hasta el día que intentas usar en el WHERE un alias que definiste en el SELECT — y falla, porque cuando el WHERE corre, el SELECT todavía no existió:

-- ERROR: columna "edad" no existe:
SELECT nombre, EXTRACT(YEAR FROM AGE(fecha_nacimiento)) AS edad
FROM pacientes
WHERE edad > 40;

-- CORRECTO: repetir la expresión en el WHERE
SELECT nombre, EXTRACT(YEAR FROM AGE(fecha_nacimiento)) AS edad
FROM pacientes
WHERE EXTRACT(YEAR FROM AGE(fecha_nacimiento)) > 40;

El 80% de las consultas reales son esto: qué filas (WHERE), en qué orden (ORDER BY) y cuántas (LIMIT). Dominar estas tres cláusulas con sus operadores te da el SQL del día a día completo.

• Filtrar con operadores de comparación, BETWEEN, IN y LIKE.
• Combinar condiciones con AND/OR/NOT sin ambigüedades.
• Ordenar por varias columnas con dirección mixta.
• Paginar resultados con LIMIT ... OFFSET.

WHERE: los operadores

-- comparación clásica:
SELECT * FROM citas WHERE estado = 'ATENDIDA';

-- rangos con BETWEEN (INCLUYE los extremos):
SELECT * FROM citas
WHERE fecha_hora BETWEEN '2026-07-01' AND '2026-07-31 23:59:59';

-- listas con IN:
SELECT * FROM citas
WHERE estado IN ('CANCELADA', 'NO_ASISTIO');

-- texto parcial con LIKE (% = cualquier cosa, _ = un carácter):
SELECT nombre, apellido FROM pacientes WHERE apellido LIKE 'QUIS%';
SELECT nombre, apellido FROM pacientes WHERE documento LIKE '_7__%';

Ojo con BETWEEN en TIMESTAMP: '2026-07-31' como límite superior solo llega a las 00:00:00 de ese día — por eso el ejemplo termina en '2026-07-31 23:59:59'. Es una de las trampas de fechas más comunes.

AND, OR y los paréntesis que salvan

-- ¿ambiguo? AND se evalúa antes que OR:
SELECT * FROM citas
WHERE estado = 'ATENDIDA' OR estado = 'PROGRAMADA'
  AND medico_id = 2;
-- ¡NO es lo que parece! Esto incluye TODAS las ATENDIDAS de cualquier médico.

-- siempre paréntesis cuando mezclas:
SELECT * FROM citas
WHERE (estado = 'ATENDIDA' OR estado = 'PROGRAMADA')
  AND medico_id = 2;

Regla de la casa: si usas AND y OR en la misma condición, paréntesis. Siempre. Cuesta 5 caracteres y evita el bug más repetido del SQL cotidiano.

ORDER BY: orden profesional

-- varias columnas, direcciones mixtas:
SELECT apellido, nombre, fecha_nacimiento
FROM pacientes
ORDER BY apellido ASC, nombre ASC;

-- citas del más reciente al más viejo:
SELECT fecha_hora, estado, motivo
FROM citas
ORDER BY fecha_hora DESC
LIMIT 10;

El orden por texto respeta el collation de la columna (el español que configuramos en cap 4 ordena bien las tildes). Y ORDER BY puede usar la posición de columna (ORDER BY 2) — no lo hagas: se rompe si editas el SELECT.

LIMIT y OFFSET: paginación

-- página 1 (primeros 10):
SELECT id, apellido, nombre FROM pacientes
ORDER BY apellido, nombre
LIMIT 10;

-- página 3 (filas 21 a 30):
SELECT id, apellido, nombre FROM pacientes
ORDER BY apellido, nombre
LIMIT 10 OFFSET 20;

-- alternativa estándar SQL (equivalente):
SELECT id, apellido, nombre FROM pacientes
ORDER BY apellido, nombre
FETCH FIRST 10 ROWS ONLY
OFFSET 20;

LIMIT sin ORDER BY es una lotería: el motor devuelve CUALQUIER 10 filas — hoy salen ordenadas por azar del almacenamiento, mañana no. LIMIT siempre acompañado de ORDER BY.

Ejercicio integrador del capítulo

-- La agenda del doctor id=2 para la primera semana de julio 2026,
-- solo PROGRAMADAS, de lo más viejo a lo más nuevo, máximo 5:
SELECT fecha_hora, motivo
FROM citas
WHERE medico_id = 2
  AND estado = 'PROGRAMADA'
  AND fecha_hora BETWEEN '2026-07-06' AND '2026-07-12 23:59:59'
ORDER BY fecha_hora
LIMIT 5;

Las funciones escalares transforman valor por valor: cada fila entra a la función y sale transformada. Son el "kit de herramientas" que convierte un dato crudo en información presentable.

• Limpiar y combinar texto: ||, TRIM, UPPER, LENGTH.
• Redondear y calcular: ROUND, CEIL, FLOOR, MOD.
• Manipular fechas: TO_CHAR, AGE, date_part, interval.
• Manejar ausencias: COALESCE con datos reales.

Texto

SELECT
  nombre || ' ' || apellido              AS completo,
  UPPER(LEFT(apellido, 3))               AS iniciales_mayus,
  CHAR_LENGTH(documento)                 AS largo_documento,
  TRIM('  Maria  ')                      AS limpio,
  REPLACE(telefono, ' ', '')             AS telefono_sin_espacios
FROM pacientes
LIMIT 3;

PostgreSQL usa el operador || para concatenar (estándar SQL) en lugar de CONCAT(). CHAR_LENGTH cuenta CARACTERES (útil donde una ñ ocupa más bytes que una a); TRIM quita espacios de los extremos — el antídoto de la trampa del cap 10.

Números

SELECT
  monto,
  ROUND(monto * 0.18, 2) AS igv,      -- 2 decimales exactos
  CEIL(monto / 50)       AS lotes_50, -- hacia arriba
  FLOOR(monto / 50)      AS completos -- hacia abajo
FROM pagos
LIMIT 5;

SELECT MOD(10, 3) AS resto;   -- 1: útil para alternar filas, lotes, etc.

CEIL es el nombre estándar de PostgreSQL (MySQL usa CEILING; ambos funcionan en PG). ROUND sobre NUMERIC es exacto — no hay sorpresas de coma flotante.

Fechas: el grupo más usado en la clínica

SELECT
  fecha_hora,
  fecha_hora::date                         AS solo_dia,
  TO_CHAR(fecha_hora, 'DD/MM/YYYY HH24:MI') AS formato_pe,
  (CURRENT_DATE - fecha_hora::date)        AS dias_transcurridos,
  fecha_hora + INTERVAL '30 minutes'       AS fin_estimado
FROM citas
LIMIT 5;

-- ¿cuándo vence una receta de 7 días?
SELECT medicamento,
       CURRENT_DATE + (duracion_dias || ' days')::interval AS vence
FROM recetas
LIMIT 5;

TO_CHAR es el formateador de fechas de PostgreSQL (equivalente a DATE_FORMAT de MySQL): DD día, MM mes, YYYY año, HH24 hora en formato 24h. Ojo: la fecha se FORMATEA al mostrar — para filtrar y comparar, la columna sigue siendo TIMESTAMP cruda (formatear rompe índices, cap 33).

COALESCE: el traductor de NULL

La función más práctica del SQL: devuelve el PRIMER valor no nulo de su lista. Es el antídoto oficial contra los NULL del cap 10:

-- Rosa no tiene teléfono: mostramos un texto digno en vez de NULL
SELECT nombre || ' ' || apellido AS paciente,
       COALESCE(telefono, 'sin registrar') AS telefono,
       COALESCE(tipo_sangre, '?')          AS sangre
FROM pacientes
LIMIT 8;

COALESCE(a, b, c) evalúa en orden: si a existe, a; si no, b; si tampoco, c. Es estándar SQL y funciona idéntico en los cuatro motores del curso.

CASE: el condicional de columnas

SELECT nombre || ' ' || apellido AS paciente,
       CASE WHEN activo THEN 'activo' ELSE 'inactivo' END AS estado
FROM pacientes
LIMIT 5;

CASE es el reemplazo estándar de IF() de MySQL. Evalúa condiciones EN ORDEN y devuelve el primer WHEN verdadero; el ELSE es el default. Funciona idéntico en los cuatro motores — acostúmbrate a él porque es portable.

Las funciones de agregado colapsan MUCHAS filas en UNA respuesta: "cuántas citas", "cuánto facturado", "el monto máximo". Combinadas con GROUP BY responden las preguntas de la gerencia de la clínica.

• Usar las 5 agregadas básicas y saber qué ignora cada una.
• Agrupar con GROUP BY y filtrar grupos con HAVING.
• Entender la regla de oro: toda columna fuera del agregado va en GROUP BY.
• Construir el reporte de productividad por especialidad.

Las cinco agregadas

SELECT
  COUNT(*)          AS total_citas,     -- cuenta FILAS
  COUNT(diagnostico) AS con_diagnostico, -- cuenta VALORES (excluye NULL)
  SUM(monto)        AS monto_total,     -- ojo: solo tiene sentido con pagos
  AVG(monto)        AS promedio,
  MIN(monto)        AS minimo,
  MAX(monto)        AS maximo
FROM pagos;

La diferencia COUNT(*) vs COUNT(columna) del cap 10 ahora cobra sentido financiero: COUNT(diagnostico) NO cuenta las citas atendidas sin diagnóstico escrito. Cada agregada responde una pregunta distinta — elige la tuya a conciencia.

GROUP BY: un resumen por cada grupo

-- ¿cuántas citas hay en cada estado?
SELECT estado, COUNT(*) AS total
FROM citas
GROUP BY estado;

-- ¿cuánto se pagó por método?
SELECT metodo, COUNT(*) AS operaciones, SUM(monto) AS recaudado
FROM pagos
WHERE estado = 'PAGADO'
GROUP BY metodo;

El motor forma "bolsas" de filas con el mismo valor de la columna agrupada y aplica la agregada a cada bolsa. El WHERE filtra filas ANTES de las bolsas; por eso puede convivir con GROUP BY sin problema.

HAVING: el filtro de los grupos

-- especialidades con MÁS de 2 médicos:
SELECT e.nombre, COUNT(m.id) AS medicos
FROM medicos m
JOIN especialidades e ON e.id = m.especialidad_id
GROUP BY e.nombre
HAVING COUNT(m.id) > 2;

-- métodos de pago con recaudación mayor a 1000:
SELECT metodo, SUM(monto) AS recaudado
FROM pagos
WHERE estado = 'PAGADO'
GROUP BY metodo
HAVING SUM(monto) > 1000;

WHERE filtra filas, HAVING filtra grupos. No puedes poner SUM(monto) > 1000 en el WHERE porque cuando el WHERE corre, la suma todavía no existe (el orden de ejecución del cap 11). HAVING corre DESPUÉS del GROUP BY: ahí sí existen los agregados.

La regla de oro del GROUP BY

-- ERROR en PostgreSQL (siempre estricto):
SELECT metodo, fecha, SUM(monto) FROM pagos GROUP BY metodo;
-- ERROR: column "fecha" must appear in GROUP BY clause
-- or be used in an aggregate function

-- CORRECTO: toda columna del SELECT está en GROUP BY o dentro de un agregado:
SELECT metodo, COUNT(*), SUM(monto) FROM pagos GROUP BY metodo;

PostgreSQL SIEMPRE exige esta regla — no hay forma de apagarla. Los tutoriales viejos de MySQL enseñan a desactivar only_full_group_by — nosotros aprendemos a escribirlo bien.

STRING_AGG: la lista dentro de la fila

-- todos los médicos de cada especialidad, en una línea:
SELECT e.nombre AS especialidad,
       STRING_AGG(m.apellido || ' (' || m.colegiatura || ')', ', '
                  ORDER BY m.apellido) AS medicos
FROM medicos m
JOIN especialidades e ON e.id = m.especialidad_id
GROUP BY e.nombre;

STRING_AGG es el equivalente de PostgreSQL a GROUP_CONCAT de MySQL: junta los valores del grupo en una lista de texto. Acepta ORDER BY dentro de la función (ventaja sobre GROUP_CONCAT que lo pone fuera).

Los datos normalizados viven en tablas separadas — los JOINs los vuelven a juntar en el momento de consultar. Es EL capítulo donde la normalización del cap 6 paga sus dividendos.

• Dominar INNER JOIN con su sintaxis moderna.
• Usar LEFT JOIN para incluir filas SIN pareja.
• Encadenar 3 tablas en una consulta real.
• Construir el anti-join: los que NO tienen (deudores, sin citas).

INNER JOIN: solo los que tienen pareja

SELECT c.fecha_hora, p.nombre, p.apellido,
       m.apellido AS medico, c.estado
FROM citas c
JOIN pacientes p ON p.id = c.paciente_id
JOIN medicos   m ON m.id = c.medico_id
ORDER BY c.fecha_hora
LIMIT 10;

Tres tablas encadenadas en una consulta legible: la cita, su paciente y su médico. Los alias cortos (c, p, m) son estándar de industria — sin ellos la consulta se vuelve ilegible a la tercera tabla. INNER JOIN devuelve SOLO las citas cuyo paciente Y médico existen (en nuestra base, todos: las FK del cap 8 lo garantizan — buen momento para apreciarlas).

LEFT JOIN: todos los de la izquierda, con o sin pareja

-- TODOS los pacientes, con sus citas si las tienen:
SELECT p.apellido, p.nombre, c.fecha_hora, c.estado
FROM pacientes p
LEFT JOIN citas c ON c.paciente_id = p.id
ORDER BY p.apellido;

Con INNER, el paciente sin citas desaparecería del resultado. Con LEFT, aparece TODOS los pacientes; los que no tienen citas traen NULL en las columnas de citas. Ese NULL es información: "este paciente existe pero nunca ha venido".

El anti-join: encontrar a los que NO tienen

-- pacientes SIN ninguna cita:
SELECT p.id, p.apellido, p.nombre
FROM pacientes p
LEFT JOIN citas c ON c.paciente_id = p.id
WHERE c.id IS NULL;

-- DEUDORES: citas ATENDIDAS sin su pago PAGADO:
SELECT p.apellido, p.nombre, c.fecha_hora, c.motivo
FROM citas c
JOIN pacientes p ON p.id = c.paciente_id
LEFT JOIN pagos pg ON pg.cita_id = c.id AND pg.estado = 'PAGADO'
WHERE c.estado = 'ATENDIDA'
  AND pg.id IS NULL
ORDER BY c.fecha_hora;

El patrón es siempre el mismo: LEFT JOIN hacia la tabla "hija" y WHERE hijo.id IS NULL. El segundo ejemplo tiene un detalle fino: la condición pg.estado = 'PAGADO' vive DENTRO del ON, no en el WHERE — así el LEFT JOIN busca específicamente "su pago pagado" y si no lo encuentra trae NULL. Si la hubiéramos puesto en el WHERE, convertiríamos el LEFT en un INNER sin querer (los NULL serían eliminados).

INNER vs LEFT: la decisión en una frase

Los JOIN viejos que no debes escribir

-- sintaxis antigua (join implícito en el WHERE) — NO usar:
SELECT * FROM citas c, pacientes p WHERE c.paciente_id = p.id;

-- el mismo riesgo sin filtro: producto cartesiano gigante
-- (cada cita x cada paciente). PostgreSQL lo rechazará si hay ambigüedad.

Una consulta DENTRO de otra: para comparar contra un cálculo, filtrar por un conjunto o derivar una tabla al vuelo. Y de regalo, CASE: el condicional de columnas que todo reporte necesita.

• Usar subconsultas escalares en SELECT y WHERE.
• Filtrar con IN y NOT IN — y su trampa fatal con NULL.
• Preferir EXISTS/NOT EXISTS cuando la fila importa más que el valor.
• Derivar tablas en FROM y clasificar con CASE.

Subconsulta escalar: un valor que se compara

-- pagos por ENCIMA del promedio:
SELECT fecha, monto, metodo
FROM pagos
WHERE monto > (SELECT AVG(monto) FROM pagos WHERE estado = 'PAGADO')
ORDER BY monto DESC;

-- el médico con más citas:
SELECT id, nombre, apellido
FROM medicos
WHERE id = (SELECT medico_id
            FROM citas
            GROUP BY medico_id
            ORDER BY COUNT(*) DESC
            LIMIT 1);

La subconsulta corre PRIMERO, produce UN valor, y la externa lo usa como una constante. Legible y perfecta cuando el interior devuelve un solo dato.

IN con subconsulta — y su trampa fatal

-- pacientes que SÍ han tenido citas:
SELECT apellido, nombre FROM pacientes
WHERE id IN (SELECT paciente_id FROM citas);

-- pacientes que NUNCA han tenido citas — ¡CUIDADO!
SELECT apellido, nombre FROM pacientes
WHERE id NOT IN (SELECT paciente_id FROM citas);   -- puede devolver VACÍO

Si la subconsulta de NOT IN devuelve UN SOLO NULL (y en nuestra base paciente_id es NOT NULL, pero imagina una columna nullable), la comparación id NOT IN (..., NULL) nunca es verdadera — NULL envenena toda la lista y el resultado es vacío. Recuerda el cap 10: NULL no es igual a nada.

EXISTS: el profesional

-- los que NUNCA han venido (versión robusta):
SELECT p.apellido, p.nombre
FROM pacientes p
WHERE NOT EXISTS (SELECT 1 FROM citas c WHERE c.paciente_id = p.id);

-- los que han tenido cita con cardiología:
SELECT p.apellido, p.nombre
FROM pacientes p
WHERE EXISTS (SELECT 1
              FROM citas c
              JOIN medicos m ON m.id = c.medico_id
              WHERE c.paciente_id = p.id
                AND m.especialidad_id = 2);

EXISTS no mira valores: pregunta "¿EXISTE al menos una fila que cumpla?" y se detiene en la primera que encuentra. Inmune al NULL, legible y eficiente. SELECT 1 es convención: a EXISTS solo le importa que la fila exista, no qué trae. NOT EXISTS reemplaza a NOT IN en el 100% de los casos serios.

Tabla derivada: la subconsulta como FROM

-- total pagado por cita, y sobre ESO, solo las que superan 50:
SELECT cita_id, total_pagado
FROM (SELECT cita_id, SUM(monto) AS total_pagado
      FROM pagos
      WHERE estado = 'PAGADO'
      GROUP BY cita_id) AS totales
WHERE total_pagado > 50;

La subconsulta produce una tabla temporal con alias (obligatorio) y la externa la consulta como si fuera una tabla más. Es el escalón previo natural a los CTEs del cap 17.

CASE: el condicional de columnas

SELECT fecha_hora, estado,
       CASE estado
         WHEN 'ATENDIDA'  THEN '✓ completada'
         WHEN 'CANCELADA' THEN '✗ cancelada'
         WHEN 'NO_ASISTIO' THEN '⚠ inasistencia'
         ELSE '… pendiente'
       END AS lectura
FROM citas
LIMIT 10;

-- CASE con rangos: clasificación por monto
SELECT id, monto,
       CASE
         WHEN monto >= 100 THEN 'consulta especializada'
         WHEN monto >= 50  THEN 'consulta general'
         ELSE 'trámite menor'
       END AS categoria
FROM pagos;

CASE evalúa condiciones EN ORDEN y devuelve el primer WHEN verdadero; el ELSE es el default (sin él, devuelve NULL). Es estándar SQL: funciona idéntico en los cuatro motores del curso.

Las CTEs (Common Table Expressions) son subconsultas con NOMBRE que viven al inicio de la sentencia. Mismo poder que la tabla derivada del cap 16, pero con legibilidad humana: primero los pasos, después el resultado. Y en PostgreSQL, además, se pueden REVISAR con RECURSIVE.

• Escribir una CTE con WITH y usarla en el SELECT.
• Encadenar varias CTEs como pasos de un cálculo.
• Reescribir tablas derivadas del cap 16 como CTE.
• Usar WITH RECURSIVE para recorrer jerarquías.

La sintaxis

WITH nombre_cte AS (
    SELECT ...          -- cualquier consulta válida
)
SELECT ... FROM nombre_cte;

El WITH define un "resultado temporal con nombre" que SOLO vive durante esa sentencia. Reescribamos el reporte de pagos del cap 16:

-- ANTES (tabla derivada): la lógica va al revés
SELECT cita_id, total_pagado
FROM (SELECT cita_id, SUM(monto) AS total_pagado
      FROM pagos WHERE estado = 'PAGADO'
      GROUP BY cita_id) AS totales
WHERE total_pagado > 50;

-- AHORA (CTE): se lee de arriba hacia abajo
WITH totales AS (
    SELECT cita_id, SUM(monto) AS total_pagado
    FROM pagos
    WHERE estado = 'PAGADO'
    GROUP BY cita_id
)
SELECT cita_id, total_pagado
FROM totales
WHERE total_pagado > 50;

Idéntico resultado, otra experiencia de lectura. En consultas de 3+ pasos la diferencia es brutal.

CTEs encadenadas: un pipeline de pasos

WITH citas_2026 AS (
    SELECT c.*, p.apellido, p.nombre,
           m.apellido AS medico
    FROM citas c
    JOIN pacientes p ON p.id = c.paciente_id
    JOIN medicos   m ON m.id = c.medico_id
    WHERE c.fecha_hora >= '2026-01-01'
),
atendidas AS (
    SELECT * FROM citas_2026
    WHERE estado = 'ATENDIDA'
),
por_medico AS (
    SELECT medico, COUNT(*) AS atendidas
    FROM atendidas
    GROUP BY medico
)
SELECT * FROM por_medico
ORDER BY atendidas DESC;

Cada CTE puede usar la anterior — es un pipeline: primero el universo, luego el filtro, luego el resumen. Cada paso tiene nombre y propósito: el reporte se EXPLICA solo.

CTE que reemplaza la subconsulta repetida

-- sin CTE: el promedio se repite dos veces
SELECT * FROM pagos
WHERE monto > (SELECT AVG(monto) FROM pagos
               WHERE estado = 'PAGADO')
  AND estado = 'PAGADO';

-- con CTE: se calcula UNA vez y se nombra
WITH promedio AS (
    SELECT AVG(monto) AS promedio
    FROM pagos WHERE estado = 'PAGADO'
)
SELECT pg.fecha, pg.monto
FROM pagos pg, promedio pr
WHERE pg.estado = 'PAGADO'
  AND pg.monto > pr.promedio;

WITH RECURSIVE: jerarquías vivas

PostgreSQL soporta WITH RECURSIVE: un CTE que se referencia a sí mismo para recorrer árboles jerárquicos. Este es el fuerte de PG frente a otros motores:

-- ejemplo clásico: lista de materiales compuestos
-- (imagina una tabla productos con producto_padre_id)
WITH RECURSIVE arbol AS (
    SELECT id, nombre, NULL::int AS padre_id, 0 AS nivel
    FROM productos
    WHERE producto_padre_id IS NULL  -- raíz
    UNION ALL
    SELECT p.id, p.nombre, p.producto_padre_id, a.nivel + 1
    FROM productos p
    JOIN arbol a ON p.producto_padre_id = a.id
)
SELECT nivel, nombre FROM arbol
ORDER BY nivel, nombre;

-- en la clínica: casos clínicos con subcasos (si el modelo los tuviera)
WITH RECURSIVE casos AS (
    SELECT id, titulo, NULL::int AS padre_id, 0 AS nivel
    FROM casos WHERE padre_id IS NULL
    UNION ALL
    SELECT c.id, c.titulo, c.padre_id, cs.nivel + 1
    FROM casos c
    JOIN casos cs ON c.padre_id = cs.id
)
SELECT nivel, titulo FROM casos ORDER BY nivel;

UNION ALL es obligatorio en RECURSIVE (no UNION: se necesitan duplicados). PostgreSQL追いel límite de 100 niveles por defecto (cambiable con SET max_recursive_iterations). MariaDB también soporta RECURSIVE desde 10.2, pero PostgreSQL es donde más se usa.

Lo que NO es una CTE

Arranca Parte IV: modificar datos. Leer es seguro; escribir no. Hoy vemos las herramientas REALES de PostgreSQL para actualizar y borrar: RETURNING, UPDATE...FROM y DELETE con subconsultas. Sin extensiones peligrosas, sin REPLACE.

• Ritual del UPDATE seguro y por qué es innegociable.
• Borrado lógico vs físico — la clínica nunca borra pacientes.
• UPDATE...FROM: el equivalente profesional de UPDATE con JOIN.
• RETURNING: ver qué cambió sin hacer un SELECT extra.
• DELETE con subconsulta: borrar lo que cumple una condición compleja.

El ritual del UPDATE seguro

-- PASO 1: escribe el SELECT y MÍRALO:
SELECT id, telefono FROM pacientes
WHERE documento = '45781234';

-- PASO 2: convierte en UPDATE (mismo WHERE, intacto):
UPDATE pacientes
SET telefono = '987111999'
WHERE documento = '45781234';

-- PASO 3: verifica con el mismo SELECT:
SELECT id, telefono FROM pacientes
WHERE documento = '45781234';

El WHERE del SELECT y el del UPDATE deben ser EL MISMO. Copiar-pegar el WHERE, nunca reescribirlo de memoria. El UPDATE sin WHERE cambia TODA la tabla — y PostgreSQL no pregunta "¿estás seguro?".

Borrado lógico: la clínica no borra pacientes

-- NO se hace (borrado físico):
DELETE FROM pacientes WHERE id = 4;

-- SE HACE (borrado lógico — el modelo lo previó con la columna activo):
UPDATE pacientes SET activo = FALSE WHERE id = 4;

-- y todas las consultas de la app filtran:
SELECT * FROM pacientes WHERE activo;

Historia clínica: los datos de un paciente atendido NO se eliminan jamás (razones legales y de salud). El DELETE físico queda reservado para datos verdaderamente desechables.

UPDATE...FROM: el JOIN de las actualizaciones

-- desactivar a todos los médicos de una especialidad específica:
UPDATE medicos
SET activo = FALSE
FROM especialidades
WHERE medicos.especialidad_id = especialidades.id
  AND especialidades.nombre = 'Oftalmología';

-- actualizar el monto de una cita usando el IGV calculado:
UPDATE pagos
SET monto = sub.total * 1.18
FROM (SELECT cita_id, SUM(monto) AS total
      FROM pagos WHERE estado = 'PENDIENTE'
      GROUP BY cita_id) AS sub
WHERE pagos.cita_id = sub.cita_id;

En PostgreSQL, UPDATE...FROM accede a otra tabla SIN ponerte en la lista de UPDATE. Solo las filas que cumplen la condición del FROM se actualizan. A diferencia de MySQL, NO puedes usar ORDER BY o LIMIT en UPDATE — necesitas una subconsulta o CTE para limitar.

DELETE con subconsulta

-- borrar citas CANCELADAS de pacientes inactivos:
DELETE FROM citas
WHERE id IN (
    SELECT c.id
    FROM citas c
    JOIN pacientes p ON p.id = c.paciente_id
    WHERE c.estado = 'CANCELADA' AND NOT p.activo
);

-- borrar pagos PENDIENTES de citas anteriores a 2025:
DELETE FROM pagos
WHERE estado = 'PENDIENTE'
  AND cita_id IN (
      SELECT id FROM citas
      WHERE fecha_hora < '2025-01-01'
  );

PostgreSQL permite DELETE con subconsulta en WHERE — MySQL (sin MariaDB) NO lo permite directamente. Es una de las ventajas reales de PG.

RETURNING: ver qué cambió sin SELECT extra

-- RETURNING muestra las filas AFFECTADAS por el DML:
UPDATE pacientes
SET telefono = '987111999'
WHERE documento = '45781234'
RETURNING id, nombre, apellido, telefono;

DELETE FROM pagos
WHERE estado = 'PENDIENTE'
  AND fecha < '2024-01-01'
RETURNING *;

RETURNING es el equivalente de PostgreSQL al LAST_INSERT_ID() de MySQL, pero mucho más poderoso: devuelve filas completas, no solo el id. Útil en migraciones: INSERT...VALUES...RETURNING id te da todos los IDs generados sin hacer SELECT separado.

ON CONFLICT (DO UPDATE): el UPSERT de PostgreSQL

-- si la especialidad ya existe, actualizar su descripción:
INSERT INTO especialidades (nombre, descripcion)
VALUES ('Cardiología', 'Corazón y sistema circulatorio — v2')
ON CONFLICT (nombre) DO UPDATE
    SET descripcion = EXCLUDED.descripcion;
-- 1 insertada o actualizada, misma PK, sin borrado

-- VERSIONES REVISADAS: marcar como releído
INSERT INTO versiones (titulo, contenido, version)
VALUES ('manual_v2', 'Texto actualizado...', 2)
ON CONFLICT (titulo) DO UPDATE
    SET version = EXCLUDED.version,
        contenido = EXCLUDED.contenido;

EXCLUDED es una tabla especial que contiene lo que INTENTABAS insertar. Es equivalente a ON DUPLICATE KEY UPDATE de MySQL pero más limpio: menciona la constraint por nombre (no busca cualquier UNIQUE automáticamente), la fila existente no se borra ni re-crea, y es estándar SQL: funciona idéntico en SQLite y PostgreSQL.

Las transacciones son el cinturón de seguridad del MODIFY: si una operación falla a mitad, TODO vuelve al estado anterior. "Todo o nada" es literal en PostgreSQL — sin transacciones, cada INSERT es un volado.

• Entender ACID en lenguaje humano (no en teoría de libro).
• Usar BEGIN, COMMIT y ROLLBACK con psycopg2 / psql.
• SAVEPOINT y ROLLBACK TO: deshacer un paso sin perder todo.
• Saber cuándo PostgreSQL commitea automático (autocommit).

ACID en lenguaje de clínica

BEGIN / COMMIT / ROLLBACK

-- en psql:
BEGIN;

INSERT INTO pagos (cita_id, monto, metodo, estado)
VALUES (10, 150.00, 'EFECTIVO', 'PENDIENTE');

UPDATE citas SET estado = 'PAGADA' WHERE id = 10;

-- ¿todo bien? confirmar:
COMMIT;

-- o algo falló? deshacer TODO:
-- ROLLBACK;

-- en Python con psycopg2 (cap 32):
-- with psycopg.connect(...) as conn:
--     with conn.cursor() as cur:
--         cur.execute("INSERT INTO pagos ...")
--         cur.execute("UPDATE citas ...")
--         conn.commit()    # o conn.rollback() en caso de error

Cada conexión psycopg2 tiene autocommit DESACTIVADO por defecto: cada sentencia sin BEGIN se ejecuta sola y se confirma inmediatamente (útil para pruebas rápidas). Para operaciones múltiples, BEGIN + COMMIT es innegociable.

SAVEPOINT: deshacer un paso sin perder todo

BEGIN;

INSERT INTO pagos (cita_id, monto, metodo, estado)
VALUES (10, 150.00, 'EFECTIVO', 'PENDIENTE');
SAVEPOINT antes_del_segundo_pago;

INSERT INTO pagos (cita_id, monto, metodo, estado)
VALUES (10, 75.50, 'TARJETA', 'PENDIENTE');

-- el segundo pago tiene un error de monto:
ROLLBACK TO antes_del_segundo_pago;
-- el primer pago INTACTO, el segundo eliminado

INSERT INTO pagos (cita_id, monto, metodo, estado)
VALUES (10, 80.00, 'TARJETA', 'PENDIENTE');

COMMIT;  -- ambos pagos confirmados

SAVEPOINT crea un "punto de guardado" al que puedes volver. ROLLBACK TO deshace TODO lo que ocurrió DESPUÉS del savepoint, pero deja intacto lo anterior. RELEASE SAVEPOINT libera el nombre sin deshacer nada (limpieza cuando ya no lo necesitas).

Autocommit: el default que debes conocer

En psql, la práctica del cap 10-12 funciona por autocommit: cada INSERT se confirma solo. Pero en producción (Python, PHP), NUNCA dependas de autocommit: una excepción sin ROLLBACK deja la conexión en estado indeterminado.

Errores comunes (y cómo evitarlos)

-- ERROR: no se puede hacer ROLLBACK fuera de transacción
ROLLBACK;   -- FAIL: there is no transaction in progress

-- ERROR: SAVEPOINT "foo" does not exist (ya fue liberado)
ROLLBACK TO foo;
-- después de RELEASE SAVEPOINT foo;

Una vista es una consulta con NOMBRE que se guarda en la base. La usas como si fuera una tabla, pero no almacena datos: ejecuta la consulta CADA VEZ que la llamas. Es el equivalente a un "reporte permanente" que siempre muestra datos frescos.

• Crear y consultar una vista con CREATE VIEW.
• Entender por qué NO almacena datos (a diferencia de MySQL).
• Crear vistas con JOINs que simplifican la app.
• Usar WITH CHECK OPTION para seguridad de UPDATE/INSERT sobre vistas.

Crear y consultar una vista

-- crear la vista: la consulta vive en la base
CREATE VIEW vista_citas AS
SELECT c.id, c.fecha_hora, c.estado, c.motivo,
       p.apellido || ', ' || p.nombre AS paciente,
       m.apellido AS medico
FROM citas c
JOIN pacientes p ON p.id = c.paciente_id
JOIN medicos   m ON m.id = c.medico_id;

-- usarla como si fuera una tabla:
SELECT * FROM vista_citas
WHERE estado = 'ATENDIDA'
ORDER BY fecha_hora DESC;

La vista "recuerda" la consulta, no el resultado. Cada SELECT sobre ella ejecuta la consulta completa — si la consulta tiene un JOIN de 3 tablas, cada SELECT sobre la vista hace ese JOIN otra vez. La diferencia con MySQL: PostgreSQL NO materializa vistas por defecto (a diferencia de STORED TABLE en MySQL); para eso existen las vistas materializadas (cap 27).

Vista con CTEs: un reporte complejo, guardado

CREATE VIEW reporte_productividad AS
WITH por_medico AS (
    SELECT medico_id, COUNT(*) AS citas_atendidas
    FROM citas
    WHERE estado = 'ATENDIDA'
    GROUP BY medico_id
)
SELECT e.nombre AS especialidad,
       m.apellido, m.nombre,
       COALESCE(pm.citas_atendidas, 0) AS atendidas
FROM medicos m
JOIN especialidades e ON e.id = m.especialidad_id
LEFT JOIN por_medico pm ON pm.medico_id = m.id;

-- ahora es simple:
SELECT especialidad, COUNT(*) AS medicos,
       SUM(atendidas) AS citas_totales
FROM reporte_productividad
GROUP BY especialidad;

La vista encapsula la CTE: quien consulta no necesita saber que hay un WITH dedentro. Modificas la vista (ALTER VIEW o DROP + CREATE) y TODOS los consultores se benefician sin cambiar su código.

WITH CHECK OPTION: protección contra datos fantasma

-- vista de pacientes activos:
CREATE VIEW pacientes_activos AS
SELECT * FROM pacientes WHERE activo = TRUE
WITH CHECK OPTION;

-- esto FUNCIONA (la fila cumple activo = TRUE):
INSERT INTO pacientes_activos (nombre, apellido, documento)
VALUES ('Juan', 'García', '12345678');

-- esto FALLA (intentas insertar un inactivo a través de la vista activa):
INSERT INTO pacientes_activos (nombre, apellido, documento, activo)
VALUES ('Pedro', 'López', '87654321', FALSE);
-- ERROR: new row violates WITH CHECK OPTION for view "pacientes_activos"

WITH CHECK OPTION asegura que cualquier INSERT o UPDATE sobre la vista cumpla la condición WHERE de la vista. Sin ella, podrías insertar un paciente inactivo a través de la vista de activos — el dato "desaparece" de la vista sin error.

Modificar y eliminar vistas

-- cambiar la consulta (no borra datos, solo cambia la definición):
CREATE OR REPLACE VIEW vista_citas AS
SELECT c.id, c.fecha_hora, c.estado,
       p.apellido || ', ' || p.nombre AS paciente
FROM citas c
JOIN pacientes p ON p.id = c.paciente_id;

-- borrar la vista (NO borra las tablas subyacentes):
DROP VIEW IF EXISTS vista_citas;

CREATE OR REPLACE VIEW es idiomático en PostgreSQL: crea si no existe, reemplaza si existe. No necesitas DROP + CREATE. La vista NO almacena datos, así que DROP VIEW es seguro: no pierdes información, solo la definición de la consulta.

Vista vs tabla: cuándo usar cada una

Un recepcionista agenda mientras otro cancela la misma cita. El nivel de aislamiento decide si uno ve los cambios del otro antes de que confirme. PostgreSQL defaulta a READ COMMITTED — el 95% de los casos, pero no es el único nivel. Hoy aprendes a elegir con criterio.

• Entender los 3 problemas de concurrencia con ejemplos de la clínica.
• Saber qué nivel usa PostgreSQL por defecto y por qué.
• Subir a SERIALIZABLE solo cuando lo justificas.
• Verificar el nivel actual con current_setting().

Los 3 problemas de concurrencia

Dos recepcionistas trabajando a la vez sobre la misma cita:

Los niveles de aislamiento de PostgreSQL

*En PostgreSQL, READ UNCOMMITTED se promueve a READ COMMITTED (PG nunca permite lectura sucia — garantía del MVCC). Y REPEATABLE READ mitiga fantasmas mediante Snapshot Isolation, pero no al nivel serial: la diferencia con SERIALIZABLE es real y detectable.

-- verificar el nivel actual:
SELECT current_setting('transaction_isolation');
-- resultado esperado: 'read committed'

-- subir para una sesión (cuesta rendimiento, úsalo solo si lo justificas):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- o solo para la próxima transacción:
SET LOCAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

PostgreSQL es más estricto que MySQL/MariaDB

Un matiz que vale oro: PostgreSQL serializa OWNING writes (bloquea el WRITE de quien viola una serialización potencial). MariaDB/InnoDB por defecto NO lo hace: si dos transacciones concurrentes violan REPEATABLE READ, una silenciosamente sobreescribe la otra. Esto es más seguro en PostgreSQL pero puede causar más errores de concurrencia visibles.

PostgreSQL es más que un motor de SQL: tiene un ecosistema de herramientas propias. Hoy recorremos los clientes nativos de PG y la diferencia con los drivers de MySQL, para que sepas elegir la herramienta correcta en cada contexto.

• Conocer psql, pgAdmin, DBeaver y DataGrip como clientes de PG.
• Entender la diferencia entre cliente nativo y driver ODBC/JDBC.
• Saber cuándo usar cada herramienta.
• Comparar con el ecosistema MySQL (mysql CLI, Workbench, ODBC).

Los clientes de PostgreSQL

psql: la herramienta más poderosa que MySQL CLI

# conectar:
psql -U admin -d clinica -h localhost

# comandos internos de psql (no son SQL):
\l          # listar bases de datos
\dt         # listar tablas del search_path actual
\d+ tabla   # descripción detallada (columnas, índices, tamaño)
\di         # listar índices
\df         # listar funciones
\sv vista   # mostrar definición de una vista
\! ls       # ejecutar comando del SO sin salir de psql

# piped queries (raro pero útil):
echo "SELECT COUNT(*) FROM citas;" | psql -U admin -d clinica

psql es el equivalente de mysql CLI pero con MUCHO más poder: comandos internos (\dt, \d+), formato tabular automático, historial, y sobre todo: no necesitas GUI para hacer cualquier cosa.

Drivers de conexión

El driver nativo de PostgreSQL es libpq (el mismo que usa psql). psycopg2 (Python) y PDO pgsql (PHP) lo usan internamente. Para MySQL, el equivalente es libmysqlclient, que usa mysql CLI, PDO mysql y mysql-connector.

pgAdmin vs Workbench: la diferencia de filosofía

Un SELECT sobre 1 millón de filas sin índice es como buscar un nombre en una guía telefónica ordenada por número: puede funcionar, pero lentamente. Los índices son el acelerador real; EXPLAIN te muestra si PostgreSQL los está usando. Y EXPLAIN ANALYZE te dice la verdad completa.

• Crear y entender un índice B-tree sobre una columna.
• Leer un plan de EXPLAIN básico: Seq Scan vs Index Scan.
• EXPLAIN ANALYZE: el timing real, no solo el plan.
• Saber cuándo crear un índice (y cuándo NO).

¿Qué es un índice?

-- SIN índice: PostgreSQL lee TODA la tabla (Seq Scan)
EXPLAIN SELECT * FROM citas WHERE paciente_id = 3;

-- crear el índice:
CREATE INDEX idx_citas_paciente ON citas(paciente_id);

-- ahora sí usa Index Scan (acceso directo, O(log n)):
EXPLAIN SELECT * FROM citas WHERE paciente_id = 3;

Un índice B-tree es una estructura que ordena los valores y permite saltar directamente al registro buscado — como el índice alfabético de un libro: abres por la letra correcta, no lees todo el libro. PostgreSQL lo crea automáticamente para las PK (el id), pero NO para las FK (por eso paciente_id no tenía índice).

EXPLAIN: el plan antes de ejecutar

-- plan básico (estimado, sin ejecutar):
EXPLAIN SELECT * FROM citas WHERE paciente_id = 3;

-- resultado típico:
-- Index Scan using idx_citas_paciente on citas
--   Index Cond: (paciente_id = 3)
--   Rows Removed by Filter: 0

EXPLAIN no ejecuta la consulta — estima el plan basándose en estadísticas de la tabla. Es RÁPIDO y seguro: no toca datos. Si ves "Seq Scan" (sequential scan), significa que PostgreSQL no encontró un índice útil y va a leer toda la tabla — en tablas grandes, es un alerta.

EXPLAIN ANALYZE: el timing real

-- ejecuta REALMENTE la consulta y muestra tiempos reales:
EXPLAIN ANALYZE
SELECT * FROM citas WHERE paciente_id = 3;

-- resultado típico:
-- Index Scan using idx_citas_paciente on citas
--   Index Cond: (paciente_id = 3)
--   Planning Time: 0.150 ms
--   Execution Time: 0.312 ms

-- vs Seq Scan (sin índice):
-- Seq Scan on citas
--   Filter: (paciente_id = 3)
--   Rows Removed by Filter: 25
--   Planning Time: 0.080 ms
--   Execution Time: 1.850 ms

EXPLAIN ANALYZE ejecuta la consulta y te da los tiempos REALES. Ojo: el resultado es el que SE VA A EJECUTAR (en queries con INSERT, UPDATE o DELETE). Para consultas SELECT, usa EXPLAIN ANALYZE con cuidado si no quieres que se ejecute — o mejor, ejecútala primero sin ANALYZE para entender el plan.

Tipos de índices en PostgreSQL

B-tree es el 90% de los índices que vas a crear: soporta igualdades (=), rangos (<, BETWEEN), y ORDER BY. Los demás son para casos especializados.

Cuándo crear (y cuándo NO)

-- índice compuesto: el más valioso para reportes
CREATE INDEX idx_citas_medico_estado
ON citas(medico_id, estado);

-- EXPLAIN ahora puede cubrir ambas columnas del SELECT
-- sin hacer Seq Scan sobre toda la tabla:
EXPLAIN SELECT COUNT(*) FROM citas
WHERE medico_id = 3 AND estado = 'ATENDIDA';

PostgreSQL tiene un modelo de seguridad mucho más robusto que MySQL: "roles" que son usuarios y grupos en uno, permisos granulares, y un archivo pg_hba.conf que controla QUIÉN se conecta. Es el capítulo que separa un proyecto hobby de uno profesional.

• Crear roles con LOGIN y PASSWORD.
• Conceder y revocar permisos con GRANT y REVOKE.
• Entender el search_path y el concepto de esquemas.
• Saber qué es pg_hba.conf y por qué es la primera línea de defensa.

Roles: usuarios y grupos en uno

-- crear un rol (no puede logearse aún):
CREATE ROLE recepcionista;

-- rol con contraseña que SÍ puede logearse:
CREATE ROLE rosa LOGIN PASSWORD 'Rosa2026!';
CREATE ROLE carlos LOGIN PASSWORD 'Carlos2026!';

-- dar permisos al grupo:
GRANT recepcionista TO rosa;
GRANT recepcionista TO carlos;

-- ahora damos permisos al ROL GRUPO (todos sus miembros los heredan):
GRANT SELECT ON ALL TABLES IN SCHEMA public TO recepcionista;
GRANT INSERT, UPDATE ON citas, pagos TO recepcionista;

-- verificar quién tiene qué:
\du+    -- en psql: lista roles y atributos
\dp     -- lista permisos sobre tablas

En PostgreSQL, ROL es el único objeto de usuario: un rol con LOGIN es un usuario; sin LOGIN es un grupo. Esta unificación es más limpio que el modelo MySQL de "CREATE USER" + "CREATE ROLE" separados.

Permisos: GRANT y REVOKE

-- niveles de permisos:
GRANT SELECT ON TABLE pacientes TO recepcionista;
GRANT INSERT ON TABLE pagos TO recepcionista;
GRANT UPDATE (telefono, email) ON TABLE pacientes TO recepcionista;
-- solo puede actualizar teléfono y email, NO其他 columnas

-- permisos sobre funciones:
GRANT EXECUTE ON FUNCTION registrar_pago TO recepcionista;

-- revocar:
REVOKE INSERT ON pagos FROM recepcionista;

-- revocar TODO de golpe (no recomendado en producción):
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM recepcionista;

La granularidad es REAL: puedes conceder acceso a solo ciertas COLUMNAS, ciertas funciones, o ciertas OPERACIONES. MySQL limita permisos a nivel de tabla o columna, pero no a nivel de función — es una de las ventajas de PG.

Search path y esquemas

-- el search_path define DÓNDE buscar tablas:
SHOW search_path;
-- resultado: "$user", public

-- PostgreSQL busca primero en un esquema que se llama igual que el usuario,
-- luego en public. Si creas tablas sin especificar esquema, van a public.

-- crear un esquema para la clínica (buena práctica):
CREATE SCHEMA clinica;
SET search_path TO clinica, public;

-- ahora las tablas se crean en clinica, no en public:
CREATE TABLE clinica.pacientes (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(80) NOT NULL
);

-- el usuario rosa busca "pacientes" y PostgreSQL lo encuentra en clinica:

Los esquemas son "carpetas" dentro de la base de datos. La mayoría de proyectos ponen todo en public (el default), pero para aplicaciones serias, un esquema propio separa tu app de las extensiones y del catálogo del sistema.

pg_hba.conf: la primera línea de defensa

# pg_hba.conf (en Ubuntu: /etc/postgresql/17/main/pg_hba.conf)

# LOCAL: conexiones desde el mismo servidor
local   all   admin       md5

# TCP/IP: conexiones remotas
host    clinica  rosa    192.168.1.0/24  md5
host    clinica  carlos  192.168.1.0/24  md5

# rechazar todo lo demás:
host    all       all     0.0.0.0/0       reject

pg_hba.conf controla QUIÉN puede conectarse, DESDE DÓNDE y CON QUÉ método de autenticación. "md5" significa contraseña hasheada; "scram-sha-256" es el推荐 actual (más seguro). Sin pg_hba.conf bien configurado, el usuario y contraseña de la aplicación no sirven de nada.

PL/pgSQL es el lenguaje procedural de PostgreSQL: un bloque de lógica que vive DENTRO de la base y se ejecuta como una función. Hoy construimos fn_edad, fn_saldo y agendar_cita — la operación más delicada de la clínica — como funciones con $$ como delimitador.

• Crear funciones con PL/pgSQL y el delimitador $$.
• Usar DECLARE, BEGIN/END, IF/ELSEIF/END IF.
• Levantar errores con RAISE EXCEPTION y SQLSTATE.
• Elegir entre función (devuelve valor) y procedimiento (no devuelve).

$$: el delimitador de PostgreSQL

MySQL necesita DELIMITER // para no partir el cuerpo en dos. PostgreSQL usa el $$ (dollar quoting): el contenido entre $$ se entrega COMPLETO al servidor sin splits. Es la convención estándar de PG — más limpio que DELIMITER.

-- primera función: fn_edad
CREATE OR REPLACE FUNCTION fn_edad(p_nacimiento DATE)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(p_nacimiento));
END;
$$;

LANGUAGE plpgsql indica el lenguaje (plpgsql es el procedural); AS $$ abre el cuerpo; el primer $$; lo cierra. No hay DELIMITER, no hay cliente especíco: el $$ es estándar.

fn_edad: usarla como una nativa

-- en SELECT:
SELECT nombre, apellido, fn_edad(fecha_nacimiento) AS edad
FROM pacientes
LIMIT 5;

-- en WHERE:
SELECT nombre, apellido, fn_edad(fecha_nacimiento) AS edad
FROM pacientes
WHERE fn_edad(fecha_nacimiento) >= 65
ORDER BY edad DESC;

La función se comporta exactamente como ROUND o COALESCE del cap 13: recibe un valor, devuelve un valor, se usa en cualquier expresión. Pero esta la hiciste tú.

IF / ELSEIF / END IF: decisiones en plpgsql

-- función que clasifica la edad:
CREATE OR REPLACE FUNCTION fn_clasificar_edad(p_nacimiento DATE)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_edad INT;
BEGIN
    v_edad := EXTRACT(YEAR FROM AGE(p_nacimiento));

    IF v_edad < 18 THEN
        RETURN 'menor de edad';
    ELSIF v_edad < 60 THEN
        RETURN 'adulto';
    ELSE
        RETURN 'adulto mayor';
    END IF;
END;
$$;

SELECT nombre, fn_clasificar_edad(fecha_nacimiento) AS categoria
FROM pacientes LIMIT 5;

La estructura es IF/ELSIF/ELSE/END IF (con S: ELSEIF lleva S, no E). Las variables se declaran con DECLARE al inicio del cuerpo; el operador de asignación es :=.

RAISE EXCEPTION: los errores con TU mensaje

CREATE OR REPLACE FUNCTION fn_validar_documento(p_doc TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    IF LENGTH(TRIM(p_doc)) < 8 THEN
        RAISE EXCEPTION 'Documento debe tener al menos 8 caracteres'
            USING ERRCODE = 'P0001';
    END IF;
    RETURN TRUE;
END;
$$;

-- probar:
SELECT fn_validar_documento('123');
-- ERROR: Documento debe tener al menos 8 caracteres
-- Context: PL/pgSQL function fn_validar_documento line 5 at RAISE

RAISE EXCEPTION es el equivalente de SIGNAL SQLSTATE de MySQL. La diferencia: en MySQL usas el código 45000 genérico; en PostgreSQL puedes usar códigos propios (P0001-Pxxxx son para errores de aplicación). El mensaje llega al cliente con contexto (nombre de función, línea).

SELECT ... INTO: capturar valores

-- fn_saldo: saldo pendiente de un paciente
CREATE OR REPLACE FUNCTION fn_saldo_paciente(p_paciente_id INT)
RETURNS NUMERIC(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_saldo NUMERIC(10,2);
BEGIN
    SELECT COALESCE(SUM(pg.monto), 0)
    INTO v_saldo
    FROM pagos pg
    JOIN citas c ON c.id = pg.cita_id
    WHERE c.paciente_id = p_paciente_id
      AND pg.estado = 'PENDIENTE';

    RETURN v_saldo;
END;
$$;

-- deudores en una línea:
SELECT apellido, nombre, fn_saldo_paciente(id) AS saldo
FROM pacientes
WHERE fn_saldo_paciente(id) > 0
ORDER BY saldo DESC;

SELECT ... INTO captura UN valor de una consulta y lo mete en una variable. Es exactamente lo que hace en MySQL, pero en plpgsql el cuerpo es más estructurado (DECLARE + BEGIN/END).

set_config / current_setting: las variables de sesión

-- PostgreSQL NO tiene @variable: usa set_config() (el canal usuario_app)
SELECT set_config('app.usuario', 'rrosa', false);
--                          clave      valor    ¿local a la txn?

-- leerla:
SELECT current_setting('app.usuario');  -- 'rrosa'

-- dentro de triggers, procedures:
CREATE OR REPLACE FUNCTION fn_obtener_usuario_app()
RETURNS TEXT
LANGUAGE sql
AS $$
    SELECT COALESCE(current_setting('app.usuario', true), '(sin app)');
$$;

set_config(setting, value, is_local) es el equivalente de SET @variable = 'valor'. El tercer parámetro (false) hace que viva en toda la conexión; true sería solo para la transacción actual. Es el canal oficial entre tu aplicación y los triggers del cap 28.

Las funciones de ventana calculan sobre un "ventana" de filas relacionadas SIN colapsar el resultado (a diferencia de GROUP BY). Te dan el ranking, el acumulado y la diferencia entre filas — sin subconsultas ni CTEs.

• ROW_NUMBER, RANK, DENSE_RANK: cada uno tiene su caso de uso.
• PARTITION BY: ranking dentro de cada grupo.
• OVER: la cláusula que define la ventana.
• LAG/LEAD: acceder a la fila anterior/siguiente.

ROW_NUMBER: el consecutivo garantizado

-- lista de citas con número de fila:
SELECT id, fecha_hora, estado,
       ROW_NUMBER() OVER (ORDER BY fecha_hora) AS fila
FROM citas
LIMIT 10;

OVER (ORDER BY fecha_hora) define la ventana: ordena las filas por fecha y les asigna un número del 1 al N. Sin OVER, ROW_NUMBER no sabe qué hacer — la ventana es OBLIGATORIA.

RANK vs DENSE_RANK: el empate con y sin hueco

-- ranking de citas por monto (pagos):
SELECT c.fecha_hora, pg.monto,
       RANK()       OVER (ORDER BY pg.monto DESC) AS puesto,
       DENSE_RANK() OVER (ORDER BY pg.monto DESC) AS puesto_denso,
       ROW_NUMBER() OVER (ORDER BY pg.monto DESC) AS fila
FROM pagos pg
JOIN citas c ON c.id = pg.cita_id
WHERE pg.estado = 'PAGADO'
LIMIT 10;

RANK deja huecos (1, 1, 3); DENSE_RANK no deja huecos (1, 1, 2); ROW_NUMBER no empatan nunca (si hay empate, elige arbitrariamente). Cada uno responde una pregunta distinta.

PARTITION BY: ranking por grupo

-- médico más ocupado de cada mes (visto en el cap 18):
WITH por_medico AS (
    SELECT EXTRACT(YEAR FROM fecha_hora)  AS anio,
           EXTRACT(MONTH FROM fecha_hora) AS mes,
           medico_id,
           COUNT(*) AS atendidas
    FROM citas
    WHERE estado = 'ATENDIDA'
    GROUP BY anio, mes, medico_id
),
ranking AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY anio, mes
               ORDER BY atendidas DESC
           ) AS puesto
    FROM por_medico
)
SELECT r.anio, r.mes, m.apellido, r.atendidas
FROM ranking r
JOIN medicos m ON m.id = r.medico_id
WHERE r.puesto = 1
ORDER BY r.anio, r.mes;

PARTITION BY anio, mes reinicia el ranking en cada mes. Es como hacer un GROUP BY que no colapsa: cada fila sigue apareciendo, pero el cálculo se hace dentro de cada partición.

LAG / LEAD: la fila anterior y la siguiente

-- cuánto tiempo pasó entre cada pago del mismo paciente:
SELECT c.paciente_id, pg.fecha, pg.monto,
       LAG(pg.fecha) OVER (
           PARTITION BY c.paciente_id
           ORDER BY pg.fecha
       ) AS fecha_anterior,
       pg.fecha - LAG(pg.fecha) OVER (
           PARTITION BY c.paciente_id
           ORDER BY pg.fecha
       ) AS dias_desde_anterior
FROM pagos pg
JOIN citas c ON c.id = pg.cita_id
WHERE pg.estado = 'PAGADO'
ORDER BY c.paciente_id, pg.fecha
LIMIT 15;

LAG(col) accede a la fila ANTERIOR de la ventana; LEAD(col) a la SIGUIENTE. Sin subconsultas, sin self-JOINs, sin dolor. La resta de fechas es nativa de PostgreSQL (no necesita DATEDIFF como MySQL).

SUM() OVER: acumulado corrido

-- acumulado de pagos por día de 2026:
SELECT pg.fecha,
       pg.monto,
       SUM(pg.monto) OVER (ORDER BY pg.fecha
                           ROWS BETWEEN UNBOUNDED PRECEDING
                           AND CURRENT ROW) AS acumulado
FROM pagos pg
WHERE pg.estado = 'PAGADO'
  AND pg.fecha >= '2026-01-01'
ORDER BY pg.fecha;

SUM() OVER (ORDER BY fecha ROWS BETWEEN ...) crea un acumulado corrido. La cláusula ROWS BETWEEN define el rango de la ventana: "desde la primera fila hasta la actual". Es el equivalente al running total que en MySQL requiere variables.

El capítulo que la serie venía prometiendo. Los triggers conectan TODO: cualquier UPDATE sobre pacientes queda firmado automáticamente con current_setting('app.usuario') + session_user — sin que el programador recuerde llamar a nada. La auditoría dual, por fin real.

• Crear triggers AFTER para auditar INSERT/UPDATE/DELETE.
• Usar NEW y OLD según el evento.
• Cancelar operaciones inválidas con BEFORE + RAISE EXCEPTION.
• Probar la auditoría dual completa de punta a punta.

La anatomía

CREATE TRIGGER nombre
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON tabla
FOR EACH ROW
EXECUTE FUNCTION funcion_trigger();

Tres decisiones: CUÁNDO (BEFORE = antes de escribir, puede cancelar; AFTER = después, la fila ya es final), QUÉ EVENTO (uno por trigger), y FOR EACH ROW (el trigger corre por cada fila afectada). La diferencia con MySQL: PostgreSQL ejecuta una FUNCIÓN, no un bloque anónimo.

La auditoría dual de pacientes

-- función que el trigger invocará:
CREATE OR REPLACE FUNCTION fn_audit_pacientes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO auditoria (tabla_afectada, operacion,
                               usuario_app, usuario_bd,
                               registro_id, fecha_hora)
        VALUES ('pacientes', 'INSERT',
                current_setting('app.usuario', true),
                session_user,
                NEW.id, NOW());
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO auditoria (tabla_afectada, operacion,
                               usuario_app, usuario_bd,
                               registro_id, fecha_hora,
                               datos_anteriores, datos_nuevos)
        VALUES ('pacientes', 'UPDATE',
                current_setting('app.usuario', true),
                session_user,
                NEW.id, NOW(),
                'tel=' || OLD.telefono || '; activo=' || OLD.activo,
                'tel=' || NEW.telefono || '; activo=' || NEW.activo);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO auditoria (tabla_afectada, operacion,
                               usuario_app, usuario_bd,
                               registro_id, fecha_hora)
        VALUES ('pacientes', 'DELETE',
                current_setting('app.usuario', true),
                session_user,
                OLD.id, NOW());
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$;

-- crear los 3 triggers:
CREATE TRIGGER trg_pacientes_ins
AFTER INSERT ON pacientes
FOR EACH ROW EXECUTE FUNCTION fn_audit_pacientes();

CREATE TRIGGER trg_pacientes_upd
AFTER UPDATE ON pacientes
FOR EACH ROW EXECUTE FUNCTION fn_audit_pacientes();

CREATE TRIGGER trg_pacientes_del
AFTER DELETE ON pacientes
FOR EACH ROW EXECUTE FUNCTION fn_audit_pacientes();

TG_OP es una variable especial de PostgreSQL: contiene 'INSERT', 'UPDATE' o 'DELETE' según el evento. Esto permite UNA función para los 3 triggers — más limpio que MySQL que necesita 3 funciones separadas (una por evento).

La prueba de fuego

-- la app declara quién opera (cap 25: set_config):
SELECT set_config('app.usuario', 'rrosa', false);

-- cualquier UPDATE queda firmado:
UPDATE pacientes SET telefono = '987999888' WHERE id = 1;

-- verificar:
SELECT usuario_app, usuario_bd, operacion,
       datos_anteriores, datos_nuevos, fecha_hora
FROM auditoria ORDER BY id DESC LIMIT 1;
-- rrosa | postgres | UPDATE
-- tel=987111999; activo=true | tel=987999888; activo=true | ...

current_setting('app.usuario', true) lee la variable de sesión (silenciosa si no existe, devuelve NULL). session_user es la cuenta de conexión (equivalente a CURRENT_USER() de MySQL). La dupla completa: quién opera + con qué cuenta.

BEFORE + RAISE EXCEPTION: el guardián que cancela

CREATE OR REPLACE FUNCTION fn_valida_pacientes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.sexo IS NOT NULL AND NEW.sexo NOT IN ('M', 'F') THEN
        RAISE EXCEPTION 'sexo debe ser M o F'
            USING ERRCODE = 'P0001';
    END IF;

    IF NEW.fecha_nacimiento > CURRENT_DATE THEN
        RAISE EXCEPTION 'fecha_nacimiento no puede ser futura'
            USING ERRCODE = 'P0001';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_pacientes_valida
BEFORE INSERT ON pacientes
FOR EACH ROW EXECUTE FUNCTION fn_valida_pacientes();

Si un trigger BEFORE lanza RAISE EXCEPTION, la sentencia original SE CANCELAN — la fila jamás se inserta. Es la capa de validación más fuerte que existe: ni el programador más distraído puede saltársela, porque no vive en ningún programa.

Reglas de convivencia

Cierre de Parte V. Cinco reportes REALES que combinan todo lo aprendido: funciones de ventana, CTEs, PL/pgSQL, triggers y la tabla auditoria. Sin teoría nueva — práctica pura.

• Reporte de productividad con ROW_NUMBER por especialidad.
• Acumulado mensual con SUM() OVER.
• Detección de anomalías con LAG/LEAD.
• Trigger de validación BEFORE con SET de columnas.
• La auditoría dual completa: set_config → trigger → consulta.

1 · Productividad por mes y especialidad (ventanas)

WITH citas_medico AS (
    SELECT EXTRACT(YEAR FROM c.fecha_hora)  AS anio,
           EXTRACT(MONTH FROM c.fecha_hora) AS mes,
           e.nombre AS especialidad,
           m.apellido,
           COUNT(*) AS atendidas
    FROM citas c
    JOIN medicos m ON m.id = c.medico_id
    JOIN especialidades e ON e.id = m.especialidad_id
    WHERE c.estado = 'ATENDIDA'
    GROUP BY anio, mes, e.nombre, m.apellido
),
ranking AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY anio, mes, especialidad
               ORDER BY atendidas DESC
           ) AS puesto
    FROM citas_medico
)
SELECT anio, mes, especialidad, apellido, atendidas
FROM ranking
WHERE puesto <= 3
ORDER BY anio, mes, especialidad, puesto;

CTE encadenada + PARTITION BY + filtros. El reporte que la administración muestra en su reunión mensual: quiénes lideran cada especialidad, cada mes.

2 · Acumulado de pagos con SUM() OVER

SELECT fecha, monto, metodo,
       SUM(monto) OVER (ORDER BY fecha
                        ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW) AS acumulado
FROM pagos
WHERE estado = 'PAGADO'
  AND fecha >= '2026-01-01'
ORDER BY fecha;

El acumulado que MySQL hace con variables de sesión (o subconsultas), PG lo hace en una línea con OVER. Es el caso de uso de OVER más directo.

3 · Detección de pagos atípicos con LAG

WITH pagos_con_anterior AS (
    SELECT pg.fecha, pg.monto,
           LAG(pg.monto) OVER (ORDER BY pg.fecha) AS monto_anterior
    FROM pagos pg
    WHERE pg.estado = 'PAGADO'
)
SELECT fecha, monto, monto_anterior,
       CASE
         WHEN monto_anterior IS NULL THEN NULL
         WHEN monto > monto_anterior * 2 THEN 'atípico alto'
         WHEN monto < monto_anterior * 0.5 THEN 'atípico bajo'
         ELSE 'normal'
       END AS clasificacion
FROM pagos_con_anterior
WHERE monto_anterior IS NOT NULL
ORDER BY ABS(monto - monto_anterior) DESC
LIMIT 10;

LAG + CASE: detecta pagos que se duplican o mitad respecto al anterior. En MySQL esto requiere variables de sesión; en PG es CTE + ventana + expresión CASE, todo declarativo.

4 · Trigger BEFORE: normalización automática

CREATE OR REPLACE FUNCTION fn_normaliza_pacientes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.nombre   := TRIM(INITCAP(NEW.nombre));
    NEW.apellido := TRIM(INITCAP(NEW.apellido));
    NEW.telefono := REPLACE(REPLACE(NEW.telefono, ' ', ''), '-', '');
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_pacientes_normaliza
BEFORE INSERT OR UPDATE ON pacientes
FOR EACH ROW EXECUTE FUNCTION fn_normaliza_pacientes();

INITCAP capitaliza la primera letra; TRIM quita espacios; REPLACE limpia guiones y espacios del teléfono. El trigger BEFORE NORMALIZA antes de que el dato se escriba — la app no necesita preocuparse por limpiar entradas.

5 · La auditoría dual completa: de punta a punta

-- 1) la app abre conexión y declara quién opera:
SELECT set_config('app.usuario', 'rrosa', false);

-- 2) un cambio se registra automáticamente:
UPDATE pacientes SET telefono = '999888777' WHERE id = 2;

-- 3) la auditoría lo captura sin que nadie llame a nada:
SELECT operacion, usuario_app, usuario_bd,
       datos_anteriores, datos_nuevos
FROM auditoria
WHERE tabla_afectada = 'pacientes'
ORDER BY id DESC LIMIT 1;
-- UPDATE | rrosa | postgres
-- tel=...; activo=true | tel=999888777; activo=true

El cierre de todo el manual. No es teoría nueva: es ver TODO lo que construiste — desde el primer CREATE TABLE hasta el trigger de auditoría — funcionando como un sistema completo. El recorrido de 28 capítulos en una mirada.

• El recorrido completo en una tabla: cada cap, una pieza del rompecabezas.
• El diagrama ER final y por qué cada constraint importa.
• La seguridad en 5 capas que ya está activa.
• Siguientes pasos: phpBB, pg_dump, interop y PHP.

El rompecabezas armado

El diagrama ER final

8 tablas, 7 FKs, 1 CHECK, 3 UNIQUE, 3 DEFAULT, 1 SERIAL. Cada constraint del cap 8 tiene una razón de ser: las FKs frenan huérfanos, el CHECK impide montos negativos, los UNIQUE evitan dobles agendas, los DEFAULT llenan timestamps y estados. Sin estas reglas, el motor no puede protegerte.

La seguridad en 5 capas

Siguientes pasos

Un backup sin restore no es un backup. pg_dump exporta tu base en SQL puro (o binario comprimido); pg_restore la reconstruye en otro servidor. Hoy aprendemos a dormir tranquilo.

• Exportar con pg_dump en formato SQL y formato custom (-Fc).
• Restaurar con psql (SQL) y pg_restore (custom).
• Filtrar tablas específicas con -t y excluir con -T.
• Scheduler de backups: el patrón crontab de la clínica.

pg_dump: exportar la base

# backup completo en SQL puro (legible, sin compresión):
pg_dump -U admin -d clinica -f clinica_backup.sql

# backup comprimido en formato custom (el recomendado):
pg_dump -U admin -d clinica -Fc -f clinica_backup.dump

# solo la estructura (sin datos):
pg_dump -U admin -d clinica --schema-only -f clinica_estructura.sql

# solo datos (sin CREATE TABLE):
pg_dump -U admin -d clinica --data-only -f clinica_datos.sql

# solo tablas específicas:
pg_dump -U admin -d clinica -t pacientes -t citas -Fc -f clinica_pacientes.dump

-Fc produce un archivo comprimido que pg_restore puede leer con filtros (restaurar solo ciertas tablas, por ejemplo). El SQL puro es legible para humanos pero no admite filtros en la restauración.

pg_restore: reconstruir la base

# restaurar en una base NUEVA (debe existir):
createdb -U admin clinica_nueva
pg_restore -U admin -d clinica_nueva -Fc clinica_backup.dump

# restaurar solo ciertas tablas:
pg_restore -U admin -d clinica_nueva -t pacientes -t citas clinica_backup.dump

# restaurar solo el esquema (estructura):
pg_restore -U admin -d clinica_nueva --schema-only clinica_backup.dump

# restore completo desde SQL puro:
psql -U admin -d clinica_nueva -f clinica_backup.sql

pg_restore es más potente que psql para restore: acepta filtros (-t, -T), puede saltarse errores (--if-exists), y muestra progreso. Para archivos SQL puros, psql es la única opción.

El patrón de la clínica: backup diario

#!/bin/bash
# /usr/local/bin/backup_clinica.sh
FECHA=$(date +%Y%m%d_%H%M)
DIR=/var/backups/pg
pg_dump -U admin -Fc -f "$DIR/clinica_$FECHA.dump" clinica

# limpiar backups de más de 30 días:
find "$DIR" -name "clinica_*.dump" -mtime +30 -delete

echo "Backup clinica_$FECHA.dump completado"

# en crontab (ejecutar todos los días a las 2:00 AM):
0 2 * * * /usr/local/bin/backup_clinica.sh >> /var/log/pgbackup.log 2>&1

Backups automáticos con retención de 30 días. El dump con formato custom (-Fc) es comprimido (ocupa ~30% del original) y permite restauración parcial.

pg_dumpall: todas las bases de un golpe

# backup de TODAS las bases + roles + tablespaces:
pg_dumpall -U admin -f todas_las_bases.sql

# restaurar TODO:
psql -U admin -f todas_las_bases.sql

pg_dumpall exporta todo el servidor (todas las bases, los roles, los tablespaces). No soporta formato custom, solo SQL. Útil para migración de servidor completo.

phpBB es el foro open source más grande del mundo, y su base de datos usa PostgreSQL. Hoy desarmamos su esquema para ver TODO lo aprendido funcionando en un proyecto real: tablas, relaciones, usuarios, roles, permisos y audit trails.

• Explorar el esquema de phpBB con \dt y \d+ tabla.
• Entender el modelo de usuarios, foros y permisos.
• Vincular roles con permisos granulares (como el cap 24).
• Comparar el modelo phpBB con el de la clínica.

El esquema de phpBB

# importar la base de phpBB (SQL incluido en la distribución):
createdb -U admin phpbb_demo
psql -U admin -d phpbb_demo -f phpBB/install/schemas/postgres_schema.sql

# explorar:
psql -U admin -d phpbb_demo

\dt                          -- listar tablas
\d phpbb_users               -- estructura de usuarios
\d phpbb_forums              -- estructura de foros
\d phpbb_acl_roles           -- roles de permisos
\d phpbb_acl_permissions     -- permisos individuales

El modelo de usuarios

-- usuarios de phpBB (simplificado):
SELECT user_id, username, user_regdate, user_type,
       user_posts, user_colour
FROM phpbb_users
ORDER BY user_id
LIMIT 5;

phpBB usa user_type (0 = normal, 1 = admin, 2 = moderador) en lugar de una columna "rol" directa. Los roles y permisos viven en tablas separadas: es el modelo EAV (Entity-Attribute-Value) para permisos, más flexible pero más complejo que el GRANT directo del cap 24.

Roles y permisos: el sistema ACL

-- roles disponibles:
SELECT role_id, role_name, role_description
FROM phpbb_acl_roles
WHERE role_type = 'f'   -- roles de foro (f=forum, u=user, m=forum global)
LIMIT 10;

-- permisos individuales:
SELECT auth_option_id, auth_option
FROM phpbb_acl_options
WHERE auth_option LIKE 'f_%'   -- permisos de foro
LIMIT 10;

-- asignación: qué role tiene qué permiso en qué foro:
SELECT a.forum_id, r.role_name, o.auth_option,
       a.auth_setting   -- 1 = permitido, 0 = denied
FROM phpbb_acl a
JOIN phpbb_acl_roles r ON r.role_id = a.role_id
JOIN phpbb_acl_options o ON o.auth_option_id = a.auth_option_id
WHERE a.forum_id = 1
LIMIT 15;

El sistema ACL de phpBB es más granular que GRANT de PostgreSQL: permite "permitir" o "denegar" permisos ESPECÍFICOS (f_read, f_post, f_reply, f_edit, f_delete) por foro, por role. Es el equivalente a GRANT SELECT ON vista_a VS. GRANT SELECT, INSERT ON tabla_b — pero con una tabla de relación tripartita (foro + role + permiso).

La comparación con la clínica

phpBB registra la auditoría en PHP (no con triggers) — es menos seguro pero más simple. Nuestro modelo de clínica con triggers es más robusto: el motor firma sin depender de que el programador lo recuerde.

El capítulo que conecta los dos manuales del curso. Si mañana te piden migrar una app de MySQL a PostgreSQL (o al revés), este capítulo es tu mapa de carreteras: qué cambia, qué se queda igual, y cómo mover datos entre los dos motores.

• Tabla de diferencias sintácticas: qué cambiar en cada consulta.
• Migrar datos con pg_dump/psql o COPY.
• Detectar y adaptar extensiones MySQL en código PG.
• Estrategia de migración de una app real.

Las diferencias que importan

Migrar datos: del dump de MySQL al restore de PG

# paso 1: exportar de MySQL:
mysqldump -u root -p clinica --no-create-info > clinica_datos.sql

# paso 2: adaptar la sintaxis (buscar-reemplazar las diferencias clave):
# - AUTO_INCREMENT → SERIAL
# - NOW() → NOW() (igual)
# - CONCAT() → ||
# - DATE_FORMAT() → TO_CHAR()

# paso 3: importar en PostgreSQL:
psql -U admin -d clinica_nueva -f clinica_datos_adaptado.sql

Para bases grandes, COPY es más rápido que INSERT individuales:

# exportar de MySQL a CSV:
mysql -u root -p clinica -e "SELECT * FROM pacientes" --batch --raw > pacientes.csv

# importar en PostgreSQL con COPY (40x más rápido que INSERT):
psql -U admin -d clinica_nueva -c "\COPY pacientes FROM 'pacientes.csv' CSV HEADER"

El truco del DSN doble en PHP

<?php
// detector automático del motor activo:
$es_pg = str_contains($dsn, 'pgsql:');

// si necesitas SQL condicional:
$truncate = $es_pg ? 'TRUNCATE TABLE pagos CASCADE' : 'TRUNCATE TABLE pagos';
$bool_true = $es_pg ? 'TRUE' : '1';

// o mejor: abstrae con un wrapper que esconda las diferencias
function db_bool($pdo, $valor) {
    return $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'pgsql'
        ? ($valor ? 'TRUE' : 'FALSE')
        : ($valor ? '1' : '0');
}

El pgsql: en el DSN (no "postgres:" ni "postgresql:") es el código oficial del driver PDO. PDO::ATTR_DRIVER_NAME te dice en qué motor estás conectado — útil para adaptar el SQL en tiempo de ejecución.

La estrategia de migración real

¿Cuánto tarda realmente tu consulta? ¿La usa el índice? ¿Qué tablas son las más consultadas? EXPLAIN ANALYZE responde la primera; pg_stat_activity la segunda; pgBadger las resume en un reporte HTML que puedes enviarle al equipo.

• EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT/JSON): el plan real.
• pg_stat_activity: quién está corriendo QUÉ ahora mismo.
• pg_stat_user_tables: las tablas más consultadas del día.
• pgBadger: el reporte HTML automático del log.

EXPLAIN ANALYZE en profundidad

-- el plan real con tiempos:
EXPLAIN ANALYZE
SELECT c.id, p.apellido, p.nombre
FROM citas c
JOIN pacientes p ON p.id = c.paciente_id
WHERE c.estado = 'ATENDIDA'
  AND c.fecha_hora >= '2026-01-01';

-- resultado típico:
-- Hash Join  (cost=... actual time=0.3..2.1 rows=45)
--   Hash Filter: (c.estado = 'ATENDIDA')
--   -> Seq Scan on citas c  (actual time=0.01..0.8 rows=200)
-- Planning Time: 0.2 ms
-- Execution Time: 2.5 ms

ANALYZE ejecuta la consulta y muestra tiempos reales. BUFFERS añade cuántas páginas leyó de disco vs. memoria compartida:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM pagos WHERE paciente_id = 3;

Si ves shared hit alto (memoria), todo va bien. Si ves shared read alto (disco), necesitas más RAM o mejores índices.

pg_stat_activity: el monitor en vivo

-- ¿quién está conectado y qué hace AHORA?
SELECT pid, usename, state, query,
       NOW() - query_start AS duracion
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duracion DESC;

-- matar una consulta zombi (equivalente a KILL de MySQL):
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes'
  AND query NOT LIKE '%pg_stat_activity%';

pg_stat_activity es el equivalente de SHOW PROCESSLIST de MySQL, pero más poderoso: es una tabla que puedes consultar con SQL, filtrar y ordenar.

pg_stat_user_tables: las más consultadas

-- tablas con más lecturas (índices que necesitan estadísticas actualizadas):
SELECT relname,
       seq_scan, idx_scan,
       n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan + idx_scan DESC
LIMIT 10;

Si una tabla tiene muchos seq_scan y pocos idx_scan, es candidata a un nuevo índice (cap 23). Si tiene muchos n_tup_upd, sus índices se están actualizando constantemente — el costo de tener demasiados.

pgBadger: el reporte automático

# pgBadger genera un reporte HTML desde el log del servidor:
# 1) activar logging en postgresql.conf:
#    log_statement = 'all'   (o 'mod' para cambios)
#    log_destination = 'stderr'

# 2) generar el reporte:
pgbadger /var/log/postgresql/postgresql-17-main.log -o reporte.html

# 3) abrir en navegador: un reporte con gráficos de:
#    - Queries más lentas
#    - Distribución de tipos de consulta
#    - Errores más comunes
#    - Tiempos de ejecución promedio

Un solo servidor es un punto único de fallo. La replicación manda los datos a otro servidor en tiempo real: si el primero muere, el segundo toma el relevo. Hoy los tres tipos de replicación de PostgreSQL y cuándo usar cada uno.

• Streaming replication: copia completa en tiempo real (WAL).
• Logical replication: solo ciertas tablas,.pub/sub.
• Replicación a MySQL: el puente entre los dos motores.
• El script de failover de la clínica.

Streaming replication: el clásico

# en el SERVIDOR PRIMARIO (postgresql.conf):
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1024  # MB

# en pg_hba.conf: permitir al replicador conectarse:
# host  replicacion  replicador  192.168.1.0/24  scram-sha-256

# crear el usuario de replicación:
CREATE ROLE replicador LOGIN REPLICATION PASSWORD 'Repl2026!';

# en el SERVIDOR SECUNDARIO:
pg_basebackup -h 192.168.1.10 -U replicador -D /var/lib/postgresql/17/main -P

# crear standby.signal:
touch /var/lib/postgresql/17/main/standby.signal

# en postgresql.primary_conninfo del secundario:
# 'host=192.168.1.10 port=5432 user=replicador password=Repl2026!'

# reiniciar el secundario: ¡ya está replicando!

Streaming replication copia TODO: cada INSERT, UPDATE, DELETE del primario llega al secundario en tiempo real a través del WAL (Write-Ahead Log). Es la replicación más simple y confiable: el secundario es un espejo exacto.

Logical replication: selectiva y flexible

-- en el PRIMARIO: crear una publicación (qué tablas replicar):
CREATE PUBLICATION clinica_pub FOR TABLE pacientes, citas;

-- o publicar TODO:
-- CREATE PUBLICATION clinica_pub FOR ALL TABLES;

-- en el SECUNDARIO: crear suscripción (de dónde viene):
CREATE SUBSCRIPTION clinica_sub
  CONNECTION 'host=192.168.1.10 dbname=clinica user=replicador password=Repl2026!'
  PUBLICATION clinica_pub;

Logical replication es más selectiva: puedes replicar SOLO ciertas tablas, desde un servidor a otro, e incluso entre versiones diferentes de PostgreSQL. Es ideal para migraciones (el viejo sigue funcionando mientras el nuevo se llena de datos).

Streaming vs Logical: cuándo usar cada una

El script de failover de la clínica

#!/bin/bash
# failover_clinica.sh — promover secundario a primario
# EJECUTAR SOLO si el primario está MUERTO

SECUNDARIO="192.168.1.11"
echo "Promoviendo $SECUNDARIO a primario..."

# en el secundario:
ssh admin@$SECUNDARIO "pg_ctl promote -D /var/lib/postgresql/17/main"

# verificar:
ssh admin@$SECUNDARIO "psql -U admin -d clinica -c 'SELECT pg_is_in_recovery();'"
# resultado: f (false = ya no es secundario, es primario)

echo "Failover completado. Actualiza la app para apuntar a $SECUNDARIO"

Meta del manual. Mapa final, examen de graduación y el ANEXO de consulta permanente: los tipos y sintaxis de PostgreSQL frente a los otros tres motores — con esta columna resaltada.

• Repasar las 6 partes como un solo sistema.
• Aprobar el examen de graduación.
• Consultar el anexo de equivalencias (PostgreSQL resaltada).
• Saber el camino: MySQL → SQL Server → SQLite.

El mapa final

EXAMEN DE GRADUACIÓN

• [ ] La base clinica creada desde cero: 8 tablas, constraints, BOOLEAN, CHECK.
• [ ] Datos deterministas cargados y las 5 consultas del cap 28 dando los mismos resultados.
• [ ] fn_validar_documento rechazando documentos cortos con RAISE EXCEPTION.
• [ ] fn_edad y fn_saldo_paciente funcionando en SELECT y WHERE.
• [ ] Triggers de auditoría firmando la dupla current_setting('app.usuario') + session_user.
• [ ] La cuenta de la app sin acceso a tablas — solo vistas y EXECUTE.
• [ ] Un script PHP con PDO: login, set_config, transacción completa.
• [ ] EXPLAIN ANALYZE sin Seq Scan en las consultas calientes.
• [ ] Backup con pg_dump -Fc restaurado con pg_restore y verificado.

ANEXO · Equivalencias entre motores (PostgreSQL resaltada)

Guarda este anexo: es la chuleta oficial de la categoría. Cuando abras el manual de MySQL, la misma tabla volverá con su columna resaltada.