Cheatsheet PostgreSQL

SELECT * FROM clientes;

SELECT nombre, email FROM clientes;

SELECT COUNT(*) FROM clientes;

SELECT * devuelve todas las columnas. Especifica columnas para mejor rendimiento. COUNT(*) cuenta filas. Evita * en producción — lista solo lo necesario.

SELECT * FROM productos
LIMIT 10;

SELECT * FROM productos
LIMIT 10 OFFSET 20;

-- Página 3 (20 por página):
SELECT * FROM productos
ORDER BY id
LIMIT 20 OFFSET 40;

LIMIT restringe el número de filas. OFFSET salta N filas (paginación). Usa siempre ORDER BY con paginación para resultados consistentes. Para datasets grandes, prefiere keyset pagination.

SELECT nombre FROM clientes
UNION
SELECT nombre FROM proveedores;

SELECT nombre FROM clientes
UNION ALL
SELECT nombre FROM proveedores;

UNION combina resultados eliminando duplicados. UNION ALL los mantiene todos (más rápido). Ambas consultas deben tener el mismo número de columnas y tipos compatibles. Prefiere UNION ALL si no necesitas deduplicación.

SELECT nombre AS cliente, email AS contacto
FROM clientes AS c;

SELECT c.nombre, p.total
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id;

AS renombra columnas o tablas temporalmente. El alias de tabla (c) simplifica los JOINs. En PostgreSQL, AS es opcional para tablas pero recomendado por claridad.

SELECT nombre || ' ' || apellido AS completo
FROM clientes;

SELECT CONCAT(nombre, ' ', apellido)
FROM clientes;

SELECT FORMAT('¡Hola, %s!', nombre)
FROM clientes;

El operador || concatena cadenas. CONCAT() ignora los NULL automáticamente. FORMAT() usa marcadores estilo %s. Si un operando de || es NULL, el resultado es NULL.

-- Alternativa estándar a LIMIT:
SELECT * FROM productos
ORDER BY precio DESC
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM productos
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

FETCH FIRST N ROWS ONLY es el estándar SQL (equivalente a LIMIT). OFFSET ... ROWS sustituye a OFFSET. Ambos funcionan en PostgreSQL. Útil para portabilidad entre SGBD. Requiere ORDER BY.

SELECT DISTINCT ciudad FROM clientes;

SELECT DISTINCT ON (ciudad)
    ciudad, nombre
FROM clientes
ORDER BY ciudad, creado_en DESC;

DISTINCT elimina filas duplicadas. DISTINCT ON es exclusivo de PostgreSQL — devuelve la primera fila de cada grupo. Combínalo con ORDER BY para controlar qué fila se elige.

SELECT precio * cantidad AS total
FROM items;

SELECT precio * 1.23 AS con_iva
FROM productos;

SELECT ROUND(precio * 1.23, 2) AS redondeado
FROM productos;

Los operadores +, -, *, / funcionan en columnas numéricas. ROUND() controla los decimales. Usa NUMERIC para dinero — nunca REAL o FLOAT para valores monetarios.

SELECT * FROM productos
ORDER BY precio DESC;

SELECT * FROM clientes
ORDER BY ciudad ASC, nombre ASC;

SELECT * FROM pedidos
ORDER BY creado_en DESC NULLS LAST;

ORDER BY ordena los resultados. ASC es el valor por defecto, DESC lo invierte. NULLS LAST coloca los nulos al final (por defecto en ASC van primero). Múltiples columnas se separan por comas.

SELECT nombre FROM clientes
WHERE id IN (
    SELECT cliente_id FROM pedidos
    WHERE total > 100
);

SELECT nombre, (
    SELECT COUNT(*) FROM pedidos p
    WHERE p.cliente_id = c.id
) AS num_pedidos
FROM clientes c;

Las subconsultas son consultas dentro de consultas. IN (SELECT ...) filtra por un conjunto. Las subconsultas correlacionadas referencian la consulta externa (c.id). Para rendimiento, prefiere JOINs o CTEs cuando sea posible.

SELECT * FROM productos
WHERE precio > 100;

SELECT * FROM clientes
WHERE activo = true;

SELECT * FROM pedidos
WHERE total >= 50.00;

WHERE filtra filas antes de devolver. Operadores: =, >, <, >=, <=, <> (distinto). Los booleanos pueden omitir = true: WHERE activo.

WHERE nombre LIKE 'Ana%';     -- empieza con
WHERE nombre LIKE '%silva%';  -- contiene
WHERE nombre LIKE '_na';      -- 2ª letra = n
WHERE nombre ILIKE 'ana%';    -- case-insensitive

-- % = cualquier secuencia
-- _ = exactamente un carácter

LIKE hace pattern matching con comodines. % = cero o más chars, _ = un char. ILIKE es exclusivo de PostgreSQL — ignora mayúsculas/minúsculas. Para rendimiento, usa un índice pg_trgm.

WHERE código SIMILAR TO '[0-9]{4}-[A-Z]{2}';
WHERE nombre SIMILAR TO '%(silva|santos)%';

-- Combina LIKE + regex:
-- % y _ funcionan como LIKE
-- | [] () funcionan como regex

-- Alternativa más simple:
WHERE código ~ '^\d{4}-[A-Z]{2}$';

SIMILAR TO es un híbrido entre LIKE y regex. Soporta %, _, |, []. Menos usado que ~ en la práctica. Para patrones complejos, prefiere regex pura con ~.

SELECT * FROM productos
WHERE activo = true
  AND (precio < 50 OR stock > 0);

SELECT * FROM clientes
WHERE NOT ciudad = 'Lisbon';

WHERE activo AND precio BETWEEN 10 AND 100;

AND exige todas las condiciones. OR basta con una. Usa paréntesis para controlar la precedencia — AND tiene prioridad sobre OR. NOT niega la condición.

SELECT * FROM clientes
WHERE telefono IS NULL;

SELECT * FROM clientes
WHERE email IS NOT NULL;

