SQL Intermedio: 200 Comandos y Técnicas

Ya dominas SELECT, WHERE y GROUP BY. Estos 200 elementos son lo que separa a quien consulta datos de quien modela y mantiene una base: relaciones, índices, transacciones, vistas, JSON y código ejecutándose dentro de la propia base.

La referencia sigue siendo PostgreSQL, con las diferencias de MySQL, SQL Server, Oracle y Firebird anotadas en los ejemplos.

Si un elemento te parece demasiado avanzado ahora, sáltalo y vuelve después. Lo importante es saber que existe — cuando aparezca el problema, recordarás dónde buscar.

Índice

• 1–20 · JOINs: combinar tablas
• 21–35 · Subconsultas
• 36–45 · CTEs: organizar consultas
• 46–53 · Operaciones de conjuntos
• 54–63 · Vistas
• 64–81 · Índices
• 82–97 · Transacciones y concurrencia
• 98–109 · Integridad y restricciones
• 110–124 · Funciones de ventana: lo esencial
• 125–138 · JSON dentro de la base
• 139–158 · Programar dentro de la base
• 159–170 · Modelado y normalización
• 171–180 · Carga y movimiento de datos
• 181–190 · Usuarios y permisos
• 191–200 · Metadatos y mantenimiento

JOINs: combinar tablas

Los datos normalizados viven separados. El JOIN es como los vuelves a juntar.

1. INNER JOIN

Trae solo las filas que existen en ambos lados. Es el JOIN más usado.

SELECT ped.id,
       cli.nome,
       ped.total
FROM pedidos ped
INNER JOIN clientes cli
ON cli.id = ped.cliente_id;

2. LEFT JOIN

Trae todas las filas de la izquierda; donde no hay pareja, las columnas de la derecha vienen como NULL.

SELECT cli.nome,
       ped.id AS pedido
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id;

3. RIGHT JOIN

El espejo del LEFT. En la práctica, invierte las tablas y usa LEFT — se lee más fácil.

SELECT cli.nome,
       ped.id
FROM pedidos ped
RIGHT JOIN clientes cli
ON cli.id = ped.cliente_id;

4. FULL OUTER JOIN

Trae todo de los dos lados. MySQL no lo tiene: simúlalo con LEFT UNION RIGHT.

SELECT cli.nome,
       ped.id
FROM clientes cli
FULL OUTER JOIN pedidos ped
ON ped.cliente_id = cli.id;

5. CROSS JOIN

El producto cartesiano: cada fila de A con cada fila de B. Útil para generar combinaciones, peligroso por accidente.

SELECT tim.nome,
       mes.mes
FROM times tim
CROSS JOIN meses mes;

6. SELF JOIN

La tabla consigo misma. La base de jerarquías como empleado y jefe.

SELECT fun.nome AS funcionario,
       ger.nome AS gerente
FROM funcionarios fun
LEFT JOIN funcionarios ger
ON ger.id = fun.gerente_id;

7. JOIN con varias condiciones

El ON acepta cualquier expresión, no solo la igualdad de clave.

SELECT *
FROM precos pre
INNER JOIN vigencias vig
ON vig.produto_id = pre.produto_id
AND pre.data BETWEEN vig.inicio AND vig.fim;

8. JOIN de tres o más tablas

Encadena los JOIN en el orden que tenga sentido para la relación.

SELECT cli.nome,
       ped.id,
       ite.produto,
       ite.qtd
FROM clientes cli
INNER JOIN pedidos ped
ON ped.cliente_id = cli.id
INNER JOIN itens ite
ON ite.pedido_id = ped.id;

9. USING — el atajo del ON

Cuando la columna tiene el mismo nombre en las dos tablas, USING lo acorta y quita la duplicada del resultado.

SELECT *
FROM pedidos
INNER JOIN clientes USING (cliente_id);

10. NATURAL JOIN

Une automáticamente por todas las columnas con el mismo nombre. Evítalo: cualquier columna nueva cambia el resultado en silencio.

SELECT *
FROM pedidos
NATURAL JOIN clientes;  -- prefiere un ON explícito

11. Anti-join con LEFT + IS NULL

Encuentra lo que no tiene pareja — clientes sin ningún pedido.

SELECT cli.*
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
WHERE ped.id IS NULL;

12. Anti-join con NOT EXISTS

La misma pregunta, normalmente con mejor plan e inmune al NULL.

SELECT cli.*
FROM clientes cli
WHERE NOT EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

13. Semi-join con EXISTS

Quien tiene al menos una fila relacionada, sin duplicar las filas como haría un JOIN.

SELECT cli.*
FROM clientes cli
WHERE EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

14. Un filtro en el ON vs en el WHERE

En un LEFT JOIN marca la diferencia: en el ON filtra el lado derecho; en el WHERE se convierte en un INNER JOIN sin querer.

-- Mantiene a todos los clientes
SELECT cli.nome,
       ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id AND ped.status = 'pago';

-- Se convierte en INNER JOIN (descarta a los clientes sin pedido pagado)
SELECT cli.nome, ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
WHERE ped.status = 'pago';

15. JOIN con agregación

Agrega antes de unir para no multiplicar filas e inflar las sumas.

SELECT cli.nome,
       ped_tot.total
FROM clientes cli
INNER JOIN (SELECT cliente_id, SUM(total) AS total
            FROM pedidos
            GROUP BY cliente_id) ped_tot
ON ped_tot.cliente_id = cli.id;

16. LATERAL JOIN

La subconsulta de la derecha ve las columnas de la izquierda. Ideal para 'los 3 últimos de cada uno'.

SELECT cli.nome,
       ped_rec.*
FROM clientes cli
CROSS JOIN LATERAL (
  SELECT *
  FROM pedidos ped
  WHERE ped.cliente_id = cli.id
  ORDER BY ped.criado_em DESC
  LIMIT 3
) ped_rec;

17. APPLY — SQL Server

El equivalente del LATERAL en SQL Server: CROSS APPLY y OUTER APPLY.

SELECT cli.nome,
       ped_rec.*
FROM clientes cli
CROSS APPLY (SELECT TOP 3 *
             FROM pedidos ped
             WHERE ped.cliente_id = cli.id
             ORDER BY ped.criado_em DESC) ped_rec;

18. JOIN a través de una tabla de enlace

Una relación de muchos a muchos pasa por una tercera tabla.

SELECT art.titulo,
       tag.nome AS tag
FROM artigos art
INNER JOIN artigo_tag art_tag
ON art_tag.artigo_id = art.id
INNER JOIN tags tag
ON tag.id = art_tag.tag_id;

