Tutorial de PostgreSQL con ejemplos ejecutables
Un tutorial gratuito de PostgreSQL con consultas que se ejecutan en la página: tipos de datos, CREATE TABLE, upserts, JSONB, fechas, CTE, funciones de ventana, índices y VACUUM. Edita la consulta y vuelve a ejecutarla.
Empezar el cursoPrimeros pasos
- PostgreSQL vs MySQLPostgreSQL y MySQL son bases de datos relacionales gratuitas. PostgreSQL tiene un SQL más completo, JSONB y DDL transaccional; MySQL es más sencillo de usar donde ya es la opción por defecto.
- PostgreSQL vs SQL ServerPostgreSQL es gratuito y de código abierto; SQL Server es la base de datos comercial de Microsoft, con T-SQL y herramientas centradas en Windows. Así se diferencian su SQL, JSON, bloqueos y licencias.
- PostgreSQL vs MongoDBPostgreSQL guarda filas en tablas con un esquema fijo y puede guardar documentos JSON en columnas jsonb; MongoDB guarda documentos de esquema flexible en colecciones y los reparte entre servidores.
- Listar bases de datos y tablasEn psql, \l lista las bases de datos, \c cambia a una y \dt lista sus tablas. En SQL, consulta pg_database, information_schema.tables e information_schema.columns.
Tablas y tipos
- Tipos de datosLos tipos de datos de PostgreSQL que vas a usar en casi todas las columnas: enteros, numeric, text, boolean, timestamptz, uuid, jsonb, arrays y enums, y cuándo elegir cada uno.
- CREATE TABLECREATE TABLE define las columnas, los tipos y las restricciones de una tabla. Incluye claves identity, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS y LIKE.
- ALTER TABLEALTER TABLE cambia una tabla que ya existe: agregar, renombrar y eliminar columnas, cambiar el tipo de una columna con USING, poner valores por defecto y NOT NULL, y añadir restricciones.
- Claves primariasCómo definir una clave primaria en PostgreSQL y cómo generarla: columnas identity, el antiguo SERIAL y UUID con gen_random_uuid(). También claves compuestas y cómo reiniciar una secuencia.
- Claves foráneasUna clave foránea hace que una columna solo acepte valores que existen en otra tabla. Incluye REFERENCES, ON DELETE CASCADE, SET NULL y RESTRICT, añadir una clave a una tabla existente e indexarla.
- Tipos enumUn tipo enum es una lista fija y ordenada de valores permitidos, creada con CREATE TYPE ... AS ENUM. Cómo añadir y renombrar valores, listarlos, ordenarlos y cuándo conviene más una restricción CHECK.
- Timestampstimestamptz guarda un instante y lo muestra en la zona horaria de la sesión; timestamp guarda una hora de reloj sin zona. Ninguno guarda la zona que escribiste. Usa timestamptz para casi todo.
Consultas
- JoinsUn join combina filas de dos tablas según una condición. INNER JOIN conserva las coincidencias, LEFT JOIN todas las filas de la primera tabla, FULL JOIN las de ambas, CROSS JOIN empareja todo y LATERAL ejecuta una subconsulta por fila.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort se queda con la primera fila de cada grupo, como el último pedido de cada cliente. Solo existe en PostgreSQL.
- COALESCE, NULLIF y CASECOALESCE(a, b, ...) devuelve el primer argumento que no es NULL, NULLIF(a, b) convierte un valor en NULL, y CASE WHEN elige un valor según una condición.
- LIKE, ILIKE y regexLIKE compara texto con un patrón usando % y _, ILIKE hace lo mismo sin distinguir mayúsculas, y los operadores ~ aplican expresiones regulares POSIX.
- CAST y ::CAST(value AS type) y value::type convierten un valor a otro tipo en PostgreSQL. Son la misma conversión; :: es la sintaxis corta de PostgreSQL.
Modificar datos
- Upsert (ON CONFLICT)Upserts en PostgreSQL con INSERT ... ON CONFLICT: DO UPDATE con EXCLUDED, DO NOTHING, actualizaciones condicionales, contadores y cómo distinguir filas insertadas de actualizadas.
- RETURNINGRETURNING hace que INSERT, UPDATE, DELETE y MERGE devuelvan las filas que cambiaron: ids generados, valores nuevos, filas borradas y qué acción de MERGE se ejecutó.
- TransaccionesCómo funcionan las transacciones en PostgreSQL: BEGIN, COMMIT y ROLLBACK, savepoints, qué le hace un error a una transacción abierta y los niveles de aislamiento.
JSON y arrays
- JSONBGuarda y consulta JSON en PostgreSQL con jsonb: los operadores -> y ->>, la contención con @>, jsonb_set y otras actualizaciones, convertir arrays en filas, índices GIN y json vs jsonb.
- ArraysColumnas de tipo array en PostgreSQL: cómo declararlas, índices desde 1, comprobar si un array contiene un valor con ANY, @> y &&, unnest y las funciones de arrays que necesitas.
- string_agg y array_aggstring_agg une los valores de un grupo en un solo texto y array_agg los reúne en un array. Orden, DISTINCT, FILTER, manejo de NULL y los errores más comunes.
Cadenas y fechas
- Funciones de textoLas funciones de texto de PostgreSQL que usas a diario: substring, left y right, || y concat, split_part, position, replace, regexp_replace, trim, lpad y format.
- Funciones de fechaTrabaja con fechas en PostgreSQL: now() y current_date, sumar y restar intervalos, date_trunc para agrupar, extract y age, y formatear con to_char.
- generate_seriesgenerate_series devuelve un conjunto de filas desde un valor inicial hasta uno final: números, fechas y timestamps. Úsalo para rellenar huecos en informes, generar datos de prueba y construir calendarios.
Consultas avanzadas
- CTE (WITH)Una expresión de tabla común da nombre a una subconsulta en una cláusula WITH para que la consulta principal la lea como una tabla. WITH RECURSIVE permite que se refiera a sí misma para recorrer árboles y grafos.
- Funciones de ventanaLas funciones de ventana calculan un valor para cada fila a partir de un grupo de filas relacionadas sin agruparlas: rankings, totales acumulados, medias móviles y comparaciones con la fila anterior.
Rendimiento y mantenimiento
- ÍndicesCREATE INDEX crea por defecto un índice B-tree para que PostgreSQL encuentre filas sin leer toda la tabla. Otros tipos (GIN, GiST, BRIN, hash) cubren JSONB, arrays, búsqueda de texto completo, rangos y tablas muy grandes.
- EXPLAIN ANALYZEEXPLAIN muestra el plan que PostgreSQL elige para una consulta, y EXPLAIN ANALYZE ejecuta la consulta y añade las filas y los tiempos reales de cada paso. Aprende a leer el árbol del plan y los nodos más comunes.
- Vistas y vistas materializadasUna vista es una consulta guardada que se ejecuta cada vez que la lees. Una vista materializada guarda el resultado de la consulta y solo cambia cuando ejecutas REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM elimina las versiones muertas de filas que dejan UPDATE y DELETE para que su espacio se pueda reutilizar. Autovacuum lo ejecuta por ti; esta página muestra cuándo, cómo ajustarlo y cuándo VACUUM FULL compensa su bloqueo.
Funciones y triggers
- Funciones y procedimientosCREATE FUNCTION define una función que llamas dentro de las consultas, escrita en SQL o PL/pgSQL. CREATE PROCEDURE define una rutina que ejecutas con CALL y que puede hacer commit a medida que avanza.
- TriggersUn trigger ejecuta una función automáticamente cuando se insertan, actualizan o borran filas. En PostgreSQL escribes una función de trigger que devuelve trigger y la asocias a una tabla con CREATE TRIGGER.