-- COALESCE para un valor por defecto:
SELECT COALESCE(telefono, 'sin número')
FROM clientes;

NULL no se compara con = — usa IS NULL / IS NOT NULL. NULL = NULL devuelve NULL, no true. COALESCE() sustituye NULL por un valor. Esencial para datos opcionales.

WHERE creado_en >= CURRENT_DATE - INTERVAL '7 days';
WHERE creado_en >= NOW() - INTERVAL '1 month';
WHERE EXTRACT(YEAR FROM creado_en) = 2024;
WHERE creado_en::date = CURRENT_DATE;

-- Últimos 30 días:
WHERE creado_en > NOW() - INTERVAL '30 days';

INTERVAL hace aritmética temporal. CURRENT_DATE es la fecha actual. NOW() incluye la hora. ::date convierte un timestamp a fecha. EXTRACT() obtiene partes (año, mes, día). Los índices funcionan mejor con comparaciones directas.

SELECT * FROM productos
WHERE precio BETWEEN 10 AND 100;

SELECT * FROM pedidos
WHERE creado_en BETWEEN '2024-01-01' AND '2024-12-31';

-- Equivalente a:
WHERE precio >= 10 AND precio <= 100;

BETWEEN verifica un intervalo inclusivo (incluye los límites). Funciona con números, fechas y cadenas. Equivale a >= AND <=. Para fechas, cuidado con el límite superior — usa < 2025-01-01 para incluir el día 31.

WHERE nombre ~ '^[AB]';       -- empieza con A o B
WHERE nombre ~* '^ana';       -- case-insensitive
WHERE nombre !~ '[0-9]';      -- NO contiene dígitos
WHERE email ~ '^[^@]+@[^@]+\.[^@]+$';

-- ~  = regex (case-sensitive)
-- ~* = regex (case-insensitive)
-- !~ = NOT regex

PostgreSQL soporta regex nativa con ~. ~* ignora el caso. !~ niega. Más potente que LIKE para patrones complejos. Usa ^ y $ para anclar. Ideal para validación de formatos.

SELECT * FROM clientes
WHERE ciudad IN ('Lisbon', 'Porto', 'Braga');

SELECT * FROM productos
WHERE categoria_id NOT IN (3, 7, 9);

-- Con subconsulta:
WHERE id IN (SELECT cliente_id FROM pedidos);

IN verifica la pertenencia a una lista. NOT IN excluye valores. Acepta subconsultas. Cuidado: NOT IN con NULL puede dar resultados inesperados — prefiere NOT EXISTS en esos casos.

WHERE precio > ANY (SELECT precio FROM promociones);
WHERE precio > ALL (SELECT precio FROM competidores);

WHERE EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = clientes.id
);

WHERE NOT EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = clientes.id
);

ANY compara contra al menos un resultado. ALL exige todos. EXISTS verifica existencia (más eficiente que IN para subconsultas grandes). NOT EXISTS es seguro con NULL.

SELECT p.nombre, c.nombre AS cliente
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id;

-- Solo devuelve filas con correspondencia
-- en AMBAS tablas

INNER JOIN devuelve solo filas con correspondencia en ambas tablas. ON define la condición de unión. Las filas sin correspondencia se excluyen. Es el JOIN más común. INNER es opcional — JOIN solo equivale.

SELECT f.nombre AS empleado,
       g.nombre AS gestor
FROM empleados f
LEFT JOIN empleados g
    ON f.gestor_id = g.id;

SELF JOIN es un JOIN de una tabla consigo misma. Usa alias diferentes (f y g). Clásico para jerarquías (empleado → gestor). La FK referencia la propia PK.

SELECT p.id, c.nombre AS cliente,
       pr.nombre AS producto, p.cantidad
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
JOIN productos pr ON pr.id = p.producto_id
JOIN categorias cat ON cat.id = pr.categoria_id
WHERE cat.nombre = 'Electrónica';

Encadena múltiples JOINs para navegar relaciones. Cada ON enlaza una FK con una PK. El orden de los JOINs puede afectar el rendimiento. El optimizador de PostgreSQL normalmente los reordena automáticamente.

SELECT c.nombre, p.total
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id;

-- Los clientes sin pedidos aparecen
-- con p.total = NULL

LEFT JOIN mantiene TODAS las filas de la tabla izquierda, incluso sin correspondencia. Las columnas de la derecha quedan NULL cuando no hay correspondencia. Ideal para "todos los clientes, con o sin pedidos".

SELECT c.nombre, ult.total
FROM clientes c
CROSS JOIN LATERAL (
    SELECT total FROM pedidos p
    WHERE p.cliente_id = c.id
    ORDER BY creado_en DESC
    LIMIT 3
) ult;

LATERAL permite subconsultas correlacionadas en el FROM. Cada fila de c ejecuta la subconsulta. Ideal para "top N por grupo". Más flexible que las subconsultas normales. Requiere CROSS JOIN o LEFT JOIN antes.

-- USING: cuando la columna tiene el mismo nombre
SELECT * FROM pedidos
JOIN clientes USING (cliente_id);

-- NATURAL: JOIN automático por columnas comunes
SELECT * FROM pedidos
NATURAL JOIN clientes;

-- Prefiere ON explícito por claridad

USING (columna) simplifica cuando ambos lados tienen el mismo nombre. NATURAL JOIN hace JOIN automático por todas las columnas comunes — arriesgado y poco claro. Prefiere siempre un ON explícito para evitar sorpresas.

SELECT c.nombre, p.total
FROM clientes c
RIGHT JOIN pedidos p ON c.id = p.cliente_id;

SELECT *
FROM tabla_a a
FULL OUTER JOIN tabla_b b ON a.id = b.a_id;

RIGHT JOIN mantiene todas las filas de la derecha (raro — invierte el orden). FULL OUTER JOIN mantiene todas de ambas, con NULL donde no hay correspondencia. Útil para encontrar registros huérfanos en cualquier lado.

SELECT ciudad, COUNT(*) AS total
FROM clientes
GROUP BY ciudad;