19. Cuidado con la duplicación

Un JOIN de uno a muchos multiplica las filas del lado 'uno'. Sumar después de eso infla el resultado.

-- Mal: el total repetido por cada ítem
SELECT SUM(ped.total)
FROM pedidos ped
INNER JOIN itens ite
ON ite.pedido_id = ped.id;

-- Bien
SELECT SUM(total)
FROM pedidos;

20. JOIN con un intervalo de fechas

Casa cada evento con el rango vigente. Muy usado con tablas de precios y de cambio.

SELECT ven.id,
       cot.taxa
FROM vendas ven
INNER JOIN cotacoes cot
ON ven.data >= cot.inicio AND ven.data < cot.fim;

Subconsultas

Una consulta dentro de otra: filtra, calcula y alimenta la consulta principal.

21. Subconsulta escalar

Devuelve un único valor y puede usarse como si fuera una columna.

SELECT nome, (SELECT COUNT(*) FROM pedidos ped WHERE ped.cliente_id = cli.id) AS pedidos
FROM clientes cli;

22. Subconsulta en el WHERE

Filtra por la lista que devuelve la subconsulta.

SELECT * FROM produtos
WHERE categoria_id IN (SELECT id FROM categorias WHERE ativa);

23. Subconsulta en el FROM

La subconsulta se convierte en una tabla temporal (derivada). Necesita alias.

SELECT cidade, media
FROM (SELECT cidade, AVG(total) AS media
      FROM pedidos
      GROUP BY cidade) AS med_cidade
WHERE media > 500;

24. Subconsulta correlacionada

Referencia la fila de la consulta externa; se ejecuta una vez por fila. Potente, pero cara.

SELECT cli.nome
FROM clientes cli
WHERE (SELECT COUNT(*) FROM pedidos ped WHERE ped.cliente_id = cli.id) > 5;

25. EXISTS vs IN

Con muchas filas, EXISTS suele ganar; con listas pequeñas y fijas, IN es más sencillo.

SELECT * FROM clientes cli WHERE EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

26. NOT IN con NULL

Si la subconsulta devuelve un NULL, NOT IN no devuelve nada. Usa NOT EXISTS.

-- Peligroso
SELECT * FROM clientes WHERE id NOT IN (SELECT cliente_id FROM pedidos);

-- Seguro
SELECT * FROM clientes cli WHERE NOT EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

27. ANY / SOME

Compara con cualquier valor de la lista devuelta.

SELECT * FROM produtos WHERE preco > ANY (SELECT preco FROM produtos WHERE categoria = 'games');

28. ALL

La condición tiene que cumplirse para todos los valores de la lista.

SELECT * FROM produtos WHERE preco >= ALL (SELECT preco FROM produtos WHERE categoria = 'games');

29. Subconsulta en el SELECT vs un JOIN

A veces un JOIN agregado sustituye a la subconsulta correlacionada con una gran ganancia de rendimiento.

SELECT cli.nome,
       COALESCE(ped_qtd.qtd, 0) AS pedidos
FROM clientes cli
LEFT JOIN (SELECT cliente_id, COUNT(*) AS qtd
           FROM pedidos
           GROUP BY cliente_id) ped_qtd
ON ped_qtd.cliente_id = cli.id;

30. Subconsulta con LIMIT

Coge un valor concreto, como el último pedido del cliente.

SELECT cli.nome,
  (SELECT ped.total FROM pedidos ped WHERE ped.cliente_id = cli.id ORDER BY ped.criado_em DESC LIMIT 1) AS ultimo
FROM clientes cli;

31. Subconsulta en un UPDATE

Calcula el nuevo valor a partir de otra tabla.

UPDATE produtos
SET estoque = (SELECT SUM(qtd) FROM movimentos mov WHERE mov.produto_id = produtos.id);

32. Subconsulta en un DELETE

Borra basándose en un criterio que viene de otra tabla.

DELETE FROM carrinho
WHERE produto_id IN (SELECT id FROM produtos WHERE descontinuado);

33. Subconsulta en el HAVING

Compara la agregación del grupo con un valor calculado.

SELECT cidade, AVG(total) AS media
FROM pedidos GROUP BY cidade
HAVING AVG(total) > (SELECT AVG(total) FROM pedidos);

34. Subconsulta de fila

Compara varias columnas a la vez.

SELECT * FROM pedidos
WHERE (cliente_id, criado_em) IN (SELECT cliente_id, MAX(criado_em) FROM pedidos GROUP BY cliente_id);

35. Cuándo cambiarla por una CTE

¿Una subconsulta repetida en la misma consulta? Extráela a una CTE: se lee mejor y se evalúa una sola vez.

WITH media AS (SELECT AVG(total) AS valor FROM pedidos)
SELECT * FROM pedidos, media WHERE total > media.valor;

CTEs: organizar consultas

WITH convierte una consulta ilegible en pasos con nombre.

36. WITH — una CTE sencilla

Da nombre a un resultado intermedio y lo usa justo debajo.

WITH ativos AS (
  SELECT * FROM clientes WHERE ativo
)
SELECT COUNT(*) FROM ativos;

37. Varias CTEs

Sepáralas con comas y monta la consulta por etapas.

WITH pagos AS (
  SELECT * FROM pedidos WHERE status = 'pago'
), por_cliente AS (
  SELECT cliente_id, SUM(total) AS total FROM pagos GROUP BY cliente_id
)
SELECT * FROM por_cliente ORDER BY total DESC LIMIT 10;

38. Una CTE que referencia a otra

Cada CTE ve las anteriores — es un pipeline de transformación.

WITH base AS (SELECT * FROM vendas WHERE ano = 2026),
     resumo AS (SELECT regiao, SUM(valor) AS total FROM base GROUP BY regiao)
SELECT * FROM resumo WHERE total > 100000;

39. CTE en un UPDATE y un DELETE

Prepara el conjunto objetivo antes de modificarlo.

WITH antigos AS (
  SELECT id FROM sessoes WHERE criado_em < CURRENT_DATE - INTERVAL '90 days'
)
DELETE FROM sessoes WHERE id IN (SELECT id FROM antigos);

40. CTE recursiva

La CTE se llama a sí misma: recorre jerarquías y genera series.

WITH RECURSIVE hierarquia AS (
  SELECT id, nome, gerente_id, 1 AS nivel FROM funcionarios WHERE gerente_id IS NULL
  UNION ALL
  SELECT fun.id, fun.nome, fun.gerente_id, hie.nivel + 1
  FROM funcionarios fun
  INNER JOIN hierarquia hie
  ON fun.gerente_id = hie.id
)
SELECT *
FROM hierarquia
ORDER BY nivel;

41. Una serie de fechas con una CTE recursiva

Crea un calendario para rellenar los días sin ventas en el informe.

WITH RECURSIVE dias AS (
  SELECT DATE '2026-01-01' AS dia
  UNION ALL
  SELECT dia + 1 FROM dias WHERE dia < DATE '2026-01-31'
)
SELECT * FROM dias;

42. Cuidado con la recursión infinita

Garantiza siempre una condición de parada; muchas bases permiten limitar la profundidad.

WITH RECURSIVE r AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM r WHERE n < 100   -- parada obligatoria
)
SELECT COUNT(*) FROM r;

43. CTE materializada

En PostgreSQL controlas si la CTE se convierte en una tabla temporal o se funde con la consulta.

WITH pesada AS MATERIALIZED (
  SELECT * FROM eventos WHERE tipo = 'compra'
)
SELECT COUNT(*) FROM pesada;

44. CTE vs subconsulta

La CTE gana en legibilidad y reutilización; la subconsulta a veces gana en el plan. Mide las dos.

-- La misma respuesta, formas distintas de escribirla
WITH ped_tot AS (
  SELECT cliente_id, SUM(total) AS total
  FROM pedidos
  GROUP BY cliente_id
)
SELECT *
FROM ped_tot
WHERE total > 1000;

45. CTE con datos literales

VALUES dentro de una CTE crea una tabla de apoyo sin necesidad de crear una tabla.

WITH faixas(nome, minimo, maximo) AS (
  VALUES ('barato', 0, 50), ('medio', 50, 200), ('caro', 200, 999999)
)
SELECT prod.nome,
       fai.nome AS faixa
FROM produtos prod
INNER JOIN faixas fai
ON prod.preco >= fai.minimo AND prod.preco < fai.maximo;

Operaciones de conjuntos

Apilar y comparar los resultados de consultas distintas.

46. UNION

Apila dos resultados y quita los duplicados (cuesta una ordenación).

SELECT email FROM clientes
UNION
SELECT email FROM leads;

47. UNION ALL

Apila sin quitar los duplicados. Bastante más rápido — úsalo cuando no haya repetición posible.

SELECT id, 'pedido' AS origem FROM pedidos
UNION ALL
SELECT id, 'orcamento' FROM orcamentos;

48. INTERSECT

Solo lo que aparece en los dos resultados.

SELECT email FROM clientes
INTERSECT
SELECT email FROM newsletter;

49. EXCEPT / MINUS

Lo que está en el primero y no en el segundo. En Oracle se llama MINUS.

SELECT email FROM leads
EXCEPT
SELECT email FROM clientes;

50. Reglas de compatibilidad

Las consultas necesitan el mismo número de columnas y tipos compatibles, en el mismo orden.

SELECT nome, email FROM clientes
UNION ALL
SELECT razao_social, contato FROM empresas;

51. ORDER BY en una unión

Se aplica al resultado entero y va al final, una sola vez.

SELECT nome FROM clientes
UNION ALL
SELECT nome FROM fornecedores
ORDER BY nome;

52. Simular un FULL JOIN en MySQL

MySQL no tiene FULL OUTER JOIN: usa LEFT UNION RIGHT.

SELECT cli.nome,
       ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
UNION
SELECT cli.nome, ped.id
FROM clientes cli
RIGHT JOIN pedidos ped
ON ped.cliente_id = cli.id;

53. Comparar dos tablas

EXCEPT en los dos sentidos muestra exactamente lo que difiere entre entornos.

(SELECT * FROM producao.clientes EXCEPT SELECT * FROM homolog.clientes)
UNION ALL
(SELECT * FROM homolog.clientes EXCEPT SELECT * FROM producao.clientes);

Vistas

Una consulta guardada con nombre: esconde la complejidad y estandariza la regla de negocio.

54. CREATE VIEW

Guarda una consulta como si fuera una tabla virtual.

CREATE VIEW vw_pedidos_pagos AS
SELECT ped.*, cli.nome AS cliente
FROM pedidos ped JOIN clientes cli ON cli.id = ped.cliente_id
WHERE ped.status = 'pago';

55. Consultar una vista

Úsala como cualquier tabla — incluso en un JOIN.

SELECT * FROM vw_pedidos_pagos WHERE total > 500;

56. CREATE OR REPLACE VIEW

Actualiza la definición sin tener que borrarla (las columnas deben ser compatibles).

CREATE OR REPLACE VIEW vw_pedidos_pagos AS
SELECT ped.*, cli.nome AS cliente, cli.cidade
FROM pedidos ped JOIN clientes cli ON cli.id = ped.cliente_id
WHERE ped.status = 'pago';

57. DROP VIEW

Elimina la vista. Los datos quedan intactos: una vista no guarda nada.

DROP VIEW IF EXISTS vw_pedidos_pagos;

58. Una vista para simplificar un informe

Encapsula el JOIN y la regla, y el equipo de negocio la consulta sin conocer el modelo.

CREATE VIEW vw_faturamento_mensal AS
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) AS faturamento
FROM pedidos WHERE status = 'pago'
GROUP BY 1;

59. Una vista como capa de seguridad

Expón solo las columnas permitidas y da el permiso sobre la vista, no sobre la tabla.

CREATE VIEW vw_clientes_publico AS
SELECT id, nome, cidade FROM clientes;   -- sin correo ni teléfono

GRANT SELECT ON vw_clientes_publico TO app_leitura;

60. Vista actualizable

Las vistas sencillas (una tabla, sin agregación) aceptan un INSERT/UPDATE directo.

CREATE VIEW vw_ativos AS SELECT * FROM clientes WHERE ativo;
UPDATE vw_ativos SET cidade = 'Campinas' WHERE id = 1;

61. WITH CHECK OPTION

Impide grabar por la vista una fila que quedaría fuera de su filtro.

CREATE VIEW vw_ativos AS
SELECT * FROM clientes WHERE ativo
WITH CHECK OPTION;

62. Vista materializada

Guarda el resultado en disco: lectura rápida, dato con retraso. Necesita un refresh.

CREATE MATERIALIZED VIEW mv_faturamento AS
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) AS total
FROM pedidos GROUP BY 1;

63. REFRESH MATERIALIZED VIEW

Lo recalcula. CONCURRENTLY evita bloquear las lecturas (exige un índice único).

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_faturamento;

Índices

Lo que convierte una consulta de 30 segundos en 3 milisegundos — y el INSERT en algo más lento.