SELECT categoria, AVG(precio), MAX(precio)
FROM productos
GROUP BY categoria;

GROUP BY agrupa filas por valor. Las funciones de agregación (COUNT, AVG, MAX) se aplican a cada grupo. Las columnas del SELECT deben estar en el GROUP BY o ser agregadas. Ordena después con ORDER BY.

SELECT t.tamano, c.color
FROM tamanos t
CROSS JOIN colores c;

-- Producto cartesiano: todas las combinaciones
-- 3 tamaños × 4 colores = 12 filas

CROSS JOIN genera el producto cartesiano — cada fila de A con cada fila de B. Sin condición ON. Útil para generar combinaciones (tamaños × colores). Cuidado con tablas grandes: N × M filas.

SELECT ciudad, COUNT(*) AS total
FROM clientes
GROUP BY ciudad
HAVING COUNT(*) > 10;

-- WHERE filtra ANTES del agrupamiento
-- HAVING filtra DESPUÉS del agrupamiento

HAVING filtra grupos (tras GROUP BY). WHERE filtra filas individuales (antes). No puedes usar un alias en HAVING — repite la expresión. Equivale a un WHERE para resultados agregados.

INSERT INTO clientes (nombre, email, ciudad)
VALUES ('Ana Silva', 'ana@mail.com', 'Lisbon');

-- Múltiples filas:
INSERT INTO productos (nombre, precio)
VALUES ('Teclado', 49.90),
       ('Ratón', 29.90),
       ('Monitor', 299.00);

INSERT INTO ... VALUES inserta filas. Especifica las columnas explícitamente. Múltiples filas se separan por comas — más rápido que INSERTs separados. Las columnas con DEFAULT o SERIAL pueden omitirse.

UPDATE productos
SET precio = 99.90
WHERE id = 5;

UPDATE productos
SET precio = precio * 1.10,
    actualizado_en = NOW()
WHERE categoria = 'Electrónica';

UPDATE ... SET modifica filas existentes. Usa siempre WHERE — sin él, actualiza TODO. Puedes referenciar el valor actual (precio * 1.10). Múltiples columnas se separan por comas.

TRUNCATE TABLE logs;

TRUNCATE TABLE logs RESTART IDENTITY;

TRUNCATE TABLE pedidos, items_pedido CASCADE;

TRUNCATE borra TODAS las filas al instante (no registra fila a fila). RESTART IDENTITY reinicia las secuencias SERIAL. CASCADE borra tablas relacionadas. Mucho más rápido que DELETE para vaciar tablas enteras.

INSERT INTO clientes (nombre, email)
VALUES ('Ana', 'ana@mail.com')
RETURNING id;

INSERT INTO productos (nombre, precio)
VALUES ('Teclado', 49.90)
RETURNING id, creado_en;

RETURNING es exclusivo de PostgreSQL — devuelve datos de la fila insertada. Elimina la necesidad de un SELECT extra para obtener el id generado. Puede devolver cualquier columna o expresión. Esencial para APIs.

UPDATE productos
SET activo = false
WHERE stock = 0
RETURNING nombre, id;

UPDATE clientes
SET descuento = 0.10
WHERE ciudad = 'Porto'
RETURNING *;

RETURNING en un UPDATE muestra las filas afectadas. RETURNING * devuelve todas las columnas. Útil para confirmar qué cambió sin un SELECT extra. También funciona con DELETE.

-- Exportar a CSV:
COPY clientes TO '/tmp/clientes.csv'
WITH (FORMAT csv, HEADER true);

-- Importar de CSV:
COPY clientes (nombre, email)
FROM '/tmp/clientes.csv'
WITH (FORMAT csv, HEADER true);

-- Vía psql:
-- \copy clientes TO 'file.csv' CSV HEADER

COPY es la forma más rápida de importar/exportar datos. FORMAT csv para CSV. HEADER true incluye/ignora el encabezado. \copy en psql opera en el cliente (no necesita superuser). Miles de veces más rápido que INSERTs individuales.

INSERT INTO productos (id, nombre, stock)
VALUES (1, 'Teclado', 10)
ON CONFLICT (id) DO UPDATE
SET stock = productos.stock + EXCLUDED.stock;

-- O ignorar:
ON CONFLICT (id) DO NOTHING;

ON CONFLICT implementa UPSERT (insert o update). DO UPDATE SET actualiza si hay conflicto. EXCLUDED referencia los valores que iban a insertarse. DO NOTHING ignora silenciosamente. Requiere una constraint UNIQUE o PK.

UPDATE pedidos p
SET estado = 'cancelado'
FROM clientes c
WHERE p.cliente_id = c.id
  AND c.activo = false;

-- Sintaxis exclusiva de PostgreSQL
-- (no usa UPDATE ... JOIN)

PostgreSQL usa FROM para JOINs en un UPDATE (no JOIN). La tabla actualizada no se repite en el FROM. Usa un alias por claridad. Ideal para actualizaciones basadas en datos de otra tabla.

INSERT INTO clientes_archivo (nombre, email)
SELECT nombre, email FROM clientes
WHERE activo = false;

INSERT INTO informe (ciudad, total)
SELECT ciudad, COUNT(*)
FROM clientes
GROUP BY ciudad;

INSERT ... SELECT copia datos de una consulta. No uses VALUES — el SELECT aporta las filas. Las columnas deben ser compatibles en tipo y orden. Ideal para migraciones y snapshots.

DELETE FROM clientes
WHERE activo = false;

DELETE FROM pedidos
WHERE creado_en < NOW() - INTERVAL '2 years';

DELETE FROM logs
RETURNING id;

DELETE FROM elimina filas. Usa siempre WHERE — sin él, borra TODO. RETURNING muestra lo que se borró. Respeta las FK con ON DELETE. Para borrar todo, prefiere TRUNCATE.

CREATE TABLE productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio NUMERIC(8,2) DEFAULT 0,
    activo BOOLEAN DEFAULT true,
    creado_en TIMESTAMP DEFAULT NOW()
);