64. CREATE INDEX

Crea un índice en la columna más usada en filtros y JOINs.

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

65. Índice compuesto

Cubre filtros por varias columnas. El orden importa: empieza por la más selectiva/más filtrada.

CREATE INDEX idx_pedidos_cliente_data ON pedidos (cliente_id, criado_em);

66. La regla del prefijo por la izquierda

Un índice en (a, b) atiende filtros por a y por a + b, pero no por b solo.

-- Usa el índice
SELECT * FROM pedidos WHERE cliente_id = 1;
-- No lo usa
SELECT * FROM pedidos WHERE criado_em > CURRENT_DATE;

67. Índice único

Garantiza la unicidad y además sirve como índice de búsqueda.

CREATE UNIQUE INDEX uq_clientes_email ON clientes (email);

68. Índice parcial

Indexa solo las filas que interesan: más pequeño, más rápido y más barato de mantener (PostgreSQL).

CREATE INDEX idx_pedidos_abertos ON pedidos (criado_em) WHERE status = 'aberto';

69. Índice por expresión

Indexa el resultado de una función — necesario cuando el filtro usa la función.

CREATE INDEX idx_clientes_email_lower ON clientes (LOWER(email));
SELECT * FROM clientes WHERE LOWER(email) = 'ana@email.com';

70. Índice descendente

Alinea el índice con la ordenación más usada, evitando un sort en cada consulta.

CREATE INDEX idx_pedidos_recentes ON pedidos (criado_em DESC);

71. Índice de cobertura (covering)

Si el índice contiene todas las columnas de la consulta, la base ni siquiera lee la tabla.

CREATE INDEX idx_cobertura ON pedidos (cliente_id) INCLUDE (total, status);

72. DROP INDEX

Un índice que nadie usa solo cuesta espacio y escritura. Quítalo.

DROP INDEX IF EXISTS idx_pedidos_cliente;

73. Crear un índice sin bloquear

CONCURRENTLY (PostgreSQL) y ONLINE (SQL Server/Oracle) lo crean sin bloquear la escritura.

CREATE INDEX CONCURRENTLY idx_pedidos_status ON pedidos (status);

74. Ver los índices de una tabla

Cada base lo expone en un catálogo distinto.

-- PostgreSQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'pedidos';

-- MySQL
SHOW INDEX FROM pedidos;

75. Los índices y la escritura

Todo índice hay que actualizarlo en cada INSERT/UPDATE/DELETE. Demasiados índices ralentizan la escritura.

-- Regla práctica: indexa lo que de verdad filtras/ordenas,
-- y revisa periódicamente los índices sin uso.

76. Un índice no usado por una conversión

Comparar una columna indexada con un tipo distinto descarta el índice.

-- No usa el índice (id es entero)
SELECT * FROM pedidos WHERE id::text = '42';

-- Sí lo usa
SELECT * FROM pedidos WHERE id = 42;

77. Un índice para la búsqueda de texto

LIKE '%algo%' no usa un índice B-tree. Un prefijo ('algo%') sí; para el resto, full-text o trigramas.

CREATE INDEX idx_produtos_nome ON produtos (nome varchar_pattern_ops);
SELECT * FROM produtos WHERE nome LIKE 'Note%';

78. Índice GIN para JSON y texto

Una estructura propia para contenido compuesto: JSONB, arrays y búsqueda textual (PostgreSQL).

CREATE INDEX idx_config_dados ON configuracoes USING GIN (dados);

79. Índice en una clave foránea

Las bases crean un índice en la PK, pero no siempre en la FK. Sin él, el JOIN y el DELETE del padre se vuelven lentos.

CREATE INDEX idx_itens_pedido ON itens (pedido_id);

80. Índices duplicados

Un índice en (a) es redundante si ya existe (a, b). Quita el menor.

-- (cliente_id) está cubierto por (cliente_id, criado_em)
DROP INDEX idx_pedidos_cliente;

81. REINDEX

Reconstruye los índices hinchados por muchas actualizaciones.

REINDEX TABLE pedidos;

Transacciones y concurrencia

Garantizar que las operaciones ocurran por entero — o que no ocurran.

82. BEGIN — abrir una transacción

A partir de aquí, nada es definitivo hasta el COMMIT.

BEGIN;
UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;

83. COMMIT

Confirma todo lo que hizo la transacción.

COMMIT;

84. ROLLBACK

Deshace todo desde el BEGIN. Tu red de seguridad.

BEGIN;
DELETE FROM clientes;   -- ups
ROLLBACK;

85. SAVEPOINT

Un punto intermedio: se puede deshacer solo un tramo de la transacción.

BEGIN;
INSERT INTO log VALUES ('inicio');
SAVEPOINT p1;
DELETE FROM temporarios;
ROLLBACK TO p1;    -- deshace solo el DELETE
COMMIT;

86. ACID

Atomicidad, Consistencia, Aislamiento y Durabilidad — las garantías que da una base relacional.

-- Atomicidad: todo o nada
-- Consistencia: restricciones siempre válidas
-- Aislamiento: las transacciones no se estorban
-- Durabilidad: si hizo commit, está grabado

87. READ COMMITTED

El nivel por defecto en la mayoría: solo ves lo que ya se ha confirmado.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

88. REPEATABLE READ

La misma consulta devuelve el mismo resultado durante toda la transacción.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

89. SERIALIZABLE

El nivel más estricto: un resultado equivalente a ejecutar las transacciones en fila. Más seguro, más conflictos.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

90. Lectura sucia

Leer un dato no confirmado. Solo ocurre en READ UNCOMMITTED — evítalo.

-- SQL Server: NOLOCK hace una lectura sucia. Úsalo con mucha conciencia.
SELECT * FROM pedidos WITH (NOLOCK);

91. SELECT FOR UPDATE

Bloquea las filas leídas hasta el final de la transacción, evitando que otra sesión las modifique por el medio.

BEGIN;
SELECT * FROM estoque WHERE produto_id = 1 FOR UPDATE;
UPDATE estoque SET qtd = qtd - 1 WHERE produto_id = 1;
COMMIT;

92. SKIP LOCKED

Se salta las filas bloqueadas en lugar de esperar. La base de una cola de trabajo con varios consumidores.

SELECT * FROM fila WHERE status = 'pendente'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

93. NOWAIT

Falla al instante en lugar de quedarse esperando el lock.

SELECT * FROM contas WHERE id = 1 FOR UPDATE NOWAIT;

94. Deadlock

Dos transacciones esperándose la una a la otra. La base mata a una de ellas. Prevénlo accediendo a las tablas siempre en el mismo orden.

-- Sesión A: contas -> pedidos
-- Sesión B: pedidos -> contas   <- riesgo de deadlock
-- Estandariza el orden de acceso

95. Actualización perdida

Leer, calcular en la aplicación y grabar sobrescribe el trabajo de otro. Actualiza en el propio SQL.

-- Frágil
SELECT saldo FROM contas WHERE id = 1;   -- la app suma
UPDATE contas SET saldo = 150 WHERE id = 1;

-- Seguro
UPDATE contas SET saldo = saldo + 50 WHERE id = 1;

96. Bloqueo optimista

Una columna de versión detecta una modificación concurrente sin mantener un lock.

UPDATE produtos SET preco = 99, versao = versao + 1
WHERE id = 1 AND versao = 7;   -- 0 filas = alguien lo modificó antes

97. Transacciones cortas

Una transacción abierta retiene locks y bloquea a otros. Ábrela tarde, ciérrala pronto, y nunca esperes E/S externa dentro de ella.

-- Evita: BEGIN; ... llamada HTTP ... COMMIT;
-- Haz la llamada externa fuera de la transacción.

Integridad y restricciones

Reglas que la base garantiza sola — valen para cualquier aplicación que escriba en ella.

98. Nombrar las restricciones

Un nombre explícito hace que el error de la base diga exactamente qué regla se violó.

ALTER TABLE pedidos
ADD CONSTRAINT ck_pedidos_total_positivo CHECK (total >= 0);

99. Clave foránea con acción

Define qué pasa con los hijos cuando el padre cambia o desaparece.

ALTER TABLE itens ADD CONSTRAINT fk_itens_pedido
FOREIGN KEY (pedido_id) REFERENCES pedidos (id)
ON DELETE CASCADE ON UPDATE CASCADE;

100. ON DELETE SET NULL

Mantiene al hijo, pero limpia la referencia.

ALTER TABLE funcionarios ADD CONSTRAINT fk_gerente
FOREIGN KEY (gerente_id) REFERENCES funcionarios (id) ON DELETE SET NULL;

101. ON DELETE RESTRICT

Impide borrar al padre mientras haya hijos. Suele ser el valor por defecto más seguro.

ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_cliente
FOREIGN KEY (cliente_id) REFERENCES clientes (id) ON DELETE RESTRICT;

102. CHECK sobre varias columnas

Valida la coherencia entre campos de la misma fila.

ALTER TABLE reservas
ADD CONSTRAINT ck_periodo CHECK (fim > inicio);

103. CHECK con una lista de valores

Una alternativa sencilla al tipo ENUM.

ALTER TABLE pedidos
ADD CONSTRAINT ck_status CHECK (status IN ('pendente','pago','cancelado'));

104. UNIQUE compuesta

Lo que tiene que ser único es la combinación, no cada columna aislada.

ALTER TABLE inscricoes
ADD CONSTRAINT uk_aluno_turma UNIQUE (aluno_id, turma_id);

105. UNIQUE parcial

Unicidad solo para un subconjunto — un registro activo por cliente, por ejemplo.

CREATE UNIQUE INDEX uq_assinatura_ativa ON assinaturas (cliente_id) WHERE ativa;

106. Deshabilitar y volver a habilitar

En cargas grandes, deshabilitarlas temporalmente acelera — pero revalida después.

ALTER TABLE pedidos DROP CONSTRAINT fk_pedidos_cliente;
-- carga
ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_cliente
FOREIGN KEY (cliente_id) REFERENCES clientes (id);

107. Restricción diferida

DEFERRABLE aplaza la comprobación al COMMIT — resuelve las inserciones circulares.

ALTER TABLE a ADD CONSTRAINT fk_a_b FOREIGN KEY (b_id) REFERENCES b (id)
DEFERRABLE INITIALLY DEFERRED;

108. Encontrar los huérfanos

Antes de crear la FK, encuentra las filas que romperían la regla.

SELECT ped.*
FROM pedidos ped
LEFT JOIN clientes cli
ON cli.id = ped.cliente_id
WHERE cli.id IS NULL;

109. ENUM

Un tipo con una lista fija de valores. PostgreSQL y MySQL lo tienen nativo; en los demás, usa un CHECK.

CREATE TYPE status_pedido AS ENUM ('pendente','pago','cancelado');
ALTER TABLE pedidos ALTER COLUMN status TYPE status_pedido USING status::status_pedido;

Funciones de ventana: lo esencial

Agregan sin colapsar las filas. Después de entenderlas, ya no puedes vivir sin ellas.

110. OVER() — la ventana

Calcula sobre un conjunto de filas, pero mantiene cada fila en el resultado.

SELECT nome, total, SUM(total) OVER () AS total_geral FROM pedidos;

111. PARTITION BY

Divide en grupos y calcula dentro de cada uno, sin GROUP BY.

SELECT cliente_id, total,
       SUM(total) OVER (PARTITION BY cliente_id) AS total_cliente
FROM pedidos;

112. ROW_NUMBER

Numera las filas dentro de la partición. La base para 'el más reciente de cada uno'.

SELECT ped.cliente_id,
       ped.criado_em,
       ROW_NUMBER() OVER (PARTITION BY ped.cliente_id ORDER BY ped.criado_em DESC) AS ordem
FROM pedidos ped;

113. Coger el último de cada grupo

Numéralas y filtra por el número 1 — el patrón más usado con las window functions.

SELECT *
FROM (
  SELECT ped.*,
         ROW_NUMBER() OVER (PARTITION BY ped.cliente_id ORDER BY ped.criado_em DESC) AS ordem
  FROM pedidos ped
) ped_num
WHERE ordem = 1;

114. RANK

Clasificación con empates: dos primeros puestos se saltan el segundo.

SELECT nome, total, RANK() OVER (ORDER BY total DESC) AS posicao FROM vendedores;

115. DENSE_RANK

Igual que RANK, pero sin saltarse posiciones tras un empate.

SELECT nome, total, DENSE_RANK() OVER (ORDER BY total DESC) AS posicao FROM vendedores;

116. NTILE

Divide las filas en N tramos de tamaño parecido — cuartiles, deciles.

SELECT nome, total, NTILE(4) OVER (ORDER BY total) AS quartil FROM clientes;

117. LAG — la fila anterior

Trae el valor de la fila anterior. La comparación con el mes pasado sale en una línea.

SELECT mes, total, LAG(total) OVER (ORDER BY mes) AS mes_anterior FROM faturamento;

118. LEAD — la fila siguiente

La misma idea, mirando hacia delante.