SERIAL crea auto-incremento (una secuencia). PRIMARY KEY define la clave. NOT NULL obliga a un valor. DEFAULT define un valor por defecto. NUMERIC(8,2) para dinero con 2 decimales.

CREATE TABLE sesiones (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id INTEGER NOT NULL
);

-- Generar manualmente:
SELECT gen_random_uuid();

UUID genera identificadores únicos universales (128-bit). gen_random_uuid() es nativo desde PostgreSQL 13. Ideal para APIs y sistemas distribuidos. Ocupa 16 bytes (vs 4 de INTEGER). No es secuencial — peor para índices.

cliente_id INT REFERENCES clientes(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- Opciones:
-- CASCADE: propaga delete/update
-- SET NULL: coloca NULL
-- RESTRICT: impide (por defecto)
-- NO ACTION: verifica al final

REFERENCES crea la FK. ON DELETE CASCADE borra los hijos automáticamente. SET NULL anula la FK. RESTRICT (por defecto) impide el delete si existen hijos. Elige según la regla de negocio.

SMALLINT          -- -32768 a 32767
INTEGER           -- -2B a 2B (por defecto)
BIGINT            -- muy grande
NUMERIC(10,2)     -- exacto (dinero)
REAL              -- 6 dígitos (float)
DOUBLE PRECISION  -- 15 dígitos

-- NUMERIC es exacto, REAL es aproximado

INTEGER es el valor por defecto para IDs y conteos. NUMERIC es exacto — úsalo para dinero. REAL/DOUBLE son aproximados (errores de redondeo). BIGINT para valores muy grandes. Nunca uses float para valores monetarios.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

INSERT INTO posts (tags)
VALUES (ARRAY['sql', 'postgres']);

WHERE 'sql' = ANY(tags);
WHERE tags @> ARRAY['sql'];

TEXT[] crea una columna de array. ANY() verifica si contiene un valor. @> verifica si contiene todos los valores del array. Alternativa a tablas de relación N:N para datos simples. Los índices GIN aceleran las búsquedas en arrays.

ALTER TABLE clientes
    ADD COLUMN telefono VARCHAR(20);

ALTER TABLE clientes
    DROP COLUMN fax;

ALTER TABLE clientes
    ALTER COLUMN nombre SET NOT NULL;

ALTER TABLE clientes
    RENAME COLUMN nombre TO nombre_completo;

ALTER TABLE modifica una estructura existente. ADD COLUMN añade, DROP COLUMN elimina. ALTER COLUMN ... SET NOT NULL añade una constraint. RENAME renombra. En tablas grandes, ADD COLUMN con un DEFAULT puede ser lento.

CHAR(10)       -- longitud fija (rellena con espacios)
VARCHAR(255)   -- longitud variable (máx 255)
TEXT           -- sin límite (¡eficiente!)

-- En PostgreSQL, TEXT = VARCHAR sin límite
-- Sin penalización de rendimiento

TEXT es la opción por defecto en PostgreSQL — sin límite y sin penalización. VARCHAR(n) limita la longitud (validación). CHAR(n) rellena con espacios (raro). A diferencia de otros SGBD, TEXT es tan rápido como VARCHAR.

DATE              -- 2024-01-15
TIME              -- 14:30:00
TIMESTAMP         -- 2024-01-15 14:30:00
TIMESTAMPTZ       -- con timezone
INTERVAL          -- duración ('7 days')

creado_en TIMESTAMPTZ DEFAULT NOW()

TIMESTAMPTZ guarda con timezone — prefiere siempre este. TIMESTAMP sin timezone es ambiguo. DATE solo fecha, TIME solo hora. INTERVAL para duraciones. NOW() devuelve el timestamp actual con tz.

activo BOOLEAN DEFAULT true

-- Valores aceptados:
-- true, 't', 'yes', '1'
-- false, 'f', 'no', '0'

WHERE activo = true;
WHERE activo;          -- equivalente
WHERE NOT activo;      -- negación

BOOLEAN almacena true/false/null. PostgreSQL acepta varias representaciones (t/f, yes/no). WHERE activo equivale a WHERE activo = true. NULL es distinto de false — usa IS NOT TRUE para incluir NULL.

CREATE TABLE productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    precio NUMERIC CHECK (precio >= 0),
    categoria_id INT REFERENCES categorias(id),
    CONSTRAINT nombre_unico UNIQUE (nombre, categoria_id)
);

PRIMARY KEY = UNIQUE + NOT NULL. UNIQUE impide duplicados. CHECK valida condiciones. REFERENCES crea una FK. CONSTRAINT nombre da un nombre explícito para facilitar el DROP. Las constraints se validan en cada INSERT/UPDATE.

WITH clientes_activos AS (
    SELECT id, nombre FROM clientes
    WHERE activo = true
)
SELECT ca.nombre, COUNT(p.id) AS pedidos
FROM clientes_activos ca
JOIN pedidos p ON p.cliente_id = ca.id
GROUP BY ca.nombre;

WITH crea una CTE (Common Table Expression) — una consulta temporal con nombre. Mejora la legibilidad de consultas complejas. Existe solo durante la consulta. Puede referenciarse múltiples veces. No se materializa por defecto (PostgreSQL 12+).

CREATE TABLE eventos (
    id SERIAL PRIMARY KEY,
    datos JSONB
);

INSERT INTO eventos (datos) VALUES
('{"tipo": "click", "pagina": "/home"}'),
('{"tipo": "view", "duracion": 30}');

JSONB almacena JSON en formato binario (más rápido que JSON). Soporta índices y operadores de consulta. Ideal para datos semi-estructurados. Valida el JSON en la inserción. Prefiere JSONB a JSON en la mayoría de los casos.

ALTER TABLE posts ADD COLUMN busqueda tsvector
    GENERATED ALWAYS AS (
        to_tsvector('spanish', título || ' ' || cuerpo)
    ) STORED;

CREATE INDEX idx_busqueda ON posts USING GIN (busqueda);

SELECT * FROM posts
WHERE busqueda @@ to_tsquery('spanish', 'sql & tutorial');