SELECT mes, total, LEAD(total) OVER (ORDER BY mes) AS proximo FROM faturamento;

119. Variación porcentual

LAG + aritmética resuelve el clásico '¿cuánto creció?'.

SELECT mes, total,
  ROUND(100.0 * (total - LAG(total) OVER (ORDER BY mes)) / NULLIF(LAG(total) OVER (ORDER BY mes), 0), 1) AS variacao
FROM faturamento;

120. Suma acumulada

Una suma progresiva a lo largo de la ordenación.

SELECT dia, valor,
       SUM(valor) OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING) AS acumulado
FROM caixa;

121. Media móvil

La media de las últimas N filas — suaviza la serie del gráfico.

SELECT dia, valor,
  AVG(valor) OVER (ORDER BY dia ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_7d
FROM metricas;

122. FIRST_VALUE y LAST_VALUE

El primer y el último valor de la ventana. Cuidado: LAST_VALUE necesita el frame explícito.

SELECT cliente_id, criado_em,
  FIRST_VALUE(total) OVER (PARTITION BY cliente_id ORDER BY criado_em) AS primeiro
FROM pedidos;

123. WINDOW con nombre

¿Definiste la misma ventana tres veces? Ponle nombre una vez y reutilízala.

SELECT cliente_id,
       SUM(total) OVER w AS soma,
       AVG(total) OVER w AS media
FROM pedidos
WINDOW w AS (PARTITION BY cliente_id);

124. Ventana vs GROUP BY

GROUP BY reduce el número de filas; la ventana las preserva. Usa la ventana cuando necesites el detalle y el total juntos.

SELECT cliente_id, total,
       ROUND(100.0 * total / SUM(total) OVER (PARTITION BY cliente_id), 1) AS pct_do_cliente
FROM pedidos;

JSON dentro de la base

Campos semiestructurados sin renunciar al SQL — siempre que se usen con criterio.

125. Columnas JSON y JSONB

En PostgreSQL, JSONB es binario, indexable y más rápido de consultar. Prefiere JSONB.

CREATE TABLE eventos (id BIGSERIAL PRIMARY KEY, payload JSONB NOT NULL);

126. Insertar JSON

Pasa el documento como texto: la base valida la sintaxis.

INSERT INTO eventos (payload) VALUES ('{"tipo":"compra","valor":199.9}');

127. Leer un campo

-> devuelve JSON; ->> devuelve texto (que es lo que quieres para comparar).

SELECT payload->>'tipo' AS tipo, (payload->>'valor')::numeric AS valor FROM eventos;

128. Ruta anidada

#>> navega por varios niveles de una vez.

SELECT payload#>>'{cliente,email}' AS email FROM eventos;

129. Filtrar por un campo del JSON

Funciona como cualquier WHERE — pero indéxalo si es una consulta frecuente.

SELECT * FROM eventos WHERE payload->>'tipo' = 'compra';

130. El operador de contención

@> pregunta si el JSON contiene ese fragmento. Es lo que acelera el índice GIN.

SELECT * FROM eventos WHERE payload @> '{"tipo":"compra"}';

131. Comprobar si existe una clave

? comprueba si la clave existe en el documento.

SELECT * FROM eventos WHERE payload ? 'cupom';

132. Indexar JSONB

Sin índice, filtrar JSON recorre la tabla entera.

CREATE INDEX idx_eventos_payload ON eventos USING GIN (payload);

133. Índice en un campo concreto

Más ligero que indexar todo el documento, cuando siempre filtras por el mismo campo.

CREATE INDEX idx_eventos_tipo ON eventos ((payload->>'tipo'));

134. Actualizar un campo

jsonb_set cambia un valor sin reescribir el documento en la aplicación.

UPDATE eventos SET payload = jsonb_set(payload, '{status}', '"processado"') WHERE id = 1;

135. Quitar una clave

El operador - devuelve el documento sin la clave.

UPDATE eventos SET payload = payload - 'temporario' WHERE id = 1;

136. Expandir un array en filas

Convierte un array JSON en filas para tratarlo con SQL normal.

SELECT eve.id, item
FROM eventos eve, jsonb_array_elements(eve.payload->'itens') AS item;

137. Montar JSON en el SELECT

Devuelve el resultado ya en el formato que necesita la API.

SELECT jsonb_build_object('id', id, 'nome', nome, 'cidade', cidade) FROM clientes;

138. Cuándo NO usar JSON

Si el campo se consulta, se filtra y se relaciona a todas horas, merece ser una columna de verdad.

-- Mal: el precio dentro del JSON, usado en todos los informes
-- Bien: una columna preco DECIMAL(12,2) + un índice

Programar dentro de la base

Procedimientos, funciones y triggers: lógica ejecutándose cerca del dato.

139. CREATE FUNCTION

Una función que recibe parámetros y devuelve un valor, utilizable dentro de un SELECT.

CREATE FUNCTION total_do_cliente(p_id INTEGER) RETURNS NUMERIC AS $$
  SELECT COALESCE(SUM(total), 0) FROM pedidos WHERE cliente_id = p_id;
$$ LANGUAGE SQL;

140. Usar la función

Llámala como cualquier función nativa.

SELECT nome, total_do_cliente(id) AS total FROM clientes;

141. Una función en PL/pgSQL

Para lógica con variables, bucles y condicionales.

CREATE FUNCTION reajuste(p_preco NUMERIC, p_pct NUMERIC) RETURNS NUMERIC AS $$
BEGIN
  IF p_pct > 50 THEN RAISE EXCEPTION 'Reajuste acima do permitido';
  END IF;
  RETURN ROUND(p_preco * (1 + p_pct / 100), 2);
END;
$$ LANGUAGE plpgsql;

142. CREATE PROCEDURE

Un procedimiento ejecuta acciones (y puede controlar la transacción); no devuelve un valor como una función.

CREATE PROCEDURE limpar_sessoes() LANGUAGE SQL AS $$
  DELETE FROM sessoes WHERE criado_em < CURRENT_DATE - INTERVAL '90 days';
$$;

143. CALL

Ejecuta el procedimiento.

CALL limpar_sessoes();

144. Parámetros de salida

Los procedimientos pueden devolver valores mediante parámetros INOUT.

CREATE PROCEDURE contar(INOUT total INTEGER) LANGUAGE plpgsql AS $$
BEGIN
  SELECT COUNT(*) INTO total FROM clientes;
END; $$;

145. Variables y SELECT INTO

Guarda el resultado de una consulta en una variable.

DECLARE v_total NUMERIC;
SELECT SUM(total) INTO v_total FROM pedidos;

146. IF / ELSE

Un condicional dentro del código de la base.

IF v_total > 1000 THEN
  RAISE NOTICE 'Meta batida';
ELSE
  RAISE NOTICE 'Faltam %', 1000 - v_total;
END IF;

147. LOOP y FOR

Bucles para procesar fila a fila — úsalos solo cuando no puedas resolverlo en conjunto.

FOR ped IN SELECT id FROM pedidos WHERE status = 'pendente' LOOP
  UPDATE pedidos SET status = 'processando' WHERE id = ped.id;
END LOOP;

148. RAISE — mensajes y errores

Registra un aviso o interrumpe con una excepción.

RAISE NOTICE 'Processando cliente %', v_id;
RAISE EXCEPTION 'Saldo insuficiente para o cliente %', v_id;

149. Tratamiento de excepciones

Captura el error y decide qué hacer.

BEGIN
  INSERT INTO clientes (email) VALUES ('duplicado@email.com');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'E-mail já cadastrado';
END;

150. CREATE TRIGGER

Código disparado automáticamente por un INSERT, UPDATE o DELETE.

CREATE TRIGGER trg_atualiza_data
BEFORE UPDATE ON produtos
FOR EACH ROW EXECUTE FUNCTION set_atualizado_em();

151. Función de trigger

En PostgreSQL, el trigger llama a una función que devuelve TRIGGER.

CREATE FUNCTION set_atualizado_em() RETURNS TRIGGER AS $$
BEGIN
  NEW.atualizado_em := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

152. BEFORE vs AFTER

BEFORE puede modificar la fila antes de grabarla; AFTER sirve para los efectos colaterales (log, cola).

CREATE TRIGGER trg_log AFTER INSERT ON pedidos
FOR EACH ROW EXECUTE FUNCTION registrar_log();

153. NEW y OLD

Dentro del trigger, NEW es la fila nueva y OLD es la antigua.

IF NEW.preco <> OLD.preco THEN
  INSERT INTO historico_precos (produto_id, de, para) VALUES (OLD.id, OLD.preco, NEW.preco);
END IF;

154. Trigger de auditoría

Guarda quién cambió qué y cuándo, sin depender de la aplicación.

CREATE FUNCTION auditar() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO auditoria (tabela, operacao, dados, quando)
  VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW), CURRENT_TIMESTAMP);
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

155. Deshabilitar un trigger

En cargas grandes, desactívalo temporalmente — y acuérdate de volver a activarlo.

ALTER TABLE pedidos DISABLE TRIGGER trg_log;
-- carga
ALTER TABLE pedidos ENABLE TRIGGER trg_log;

156. DROP FUNCTION / TRIGGER

Quita lo que ya no se usa: el código muerto en la base es peor que en la aplicación.

DROP TRIGGER IF EXISTS trg_log ON pedidos;
DROP FUNCTION IF EXISTS registrar_log();

157. Cuidado con los triggers

Un trigger es invisible para quien lee la aplicación. Úsalo para la integridad y la auditoría, no para reglas de negocio complejas.

-- Bien: rellenar atualizado_em, auditar
-- Mal: calcular una comisión, enviar un correo, llamar a una API

158. Función determinista

Márcala como IMMUTABLE cuando el resultado dependa solo de los parámetros: permite indexarla y cachearla.

CREATE FUNCTION slug(t TEXT) RETURNS TEXT AS $$
  SELECT LOWER(REPLACE(t, ' ', '-'));
$$ LANGUAGE SQL IMMUTABLE;

Modelado y normalización

Decisiones de modelo que tomas en una tarde y sostienes durante años.

159. 1FN — primera forma normal

Nada de listas dentro de una columna: cada campo guarda un solo valor.

-- Mal: telefones = '1199..., 1198...'
CREATE TABLE telefones (cliente_id INT, numero VARCHAR(20));

160. 2FN — segunda forma normal

Ninguna columna depende solo de una parte de la clave compuesta.

-- El nombre del producto no depende del pedido: sale de la tabla de ítems
CREATE TABLE itens (pedido_id INT, produto_id INT, qtd INT, PRIMARY KEY (pedido_id, produto_id));

161. 3FN — tercera forma normal

Una columna no puede depender de otra columna común, solo de la clave.

-- La ciudad y la provincia dependen del código postal, no del cliente
CREATE TABLE ceps (cep CHAR(8) PRIMARY KEY, cidade VARCHAR(80), estado CHAR(2));

162. Desnormalizar a propósito

Duplicar datos para acelerar la lectura es válido — siempre que sea una decisión consciente y con una rutina de sincronización.

ALTER TABLE pedidos ADD COLUMN cliente_nome VARCHAR(120);  -- foto histórica

163. Uno a muchos

La clave foránea vive en el lado 'muchos'.

CREATE TABLE pedidos (
  id SERIAL PRIMARY KEY,
  cliente_id INTEGER NOT NULL REFERENCES clientes (id)
);

164. Muchos a muchos

Necesita una tabla de enlace con las dos claves.

CREATE TABLE aluno_curso (
  aluno_id INT REFERENCES alunos (id),
  curso_id INT REFERENCES cursos (id),
  PRIMARY KEY (aluno_id, curso_id)
);

165. Uno a uno

Separa los datos opcionales o sensibles en otra tabla con la misma clave.

CREATE TABLE cliente_documentos (
  cliente_id INT PRIMARY KEY REFERENCES clientes (id),
  cpf VARCHAR(14)
);

166. Clave natural vs sustituta

Una clave sustituta (un id secuencial) es estable; una clave natural (una identificación fiscal, un correo) cambia más de lo que parece.

CREATE TABLE clientes (
  id BIGSERIAL PRIMARY KEY,      -- sustituta
  cpf VARCHAR(14) UNIQUE         -- natural, con unicidad garantizada
);

167. Soft delete

Marcar en lugar de borrar preserva el historial — pero exige filtrar en cada consulta.

ALTER TABLE clientes ADD COLUMN excluido_em TIMESTAMP;
SELECT * FROM clientes WHERE excluido_em IS NULL;

168. Tabla de historial

Guarda el estado a lo largo del tiempo, en lugar de sobrescribirlo.

CREATE TABLE precos_historico (
  produto_id INT, preco DECIMAL(12,2),
  inicio DATE NOT NULL, fim DATE
);

169. Un estándar de nombres

Elige un estándar (snake_case, singular o plural) y mantenlo. La consistencia vale más que la elección en sí.