tsvector convierte texto en tokens de búsqueda. to_tsquery construye la consulta de búsqueda. @@ es el operador de match. Un índice GIN lo acelera. Soporta stemming y ranking. Una alternativa nativa a Elasticsearch para búsquedas simples.

WITH RECURSIVE jerarquia AS (
    SELECT id, nombre, gestor_id, 1 AS nivel
    FROM empleados WHERE gestor_id IS NULL
    UNION ALL
    SELECT f.id, f.nombre, f.gestor_id, h.nivel + 1
    FROM empleados f
    JOIN jerarquia h ON f.gestor_id = h.id
)
SELECT * FROM jerarquia;

WITH RECURSIVE permite recursión en SQL. La parte ancla inicia, la recursiva referencia la propia CTE. UNION ALL combina los resultados. Ideal para jerarquías, árboles y grafos. Incluye siempre una condición de parada.

SELECT datos->>'tipo' AS tipo
FROM eventos;

SELECT * FROM eventos
WHERE datos @> '{"tipo": "click"}';

SELECT datos->'user'->>'nombre'
FROM eventos;

-- Camino anidado:
SELECT datos #>> '{user, address, city}'
FROM eventos;

->> extrae un valor como texto. -> extrae como JSON. @> verifica contención (usa un índice GIN). #>> accede a caminos anidados. Combina con WHERE para filtrar documentos JSON eficientemente.

BEGIN;

UPDATE cuentas SET saldo = saldo - 100
WHERE id = 1;

UPDATE cuentas SET saldo = saldo + 100
WHERE id = 2;

COMMIT;
-- O ROLLBACK; para anular todo

BEGIN inicia una transacción. COMMIT la confirma, ROLLBACK la anula. Garantiza atomicidad — todo o nada. Esencial para operaciones multi-tabla. SAVEPOINT permite un rollback parcial. PostgreSQL usa MVCC por defecto.

SELECT nombre, salario,
    RANK() OVER (ORDER BY salario DESC) AS rank,
    AVG(salario) OVER () AS media_general
FROM empleados;

-- PARTITION BY agrupa la ventana:
SELECT dept, nombre, salario,
    RANK() OVER (PARTITION BY dept ORDER BY salario DESC)
FROM empleados;

OVER() define la ventana. ORDER BY dentro de OVER ordena. PARTITION BY reinicia el cálculo por grupo. No colapsa filas como GROUP BY. RANK(), ROW_NUMBER(), DENSE_RANK() son las más comunes.

CREATE INDEX idx_email ON clientes(email);

CREATE INDEX idx_datos ON eventos USING GIN (datos);

CREATE UNIQUE INDEX idx_slug ON posts(slug);

CREATE INDEX idx_creado ON pedidos(creado_en DESC);

DROP INDEX idx_email;

CREATE INDEX acelera WHERE y ORDER BY. B-tree es el valor por defecto (comparaciones). GIN para JSONB, arrays y full-text. UNIQUE impide duplicados. Los índices aceleran la lectura pero ralentizan la escritura. No crees demasiados.

-- Secuencia de números:
SELECT * FROM generate_series(1, 10);

-- Secuencia con paso:
SELECT * FROM generate_series(0, 100, 10);

-- Secuencia de fechas (útil para informes):
SELECT d::date
FROM generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 month'::interval
) AS d;

-- Rellenar gaps: LEFT JOIN con la serie
-- garantiza todos los días/meses en el resultado

generate_series() genera secuencias de números, fechas o timestamps. Esencial para informes sin gaps — haz un LEFT JOIN de la serie con los datos para incluir periodos sin registros. Acepta un paso (incremento) como tercer argumento.

SELECT nombre, salario,
    ROW_NUMBER() OVER (ORDER BY id) AS num,
    LAG(salario) OVER (ORDER BY id) AS anterior,
    LEAD(salario) OVER (ORDER BY id) AS proximo
FROM empleados;

-- Diferencia con la fila anterior:
SELECT salario - LAG(salario) OVER (ORDER BY id)
FROM empleados;

ROW_NUMBER() numera secuencialmente. LAG() accede a la fila anterior, LEAD() a la siguiente. Ideales para comparaciones temporales y deltas. Aceptan un offset: LAG(col, 2) = 2 filas atrás.

CREATE VIEW clientes_activos AS
SELECT id, nombre, email FROM clientes
WHERE activo = true;

CREATE MATERIALIZED VIEW informe_ventas AS
SELECT ciudad, SUM(total) FROM pedidos
GROUP BY ciudad;

REFRESH MATERIALIZED VIEW informe_ventas;

VIEW es una consulta guardada (se ejecuta en cada acceso). MATERIALIZED VIEW guarda el resultado físicamente — más rápido pero desactualizado. REFRESH actualiza los datos. Ideal para informes pesados consultados con frecuencia.

-- Agregar solo un subconjunto de filas:
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE estado = 'pagado') AS pagados,
  SUM(total) FILTER (WHERE estado = 'pagado') AS ingresos,
  AVG(total) FILTER (WHERE total > 0) AS media
FROM pedidos;

-- Equivalente a CASE dentro del agregado,
-- pero más legible:
-- SUM(CASE WHEN estado='pagado' THEN total END)

-- Funciona con cualquier agregado:
-- COUNT, SUM, AVG, array_agg, etc.

FILTER (WHERE ...) aplica un agregado solo a las filas que cumplen la condición — más legible que CASE dentro del agregado. Funciona con COUNT, SUM, AVG, array_agg, etc. Exclusivo de PostgreSQL / SQL estándar.

SELECT
    COUNT(*) AS total,
    SUM(precio) AS suma,
    AVG(precio) AS media,
    MIN(precio) AS mínimo,
    MAX(precio) AS máximo
FROM productos;

COUNT(*) cuenta filas. SUM, AVG, MIN, MAX operan en columnas numéricas. Ignoran los NULL (excepto COUNT(*)). Combina con GROUP BY para agregaciones por grupo.

SELECT '123'::INTEGER;
SELECT 123::TEXT;
SELECT '2024-01-15'::DATE;
SELECT precio::NUMERIC(10,2);

-- Equivalente estándar:
SELECT CAST('123' AS INTEGER);

:: es la sintaxis de PostgreSQL para conversión de tipos. Equivale a CAST(x AS tipo). Convierte strings a números, fechas, etc. Si la conversión falla, genera un error. Útil para comparar tipos diferentes.

SELECT GREATEST(10, 20, 5);   -- 20
SELECT LEAST(10, 20, 5);      -- 5

SELECT WIDTH_BUCKET(precio, 0, 100, 5)
FROM productos;  -- bucket 1-5

-- IIF no existe; usa CASE o:
SELECT (precio > 100)::TEXT FROM productos;

GREATEST/LEAST devuelven el mayor/menor de una lista. WIDTH_BUCKET distribuye en intervalos. PostgreSQL no tiene IIF() — usa CASE. Convertir un booleano a texto devuelve true/false.

SELECT STRING_AGG(nombre, ', ' ORDER BY nombre)
FROM clientes;

SELECT ARRAY_AGG(DISTINCT ciudad)
FROM clientes;

-- Resultado: "Ana, Bruno, Carla"
-- Resultado: {Lisbon, Porto, Braga}

STRING_AGG() concatena valores con un separador. ARRAY_AGG() recoge valores en un array. Ambos aceptan un ORDER BY interno. DISTINCT elimina duplicados. Útiles para informes y listas.

SELECT COALESCE(telefono, 'sin número')
FROM clientes;

SELECT COALESCE(a, b, c, 'default');

SELECT NULLIF(precio, 0);
-- Devuelve NULL si precio = 0

-- Evitar división por cero:
SELECT total / NULLIF(cant, 0) FROM items;

COALESCE() devuelve el primer valor no-NULL. NULLIF(a, b) devuelve NULL si a = b. Combínalos para evitar la división por cero. COALESCE acepta múltiples argumentos. Esencial para valores por defecto.

SELECT current_database();
SELECT current_user;
SELECT version();
SELECT pg_size_pretty(pg_database_size('tienda'));
SELECT pg_backend_pid();

-- Tamaño de tabla:
SELECT pg_size_pretty(pg_total_relation_size('pedidos'));

current_database() y current_user muestran el contexto. version() devuelve la versión de PostgreSQL. pg_size_pretty() formatea tamaños legibles. pg_total_relation_size() incluye los índices. Útiles para administración y monitoring.

UPPER('hola')          -- 'HOLA'
LOWER('HOLA')          -- 'hola'
LENGTH('hola')         -- 4
TRIM('  hola  ')       -- 'hola'
SUBSTRING('hola' FROM 1 FOR 2)  -- 'ho'
REPLACE('hola', 'o', '0')       -- 'h0la'
INITCAP('ana silva')   -- 'Ana Silva'

UPPER/LOWER cambian el caso. LENGTH cuenta caracteres. TRIM elimina espacios. SUBSTRING extrae una parte. INITCAP capitaliza cada palabra. PostgreSQL usa posiciones basadas en 1.

SELECT nombre,
    CASE
        WHEN precio > 100 THEN 'caro'
        WHEN precio > 50 THEN 'medio'
        ELSE 'barato'
    END AS categoria
FROM productos;

-- Forma simple:
CASE status WHEN 'A' THEN 'Activo'
            WHEN 'I' THEN 'Inactivo' END

CASE WHEN implementa lógica condicional en el SELECT. Funciona como if/else. La forma simple compara un valor. Termina siempre con END. Puedes usarlo en WHERE, ORDER BY y GROUP BY. Devuelve NULL si ningún caso coincide y no hay ELSE.

NOW()                          -- timestamp actual
CURRENT_DATE                   -- fecha actual
EXTRACT(YEAR FROM creado_en)   -- 2024
TO_CHAR(creado_en, 'DD/MM/YYYY')  -- '15/01/2024'
DATE_TRUNC('month', creado_en)    -- 1º del mes
creado_en + INTERVAL '7 days'     -- +7 días

NOW() devuelve un timestamp con timezone. EXTRACT() obtiene partes (YEAR, MONTH, DAY). TO_CHAR() formatea como string. DATE_TRUNC() redondea a una unidad. La aritmética con INTERVAL es intuitiva.

ROUND(3.14159, 2)   -- 3.14
CEIL(4.1)           -- 5
FLOOR(4.9)          -- 4
ABS(-5)             -- 5
MOD(10, 3)          -- 1
POWER(2, 10)        -- 1024
RANDOM()            -- 0.0 a 1.0

ROUND() redondea a N decimales. CEIL/FLOOR hacia arriba/abajo. ABS() valor absoluto. MOD() resto de la división. RANDOM() genera un float aleatorio. Usa ORDER BY RANDOM() para muestras (lento en tablas grandes).

psql -U postgres -d mi_bd
psql -h localhost -p 5432 -U user -d bd

# Con password (pide interactivamente):
psql -U postgres -W -d bd

# Connection string:
psql "postgresql://user:pass@host:5432/bd"

psql es el cliente CLI de PostgreSQL. -U usuario, -d base de datos, -h host, -p puerto. -W fuerza la petición de password. Las connection strings son prácticas para scripts y automatización.

# Backup SQL:
pg_dump -U postgres tienda > backup.sql

# Backup custom (comprimido, selectivo):
pg_dump -U postgres -Fc tienda > backup.dump

# Restaurar SQL:
psql -U postgres tienda < backup.sql

# Restaurar custom:
pg_restore -U postgres -d tienda backup.dump

pg_dump exporta una base de datos. -Fc formato custom (comprimido, permite un restore selectivo). pg_restore importa el formato custom. pg_dumpall exporta todas las bases de datos. Prueba siempre los restores. Programa los backups con cron.

VACUUM ANALYZE clientes;

VACUUM FULL clientes;  -- reescribe la tabla (lock)