-- tablas en plural, columnas en snake_case, FK como <tabla>_id
CREATE TABLE pedidos (id SERIAL PRIMARY KEY, cliente_id INT);

170. Tipos coherentes entre tablas

La FK y la PK necesitan el mismo tipo, si no el JOIN convierte y pierde el índice.

-- clientes.id BIGINT  ->  pedidos.cliente_id BIGINT (no INTEGER)

Carga y movimiento de datos

Importar, exportar y mover volumen sin tumbar la base.

171. COPY — importar un CSV

La carga masiva nativa de PostgreSQL, órdenes de magnitud más rápida que un INSERT fila a fila.

COPY clientes (nome, email) FROM '/tmp/clientes.csv' WITH (FORMAT csv, HEADER true);

172. COPY TO — exportar

La misma vía, en sentido contrario.

COPY (SELECT * FROM pedidos WHERE status = 'pago') TO '/tmp/pagos.csv' WITH (FORMAT csv, HEADER true);

173. \copy en el cliente

Una variante de psql que lee/escribe en la máquina del cliente, sin necesitar acceso al disco del servidor.

\copy clientes FROM 'clientes.csv' WITH (FORMAT csv, HEADER true)

174. LOAD DATA INFILE — MySQL

El equivalente del COPY en MySQL.

LOAD DATA INFILE '/tmp/clientes.csv'
INTO TABLE clientes FIELDS TERMINATED BY ',' IGNORE 1 LINES;

175. BULK INSERT — SQL Server

La carga masiva en SQL Server.

BULK INSERT clientes FROM 'C:\dados\clientes.csv'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',');

176. Tabla de staging

Importa en crudo a una tabla temporal, valida, y solo entonces muévelo a la tabla final.

CREATE TABLE stg_clientes (nome TEXT, email TEXT);
-- COPY hacia stg_clientes
INSERT INTO clientes (nome, email)
SELECT nome, LOWER(email) FROM stg_clientes WHERE email LIKE '%@%';

177. Deduplicar durante la carga

DISTINCT ON (PostgreSQL) o ROW_NUMBER resuelven los duplicados del archivo de origen.

INSERT INTO clientes (email, nome)
SELECT DISTINCT ON (email) email, nome FROM stg_clientes ORDER BY email, nome;

178. Actualizar por lotes

Un UPDATE de millones de filas a la vez bloquea la tabla. Divídelo en bloques.

UPDATE pedidos SET processado = true
WHERE id IN (SELECT id FROM pedidos WHERE NOT processado LIMIT 10000);

179. Tabla temporal

Existe solo en la sesión. Ideal para los pasos intermedios de un ETL.

CREATE TEMP TABLE tmp_resultado AS
SELECT cliente_id, SUM(total) AS total FROM pedidos GROUP BY cliente_id;

180. Generar datos de prueba

Datos sintéticos para medir el rendimiento con un volumen realista.

INSERT INTO pedidos (cliente_id, total, criado_em)
SELECT (random()*1000)::int, (random()*500)::numeric(12,2),
       CURRENT_DATE - (random()*365)::int
FROM generate_series(1, 100000);

Usuarios y permisos

El menor privilegio posible: la aplicación no necesita ser dueña de la base.

181. CREATE USER

Crea un usuario/role con contraseña.

CREATE USER app_web WITH PASSWORD 'senha-forte-aqui';

182. GRANT sobre una tabla

Concede solo las operaciones necesarias.

GRANT SELECT, INSERT, UPDATE ON pedidos TO app_web;

183. GRANT de solo lectura

El perfil clásico para BI e informes.

GRANT CONNECT ON DATABASE loja TO bi_leitura;
GRANT USAGE ON SCHEMA public TO bi_leitura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO bi_leitura;

184. REVOKE

Retira un permiso concedido.

REVOKE DELETE ON pedidos FROM app_web;

185. Roles y herencia

Agrupa los permisos en un role y concede el role a los usuarios.

CREATE ROLE leitura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO leitura;
GRANT leitura TO analista1, analista2;

186. Permiso por defecto para las tablas futuras

Sin esto, cada tabla nueva necesita un GRANT manual.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO leitura;

187. Permiso por columna

Se puede liberar solo algunas columnas de la tabla.

GRANT SELECT (id, nome, cidade) ON clientes TO app_web;

188. Row Level Security

Cada usuario ve solo sus filas — multi-tenant garantizado por la base.

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant ON pedidos
  USING (tenant_id = current_setting('app.tenant')::int);

189. Ver los permisos

Una auditoría rápida de quién puede qué.

SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_name = 'pedidos';

190. Nunca uses el superusuario en la aplicación

La cuenta de la aplicación no debería poder crear/borrar tablas ni leer datos de otros esquemas.

-- Mal: un DATABASE_URL con el usuario postgres
-- Bien: un usuario dedicado, con el GRANT mínimo

Metadatos y mantenimiento

Preguntarle a la base sobre sí misma — y mantenerla sana.

191. Listar las tablas

El catálogo estándar information_schema funciona en la mayoría de las bases.

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

192. Listar las columnas

El tipo, la nulabilidad y el valor por defecto de cada columna.

SELECT column_name, data_type, is_nullable
FROM information_schema.columns WHERE table_name = 'pedidos';

193. Ver la definición de una tabla

Atajos del cliente que ahorran consultar el catálogo.

\d pedidos            -- psql
SHOW CREATE TABLE pedidos;   -- MySQL
sp_help 'pedidos';           -- SQL Server

194. El tamaño de las tablas

Descubre quién está ocupando el disco.

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS tamanho
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

195. Recuento estimado de filas

Un COUNT(*) en una tabla gigante es caro; la estimación del catálogo es instantánea.

SELECT reltuples::bigint AS estimativa FROM pg_class WHERE relname = 'pedidos';

196. ANALYZE — actualizar las estadísticas

El optimizador decide el plan basándose en ellas. Una estadística vieja genera un plan malo.

ANALYZE pedidos;

197. VACUUM

Recupera el espacio de las filas eliminadas en PostgreSQL (MVCC).

VACUUM ANALYZE pedidos;

198. Conexiones activas

Ver quién está conectado y qué se está ejecutando.

SELECT pid, usename, state, query FROM pg_stat_activity WHERE state <> 'idle';

199. Terminar una consulta atascada

Cancela (o mata) una sesión que está reteniendo la base.

SELECT pg_cancel_backend(12345);    -- pide que se cancele
SELECT pg_terminate_backend(12345); -- termina la conexión

200. Las consultas más lentas

La extensión pg_stat_statements muestra dónde se va el tiempo de verdad.

SELECT query, calls, mean_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;