ANALYZE clientes;      -- solo estadísticas

-- Autovacuum (configuración):
-- autovacuum = on (por defecto)
-- autovacuum_naptime = 60s

VACUUM recupera espacio de filas muertas (MVCC). ANALYZE actualiza las estadísticas para el planner. VACUUM FULL reescribe la tabla (lock exclusivo — evítalo en producción). autovacuum corre automáticamente por defecto.

\l          -- listar databases
\dt         -- listar tablas
\d tabla    -- describir tabla
\du         -- listar roles
\di         -- listar índices
\dn         -- listar schemas
\x          -- modo expandido
\timing     -- mostrar tiempo de ejecución
\q          -- salir

Los meta-comandos empiezan con \ (no son SQL). \dt lista tablas, \d tabla muestra la estructura. \timing muestra la duración de cada query. \x alterna el formato vertical (útil para filas anchas). \? muestra la ayuda.

CREATE SCHEMA ventas;

CREATE TABLE ventas.pedidos (
    id SERIAL PRIMARY KEY
);

SET search_path TO ventas, public;

SELECT * FROM ventas.pedidos;

SCHEMA organiza las tablas en namespaces lógicos. public es el schema por defecto. search_path define el orden de búsqueda. Útil para multi-tenancy o separación de módulos. Los permisos pueden ser por schema.

CREATE TABLESPACE ssd LOCATION '/mnt/ssd/pgdata';

CREATE TABLE hot_data (...)
    TABLESPACE ssd;

-- WAL (Write-Ahead Log):
-- pg_wal/ contiene los logs de transacciones
-- archive_mode = on  (para PITR)
-- wal_level = replica (para streaming)

TABLESPACE permite almacenar datos en discos diferentes. Útil para separar datos calientes/fríos. WAL garantiza la durabilidad — escribe antes de los datos. archive_mode para point-in-time recovery. Esencial para HA y disaster recovery.

CREATE DATABASE tienda
    ENCODING 'UTF8'
    LC_COLLATE 'es_ES.UTF-8'
    TEMPLATE template0;

-- Conectar:
\c tienda

-- Eliminar:
DROP DATABASE tienda;

CREATE DATABASE crea una nueva base de datos. ENCODING UTF8 es el valor por defecto recomendado. TEMPLATE0 para un encoding distinto del template. \c cambia de base de datos en psql. DROP DATABASE es irreversible — cuidado.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Listar disponibles:
SELECT * FROM pg_available_extensions;

-- Listar instaladas:
SELECT * FROM pg_extension;

CREATE EXTENSION activa funcionalidades extra. pg_trgm para búsqueda fuzzy. postgis para datos geográficos. uuid-ossp para UUIDs (legacy). Requiere superuser. Las extensiones se instalan por base de datos.

# Backup lógico (SQL):
pg_dump -U postgres -d tienda > tienda.sql

# Formato custom (binario, compresión):
pg_dump -U postgres -Fc tienda > tienda.dump

# Restore del formato custom:
pg_restore -U postgres -d tienda tienda.dump

# Solo el schema (sin datos):
pg_dump -U postgres --schema-only tienda > schema.sql

# Backup de TODAS las bases de datos:
pg_dumpall -U postgres > todas.sql

# pg_dump no bloquea las lecturas

pg_dump hace un backup lógico sin bloquear las lecturas. El formato -Fc (custom) es binario, comprimido y permite un restore selectivo con pg_restore. --schema-only exporta solo la estructura. pg_dumpall copia todas las bases de datos.

CREATE ROLE ana WITH LOGIN PASSWORD 'clave';
CREATE ROLE admin WITH SUPERUSER;

GRANT ALL PRIVILEGES ON DATABASE tienda TO ana;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO ana;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO ana;

REVOKE DELETE ON clientes FROM ana;

CREATE ROLE crea un usuario/grupo. WITH LOGIN permite la conexión. GRANT da permisos, REVOKE los quita. SUPERUSER tiene acceso total. Los permisos son por objeto (tabla, schema, secuencia).

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_user_tables;

-- Queries lentas activas:
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Cancelar una query:
SELECT pg_cancel_backend(pid);

pg_stat_activity muestra las conexiones y queries activas. pg_stat_user_tables estadísticas de uso. pg_cancel_backend() cancela una query. pg_terminate_backend() mata la conexión. Esenciales para debugging en producción.

# Fichero: data/pg_hba.conf
# Controla quién puede conectar y cómo

# TYPE  DATABASE  USER  ADDRESS        METHOD
local   all       all                  peer
host    all       all   127.0.0.1/32   scram-sha-256
host    tienda    ana   192.168.1.0/24 scram-sha-256
host    all       all   0.0.0.0/0      reject

# Recargar sin reiniciar:
SELECT pg_reload_conf();

pg_hba.conf controla el acceso: tipo (local/host), BD, usuario, dirección y método. scram-sha-256 es el método de password recomendado. peer usa el usuario del SO. pg_reload_conf() aplica los cambios sin reiniciar el servicio.

EXPLAIN ANALYZE
SELECT * FROM productos
WHERE precio > 100;

-- Buscar por:
-- "Seq Scan" = sin índice (lento)
-- "Index Scan" = usa índice (rápido)
-- "actual time" = tiempo real
-- "rows" = filas procesadas

EXPLAIN muestra el plan de ejecución. ANALYZE lo ejecuta y muestra los tiempos reales. Seq Scan indica falta de índice. Index Scan es el deseado. Verifica rows vs actual rows para estimaciones erróneas. La herramienta #1 de optimización.

-- Strings: comillas simples
WHERE nombre = 'Ana';

-- Identificadores: comillas dobles
SELECT "Nombre" FROM "Clientes";

-- PostgreSQL convierte a minúsculas:
CREATE TABLE Cliente  -- guarda como "cliente"
SELECT * FROM CLIENTE -- funciona (→ cliente)

Las strings usan comillas simples. Los identificadores usan comillas dobles (solo si es necesario). PostgreSQL normaliza a minúsculas — Cliente se convierte en cliente. Evita nombres con mayúsculas o palabras reservadas. Convención: todo en snake_case.

-- Nunca concatenar inputs:
-- "SELECT * FROM users WHERE id = " + input  ← MAL

-- Siempre parámetros:
SELECT * FROM users WHERE id = $1;

-- Principio del menor privilegio:
CREATE ROLE app WITH LOGIN PASSWORD 'x';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES
    IN SCHEMA public TO app;
-- Sin DELETE, sin DDL, sin superuser

Nunca concatenes el input del usuario — usa parámetros ($1, ?). Previene SQL injection. Crea roles con privilegio mínimo para la aplicación. No uses superuser para la app. pg_hba.conf controla el acceso por red/IP.

-- Índice para un WHERE frecuente:
CREATE INDEX idx_activo ON clientes(activo);

-- Índice compuesto (el orden importa):
CREATE INDEX idx_ciu_nombre ON clientes(ciudad, nombre);

-- Índice parcial:
CREATE INDEX idx_activos ON clientes(nombre)
WHERE activo = true;

Crea índices para columnas en WHERE, JOIN y ORDER BY. Los índices compuestos siguen el orden de las columnas. Un WHERE en el índice crea un índice parcial (más pequeño, más rápido). Cada índice ralentiza INSERT/UPDATE. Equilibra lectura vs escritura.

-- OFFSET (lento para páginas grandes):
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 10000;

-- Keyset (rápido y consistente):
SELECT * FROM posts
WHERE id > 10000
ORDER BY id
LIMIT 20;

OFFSET salta filas (lento para valores grandes). Keyset pagination usa un WHERE con el último ID visto — siempre rápido. Prefiere keyset para infinite scroll. OFFSET para paginación con números de página. Combínalo con un índice en la columna de ordenación.

-- pg_trgm: búsqueda fuzzy y LIKE rápido
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_nombre ON clientes
    USING GIN (nombre gin_trgm_ops);

-- pg_stat_statements: queries lentas
CREATE EXTENSION pg_stat_statements;
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

pg_trgm acelera LIKE y la búsqueda fuzzy con un índice GIN. pg_stat_statements registra todas las queries con sus tiempos. Esencial para encontrar queries lentas en producción. Activa shared_preload_libraries en postgresql.conf para pg_stat_statements.

PREPARE get_cliente (int) AS
    SELECT * FROM clientes WHERE id = $1;

EXECUTE get_cliente(42);

-- En aplicaciones:
-- PHP: $pdo->prepare('SELECT * WHERE id = ?')
-- Node: client.query('SELECT * WHERE id = $1', [id])

PREPARE compila la query una vez, la ejecuta muchas. $1, $2 son parámetros. Previene SQL injection — nunca concatenes inputs. El planner puede reutilizar el plan. Todos los lenguajes tienen soporte nativo.

-- IN (materializa la subconsulta):
WHERE id IN (SELECT cliente_id FROM pedidos);

-- EXISTS (para en la 1ª coincidencia):
WHERE EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = clientes.id
);

-- NOT EXISTS (seguro con NULLs):
WHERE NOT EXISTS (SELECT 1 FROM ...);

EXISTS para en la primera coincidencia — más rápido para subconsultas grandes. IN materializa el conjunto completo. NOT EXISTS es seguro con NULLs (NOT IN no lo es). Para conjuntos pequeños, la diferencia es mínima. El planner puede optimizar ambos.

-- Indexar el resultado de una expresión:
CREATE INDEX idx_email_lower
ON clientes (LOWER(email));

-- La query debe usar la MISMA expresión:
SELECT * FROM clientes
WHERE LOWER(email) = 'ana@ejemplo.com';

-- Útil para JSONB:
CREATE INDEX idx_meta ON eventos ((meta->>'tipo'));

-- Índice sobre un cast de fecha:
CREATE INDEX idx_dia ON ventas ((created_at::date));

Los índices de expresión indexan el resultado de una función o expresión, como LOWER(email). La query debe usar la expresión exacta para que el índice se aproveche. Ideales para búsquedas case-insensitive y campos JSONB. También funcionan con casts como ::date.

-- CORRECTO:
precio NUMERIC(10,2)

-- INCORRECTO:
precio REAL          -- ¡errores de redondeo!
precio DOUBLE PRECISION

-- Demostración:
SELECT 0.1::REAL + 0.2::REAL;  -- 0.30000001
SELECT 0.1::NUMERIC + 0.2;     -- 0.3

NUMERIC es exacto — esencial para valores monetarios. REAL/DOUBLE tienen errores de representación binaria. 0.1 + 0.2 != 0.3 con floats. NUMERIC(10,2) = 10 dígitos, 2 decimales. Nunca uses float para dinero.

-- Tablas: plural, snake_case
clientes, pedidos, items_pedido

-- Columnas: snake_case
nombre, creado_en, cliente_id

-- FKs: tabla_singular_id
cliente_id, producto_id

-- Índices: idx_tabla_columna
idx_clientes_email

-- Constraints: pk_, fk_, uq_, chk_

Usa snake_case para todo (sin mayúsculas). Tablas en plural (clientes). FKs como tabla_id. Índices con prefijo idx_. Constraints con prefijo descriptivo. Evita palabras reservadas (order, group, user).

-- Indexar solo un subconjunto de filas:
CREATE INDEX idx_pedidos_activos
ON pedidos (cliente_id)
WHERE estado = 'activo';

-- Más pequeño y rápido de mantener
-- que un índice completo

-- El planificador lo usa cuando la
-- query tiene la misma condición:
SELECT * FROM pedidos
WHERE estado = 'activo' AND cliente_id = 42;

-- Ideal para flags (activo/archivado)
-- donde el 90% de las filas son "muertas"

Los índices parciales (CREATE INDEX ... WHERE condición) indexan solo un subconjunto de filas — más pequeños y rápidos de mantener. El planificador los usa cuando la query incluye la condición. Perfectos para flags donde la mayoría de las filas es irrelevante.