Referencia de SQL

SELECT col1, col2 FROM table_name;

Elige columnas de una tabla.

SELECT * FROM t
WHERE col = 5 AND status = 'active';

Filtra filas: =, <>, <, >, AND, OR, IN, BETWEEN, LIKE.

SELECT * FROM t ORDER BY created_at DESC;

Ordena el resultado. ASC ascendente (por defecto), DESC descendente.

SELECT * FROM t ORDER BY id LIMIT 10;

Limita el número de filas devueltas.

SELECT * FROM a JOIN b ON a.id = b.a_id;

Solo las filas que tienen correspondencia en ambas tablas.

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

Todas las filas de la tabla izquierda; NULL a la derecha si no hay correspondencia.

SELECT u.name
FROM users u
JOIN orders o ON o.user_id = u.id;

Nombres cortos para las tablas — obligatorios cuando las columnas comparten nombre.

SELECT * FROM sizes CROSS JOIN colors;

Producto cartesiano — cada fila izquierda con cada fila derecha. Para generar todas las combinaciones.

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

Todas las filas de ambas tablas; NULL donde no hay correspondencia. MySQL no tiene FULL JOIN — se emula con LEFT ∪ RIGHT.

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Una tabla unida consigo misma mediante alias — para jerarquías como «empleado → jefe».

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Filas de la izquierda SIN correspondencia a la derecha — «usuarios sin pedidos». Alternativa a NOT EXISTS.

SELECT COUNT(*), COUNT(email) FROM users;

Cuenta filas de un grupo. COUNT(*) — todas las filas, COUNT(col) — solo no NULL, COUNT(DISTINCT col) — valores únicos.

SELECT SUM(amount) FROM orders;

Suma de valores numéricos de un grupo. Los NULL se ignoran. Devuelve NULL (no 0) si el grupo está vacío.

SELECT AVG(price) FROM products;

Media aritmética. Los NULL se excluyen del divisor. Convierte una columna entera a numeric o se truncan los decimales.

SELECT MIN(created_at) FROM orders;

Valor mínimo de un grupo. Funciona con números, fechas y cadenas. Los NULL se ignoran.

SELECT MAX(created_at) FROM orders;

Valor máximo de un grupo. Funciona con números, fechas y cadenas. Los NULL se ignoran.

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

Agrupa filas. Toda columna de SELECT que no sea de agregación debe estar en GROUP BY.

SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Filtra sobre agregados — como WHERE pero después de GROUP BY.

SELECT DISTINCT country FROM users;

Elimina las filas duplicadas del resultado.

COUNT(*) FILTER (WHERE type = 'view') AS views

Agregado condicional: COUNT/SUM solo sobre las filas que cumplen el WHERE. Sustituye tres consultas separadas por una sola.

STRING_AGG(name, ', ' ORDER BY created_at)

Concatena valores en una sola cadena con un separador. El ORDER BY interno fija el orden.

ARRAY_AGG(amount ORDER BY created_at)

Reúne valores en un array. Útil cuando una fila de auditoría necesita todo el historial en una celda.

GROUP BY GROUPING SETS ((kind), (user_id), ())

Varios niveles de agrupación en una consulta — fila a fila: por kind, por user_id y un total general.

GROUP BY ROLLUP (DATE_TRUNC('month', ts))

El mismo nivel de agrupación más una fila de total general (una única fila NULL al final).

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)

Mediana / cuantil. Más resistente a los valores atípicos que AVG.

SELECT tag FROM articles, UNNEST(tags) tag

Expande un array en filas — una fila por cada elemento del array.

SELECT COUNT(DISTINCT user_id) FROM events;

Cuenta los valores distintos. Costoso en tablas grandes — usa APPROX_COUNT_DISTINCT / HLL cuando basta con una estimación.

SELECT BOOL_AND(active), BOOL_OR(is_admin) FROM users;

Agregados booleanos: BOOL_AND es true si TODAS las filas son true; BOOL_OR si al menos una lo es. Los NULL se ignoran.

SELECT dept, EVERY(salary > 0) FROM emp GROUP BY dept;

El sinónimo estándar de SQL para BOOL_AND — true cuando la condición se cumple en todas las filas del grupo.

SELECT STDDEV_SAMP(amount), STDDEV_POP(amount) FROM orders;

Desviación estándar: _SAMP para una muestra (divisor n-1), _POP para toda la población (divisor n). STDDEV a secas equivale a STDDEV_SAMP.

SELECT VAR_SAMP(amount), VAR_POP(amount) FROM orders;

Varianza — el cuadrado de la desviación estándar. _SAMP para muestra, _POP para población. VARIANCE a secas equivale a VAR_SAMP.

SELECT MODE() WITHIN GROUP (ORDER BY status) FROM tickets;

La moda — el valor más frecuente del grupo. En caso de empate gana el primero según ORDER BY.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) FROM orders;

Percentil discreto — devuelve un valor que realmente existe en los datos, a diferencia de PERCENTILE_CONT que interpola.

SELECT BIT_OR(flags), BIT_AND(flags) FROM permissions;

Agregados a nivel de bits sobre una columna integer: BIT_OR reúne todos los bits activos, BIT_AND conserva los comunes a todas las filas. Para máscaras de banderas.

SELECT JSONB_AGG(t ORDER BY t.id) FROM tasks t;

Agrupa filas en un array JSON — útil para devolver datos anidados en una sola consulta. JSONB_AGG lo guarda como jsonb (más rápido, sin claves duplicadas).

SELECT JSONB_OBJECT_AGG(key, value) FROM settings;

Convierte pares clave-valor en un único objeto JSON. Ideal para transformar una tabla de ajustes en un mapa.

SELECT CORR(price, sales) FROM products;

Coeficiente de correlación de Pearson entre dos columnas: de -1 a 1. Una medida de asociación lineal.

SELECT REGR_SLOPE(y, x), REGR_INTERCEPT(y, x) FROM points;

Pendiente e intersección de la recta de regresión de y sobre x — una tendencia en un solo agregado, sin paquetes estadísticos externos.

SELECT REGR_R2(y, x) FROM points;

El coeficiente de determinación R² de la regresión de y sobre x: 0..1, qué tan bien la recta se ajusta a los datos.

SELECT user_id,
  MAX(amount) FILTER (WHERE kind = 'deposit')  AS deposit,
  MAX(amount) FILTER (WHERE kind = 'withdraw') AS withdraw
FROM tx GROUP BY user_id;

Transforma filas en columnas (pivot): MAX/SUM con FILTER por categoría. Sustituye un montón de agregados CASE escritos a mano.

SELECT region, product, SUM(amount)
FROM sales
GROUP BY CUBE (region, product);

Todas las combinaciones de agrupación a la vez: por region, por product, por ambos y un total general. Para informes de tabla cruzada.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Comprueba la pertenencia a una lista generada por otra consulta.

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Conserva la fila si la consulta interna encuentra al menos una fila coincidente.

SELECT
  name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

Una subconsulta que devuelve un único valor — puede ir dentro de SELECT.

SELECT id FROM a
UNION ALL
SELECT id FROM b;

Apila los resultados de dos consultas (mismo número de columnas, tipos compatibles). UNION elimina duplicados, UNION ALL los conserva (y es más rápido).

SELECT user_id FROM purchases
INTERSECT
SELECT user_id FROM refunds;

Filas presentes en AMBAS consultas. En MySQL — desde la versión 8.0.31.

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM banned;

Filas de la primera consulta que NO están en la segunda. En Oracle es MINUS.

SELECT
  name,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM players;

Número secuencial único para cada fila dentro de la ventana.

RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

Rango con saltos (RANK) o sin saltos (DENSE_RANK) cuando hay empates.

SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)

Divide la ventana en grupos — el agregado se calcula por grupo.

LAG(price, 1) OVER (ORDER BY date)

Valor de la fila anterior (LAG) o siguiente (LEAD) de la ventana.

NTILE(4) OVER (ORDER BY score DESC)

Divide las filas en N grupos del mismo tamaño en orden. Con cantidades desiguales los grupos quedan 3-3-2-2 (los sobrantes van a los de número más bajo).

LAST_VALUE(score) OVER (
  PARTITION BY team_id ORDER BY score DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Primer / último valor de la ventana. Para LAST_VALUE hay que ampliar el marco con ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — si no, el marco por defecto recorta el borde derecho.

PERCENT_RANK() OVER (ORDER BY score DESC, player_id)

Rango percentil de 0 a 1. Una segunda columna de ordenación hace el resultado determinista cuando hay empates.

NTH_VALUE(amount, 2) OVER (
  PARTITION BY customer_id ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

El N-ésimo valor de la ventana. También requiere el marco ampliado.

AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Ventanas deslizantes: «los últimos 7 días incluidos», «media de 3 días», etc.

WITH active AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active WHERE country = 'RU';

Un resultado temporal con nombre — divide una consulta grande en pasos.

WITH a AS (...), b AS (...)
SELECT * FROM a JOIN b ON ...;

Varios CTE separados por comas, se leen de arriba abajo.

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id FROM emp e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

Recorre una jerarquía: consulta de anclaje → UNION ALL → paso recursivo. Ideal para organigramas, grafos y cadenas.

FROM customers c
LEFT JOIN LATERAL (
  SELECT * FROM orders WHERE customer_id = c.id
  ORDER BY amount DESC LIMIT 2
) l ON true

La subconsulta ve las columnas de la fila externa. Ideal para «top-N por cada X». LEFT JOIN LATERAL … ON true conserva las filas externas sin coincidencia; la forma con coma las descarta en silencio.

generate_series('2024-01-01'::date, '2024-01-15'::date, '1 day')

Genera un calendario / eje. Incluye ambos extremos. Truco habitual para «rellenar con cero los días que faltan».

INSERT INTO t (col1, col2) VALUES (1, 'a'), (2, 'b');

Agrega filas a una tabla.

UPDATE t SET col = 'x' WHERE id = 5;

Modifica filas existentes. Incluye siempre WHERE — si no, se actualizan TODAS las filas.

DELETE FROM t WHERE id = 5;

Elimina filas. Incluye siempre WHERE.

INSERT INTO t (id) VALUES (1)
ON CONFLICT (id) DO NOTHING;

Inserción idempotente — al volver a ejecutarse omite sin avisar las filas que ya existen.

INSERT INTO t (id, n) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE
  SET n = t.n + EXCLUDED.n;

UPSERT: insertar o actualizar. EXCLUDED.col es el valor que intentamos insertar.

MERGE INTO t USING src ON t.id = src.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Postgres 15+ — alternativa a UPSERT con ramas MATCHED / NOT MATCHED y condiciones en cada una.

INSERT INTO t (name) VALUES ('a')
RETURNING id, name;

Devuelve las filas recién insertadas / actualizadas / eliminadas en la misma sentencia — sin un segundo viaje al servidor.

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.country = 'US';

DELETE al estilo JOIN — filtra por otra tabla sin una subconsulta.

UPDATE customers c SET total = s.total
FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) s
WHERE c.id = s.customer_id;

UPDATE masivo impulsado por una subconsulta de agregación.

WITH moved AS (
  DELETE FROM orders WHERE old RETURNING *
)
INSERT INTO archive SELECT * FROM moved;

Archivado atómico: mueve las filas en una sola sentencia, sin ventana de carrera entre DELETE e INSERT.

CREATE TABLE t (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Crea una tabla con columnas tipadas.

ALTER TABLE t ADD COLUMN created_at TIMESTAMP;

Modifica una tabla existente.

ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

Rechaza valores no válidos a nivel de base de datos, no en el código.

FOREIGN KEY (post_id) REFERENCES posts(id)
  ON DELETE CASCADE   -- or SET NULL / RESTRICT

Qué hacer con la fila hija cuando se elimina la padre: propagar en cascada, poner la clave foránea a NULL o bloquear.

ALTER TABLE t
  ADD CONSTRAINT fk REFERENCES p(id) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT fk;

Agrega una clave foránea a una tabla grande en producción sin un bloqueo pesado: NOT VALID es instantáneo, VALIDATE no bloquea a los escritores.

total NUMERIC GENERATED ALWAYS AS (price * (1 + tax)) STORED

El valor de la columna se calcula automáticamente — la fórmula vive en un único lugar.

CREATE UNIQUE INDEX u ON users (email)
WHERE deleted_at IS NULL;

Unicidad solo sobre las filas activas — para el soft-delete, de modo que los usuarios puedan registrarse de nuevo tras la eliminación.

CREATE TABLE logs (...) PARTITION BY RANGE (ts);
CREATE TABLE logs_2024 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Divide una tabla grande por rangos. Las particiones antiguas se eliminan en milisegundos.

CREATE TRIGGER touch BEFORE UPDATE ON notes
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Lógica automática a nivel de base de datos — por ejemplo, actualizar updated_at sin tocar el código de la aplicación.

CREATE MATERIALIZED VIEW v AS SELECT ...;
REFRESH MATERIALIZED VIEW v;

Resultado cacheado de una consulta pesada. Se actualiza de forma programada con REFRESH.

LOWER(name), UPPER(code), LENGTH(text)

Minúsculas, mayúsculas, longitud de la cadena.

CONCAT(first_name, ' ', last_name)

Concatena cadenas de texto. PostgreSQL también admite el operador || (en MySQL, || es el OR lógico por defecto, no la concatenación).

EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)

Extrae una parte de una fecha — año, mes, día.

DATE_TRUNC('month', created_at)

Trunca una marca de tiempo hacia abajo a un período (día/semana/mes). La herramienta básica para agrupar por tiempo.

WHERE created_at >= NOW() - INTERVAL '7 days'

Instante actual (NOW()) / hoy (CURRENT_DATE) y aritmética con intervalos — «en los últimos 7 días».

CAST(price AS INTEGER)   -- or price::int

Convierte un valor a otro tipo. CAST(x AS type) es estándar; x::type es la forma corta de PostgreSQL.

TRIM(name), SUBSTRING(code FROM 1 FOR 3), REPLACE(phone, '-', '')

Recorta espacios, extrae una subcadena, reemplaza un fragmento. Limpieza de cadenas del día a día.

SPLIT_PART(email, '@', 2)   -- domain from an e-mail

Divide una cadena por un separador y toma la N-ésima parte. En MySQL — SUBSTRING_INDEX.

WHERE name ILIKE '%ivan%'

LIKE sin distinción de mayúsculas (PostgreSQL). En MySQL, el LIKE normal ya ignora mayúsculas con la colación por defecto.

LEFT(code, 3), RIGHT(phone, 4)

Toma los primeros N caracteres (LEFT) o los últimos N (RIGHT) de una cadena.

POSITION('@' IN email), STRPOS(email, '@')

Posición de la primera aparición (desde 1), 0 si no se encuentra. STRPOS es la forma corta de PostgreSQL.

LPAD(id::text, 6, '0'), RPAD(name, 20, ' ')

Rellena una cadena hasta una longitud dada por la izquierda (LPAD) o la derecha (RPAD). Uso típico: rellenar un id con ceros.

INITCAP('john DOE')   -- John Doe

Pone en mayúscula la primera letra de cada palabra y el resto en minúscula. No existe en MySQL.

REPEAT('ab', 3)   -- ababab

Repite una cadena N veces. Útil para marcadores de posición y gráficos de barras en texto.

REVERSE(name)

Invierte una cadena carácter a carácter. A veces se usa para indexar por sufijo.

char_length(name), char_length('açai')   -- 4

Longitud de la cadena en CARACTERES (no en bytes) — importa con UTF-8. Sinónimo de character_length.

REGEXP_REPLACE(phone, '[^0-9]', '', 'g')

Reemplazo mediante expresión regular. La bandera 'g' reemplaza todas las coincidencias; sin ella, solo la primera.

SELECT (REGEXP_MATCHES(url, '/(\d+)'))[1] AS id;

Devuelve los grupos capturados de la regex como un array. Con la bandera 'g' produce una fila por coincidencia.

REGEXP_SPLIT_TO_ARRAY('a, b,c', '\s*,\s*')

Divide una cadena por un separador regex en un array. También existe …TO_TABLE para filas.

TRANSLATE(code, 'abc', 'xyz')   -- a->x, b->y, c->z

Sustitución carácter a carácter entre dos conjuntos. Los caracteres sobrantes del primer conjunto se eliminan. No es REPLACE.

BTRIM(code, '0'), LTRIM(s), RTRIM(s, '/')

Recorta los caracteres indicados por ambos extremos (BTRIM), la izquierda (LTRIM) o la derecha (RTRIM); espacios por defecto.

FORMAT('Hi %s, id=%L', name, id)

Construye una cadena a partir de una plantilla: %s valor, %I identificador, %L literal seguro. Clave en SQL dinámico.

WHERE STARTS_WITH(path, '/api/')

Comprueba si una cadena empieza por un prefijo — más legible que LIKE 'x%'. Disponible desde PostgreSQL 11.

ascii('A')   -- 65
chr(65)      -- A

Código del primer carácter (ascii) y el carácter de un código (chr). En MySQL la inversa se llama CHAR.

to_hex(255)   -- 'ff'

Convierte un entero en su cadena hexadecimal. Útil para colores, máscaras de bits y depuración.

ROUND(3.14159)        -- 3
ROUND(2.5)            -- banker? no: 3

Redondea al entero más cercano. Las mitades se redondean alejándose del cero (2.5 → 3).

ROUND(3.14159, 2)     -- 3.14
ROUND(12345.6, -2)    -- 12300

Redondea a n decimales; un n negativo redondea a la izquierda del punto. Solo funciona con numeric, no con float.

CEIL(4.1)   -- 5
CEIL(-4.1)  -- -4

Redondea hacia arriba al siguiente entero. CEILING es un sinónimo.

FLOOR(4.9)   -- 4
FLOOR(-4.1)  -- -5

Redondea hacia abajo al entero anterior. Con negativos se aleja del cero.

TRUNC(3.99)     -- 3
TRUNC(3.456, 2) -- 3.45

Descarta la parte decimal (hacia el cero), sin redondear. En MySQL es TRUNCATE(x, n).

ABS(-7)   -- 7

Valor absoluto — la magnitud sin signo.

MOD(10, 3)   -- 1

Resto de la división. Útil para «cada N-ésima fila» y la paridad; el signo del resultado sigue al dividendo.

POWER(2, 10)   -- 1024

Eleva a una potencia. POW es un sinónimo.

SQRT(144)   -- 12

Raíz cuadrada. Un argumento negativo provoca un error.

EXP(1)    -- 2.7182818...
LN(2.718) -- ~1

Exponencial e^x y logaritmo natural (base e). LN(0) y LN(negativo) dan error.

LOG(100)     -- 2  (base 10)
LOG(2, 8)    -- 3  (base 2)

En PostgreSQL LOG(x) es base 10, LOG(b, x) usa una base arbitraria. Atención: en MySQL LOG(x) es el logaritmo natural.

SIGN(-42)  -- -1
SIGN(0)    -- 0

Signo de un número: -1, 0 o 1. Útil para bifurcar según la dirección del cambio.

GREATEST(a, b, c), LEAST(a, b, c)

El mayor / menor entre los argumentos dentro de una fila (no es agregación). Los argumentos NULL se ignoran.

SELECT * FROM t ORDER BY RANDOM() LIMIT 5;

Número aleatorio en [0,1). En MySQL es RAND(). ORDER BY RANDOM() da una muestra aleatoria, pero es costoso en tablas grandes.

DIV(7, 2)   -- 3
7 / 2       -- 3 when both are int

División entera que descarta el resto. En PostgreSQL / ya divide entero si ambos operandos son enteros; MySQL usa el operador DIV para esto.

WIDTH_BUCKET(score, 0, 100, 10)  -- bucket 1..10

Asigna un valor a un cubo de histograma de ancho igual entre dos límites. Para distribuciones y agrupación por rangos.

AGE(end_ts, start_ts)   -- or AGE(birthday) vs now
AGE('2024-03-01', '2024-01-15')

Diferencia entre dos fechas como intervalo (años/meses/días), no en segundos. Con un solo argumento cuenta desde hoy — útil para la edad.

DATE_PART('hour', created_at), DATE_PART('dow', created_at)

Forma de función de EXTRACT — extrae una parte de fecha/hora como número. El campo es una cadena, así que es fácil pasarlo dinámicamente.

EXTRACT(EPOCH FROM (ended_at - started_at)) AS seconds

Convierte un intervalo o timestamp en segundos (tiempo Unix). La forma habitual de medir una duración en segundos — luego divide entre 60/3600.

TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI'), TO_CHAR(amount, 'FM999G999D00')

Formatea una fecha/número en una cadena mediante una plantilla (YYYY, MM, DD, HH24...). Las plantillas de PostgreSQL difieren del DATE_FORMAT de MySQL.

TO_DATE('2024-03-15', 'YYYY-MM-DD')

Convierte una cadena en una fecha con una plantilla explícita. Más fiable que el cast ::date cuando el formato no es estándar.

TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
TO_TIMESTAMP(1710512400)   -- from Unix epoch

Convierte una cadena en un timestamp por plantilla, o construye un timestamptz a partir de segundos Unix (argumento numérico).

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP;

Hora de inicio de la transacción: CURRENT_TIMESTAMP incluye zona horaria (timestamptz), LOCALTIMESTAMP no. Constante dentro de una misma transacción.

SELECT CURRENT_DATE, CURRENT_TIME;

Solo la fecha de hoy (CURRENT_DATE) o solo la hora (CURRENT_TIME) — sin paréntesis; son valores especiales de SQL, no funciones.

SELECT order_date + 7, due_date - 1, end_dt - start_dt AS days;

Puedes sumar/restar días enteros a un date (date + 7). Restar dos date da un número entero de días; restar dos timestamp da un intervalo.

make_date(2024, 3, 15), make_time(14, 30, 0)

Construye una fecha u hora a partir de números separados de año/mes/día, hora/minuto/segundo — sin lidiar con cadenas de formato.

make_timestamp(2024, 3, 15, 14, 30, 0)
make_interval(days => 10, hours => 2)

Construye un timestamp o intervalo a partir de componentes numéricos. make_interval acepta argumentos con nombre (days =>, hours =>).

ts_utc AT TIME ZONE 'Europe/Moscow'
local_ts AT TIME ZONE 'UTC'

Lleva un instante a otra zona horaria. Sobre un timestamptz da la hora local de esa zona; sobre un timestamp sin zona lo interpreta como hora de esa zona.

JUSTIFY_HOURS(INTERVAL '36 hours')   -- 1 day 12:00:00

Normaliza un intervalo: convierte las horas sobrantes en días y los días en meses. Convierte «50 hours» en un legible «2 days 02:00:00».

DATE_BIN('15 minutes', ts, TIMESTAMP '2024-01-01')

Ajusta un timestamp al inicio de un bucket de ancho arbitrario (p. ej. 15 minutos) desde un origen. Más flexible que DATE_TRUNC. Postgres 14+.

(start_a, end_a) OVERLAPS (start_b, end_b)

Comprueba si dos periodos de tiempo se solapan. Útil para detectar conflictos de reservas o turnos.

now()::timestamptz, '2024-03-15 10:00'::timestamp

timestamptz guarda el instante en UTC y aplica la zona al mostrarlo; timestamp es hora «ingenua» sin zona. Para eventos casi siempre quieres timestamptz.

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 70 THEN 'B'
  ELSE 'C'
END

Lógica condicional en línea — como un if/else dentro de SELECT.

COALESCE(nickname, full_name, 'Anonymous')

Devuelve el primer valor no NULL de la lista.

NULLIF(divisor, 0)

Convierte un valor en NULL cuando es igual al segundo argumento. Útil para evitar la división por cero.

-- = NULL is never true — use IS NULL
WHERE deleted_at IS NULL
-- NULL-safe equality:
WHERE a IS DISTINCT FROM b

Comparar con NULL usando = siempre da «desconocido» (no TRUE/FALSE). Usa IS NULL para comprobar; usa IS DISTINCT FROM para igualdad segura ante NULL.

payload->>'target'

Extrae un valor de un JSONB como text — para operaciones con cadenas y comparaciones.

payload @> '{"plan":"pro"}'

¿Contiene el JSONB el fragmento indicado? Usa un índice GIN — rápido en tablas grandes.

CREATE INDEX idx ON events USING GIN (payload jsonb_path_ops)

Índice óptimo para consultas @> sobre JSONB. Más compacto que el jsonb_ops por defecto.

data->'user'->>'name'   -- -> keeps json, ->> as text

-> extrae un campo/elemento como jsonb (para seguir navegando), ->> como text. Clave por cadena, índice de array por número.

data #>> '{address,city}'   -- text at a nested path

Lee un valor en una ruta anidada dada como array de claves: #> como jsonb, #>> como text. Más corto que encadenar ->.

jsonb_build_object('id', id, 'name', name)

Construye un objeto JSON a partir de pares clave, valor alternados. Se conservan los tipos de los valores (los números siguen siendo números).

jsonb_build_array(id, name, created_at)

Construye un array JSON a partir de los argumentos dados de cualquier tipo.

jsonb_agg(item ORDER BY created_at)

Agregado: reúne un grupo de filas en un array JSON. El ORDER BY interno fija el orden de los elementos.

SELECT e FROM t, jsonb_array_elements(t.tags) AS e

Expande un array JSON en filas — una fila por elemento. La variante _text devuelve text en lugar de jsonb.

jsonb_array_length(data->'items')

Longitud de un array JSON. Da error si el valor no es un array — protégelo con jsonb_typeof.

jsonb_set(data, '{address,city}', '"Lima"')

Devuelve una copia del JSON con el valor de la ruta reemplazado. create_missing=true (por defecto) añade la clave si falta.

SELECT key, value FROM jsonb_each(data)

Expande un objeto JSON en filas (key, value) — una por clave. La variante _text devuelve value como text.

SELECT jsonb_object_keys(data)

Devuelve los nombres de las claves de nivel superior de un objeto JSON, una fila por clave.

data ? 'email'        -- has key?
data ?| array['a','b'] -- any of these keys?

Pruebas de existencia de claves: ? una clave, ?| alguna de estas, ?& todas. Solo nivel superior; admiten índice GIN.

data || '{"verified":true}'

Fusiona dos valores JSONB: las claves de la derecha sobrescriben las de la izquierda (superficial, no recursivo). Útil para una actualización parcial.

data - 'temp'              -- drop a key
data #- '{address,zip}'    -- drop at a path

Elimina una clave/elemento: - por clave o índice de nivel superior, #- en una ruta anidada. Devuelve un nuevo JSONB.

to_jsonb(row_var)   -- whole row as a json object

Convierte cualquier valor/fila/array de SQL en jsonb. Una fila entera de la tabla se vuelve un objeto JSON columna → valor.

jsonb_typeof(data->'price')   -- 'number','string',...

El tipo del valor JSON como text: object, array, string, number, boolean, null. Útil antes de jsonb_array_length, etc.

jsonb_pretty(data)

Formatea JSONB con sangría para una salida legible o depuración.

CREATE INDEX i ON orders (id) WHERE status = 'pending';

Índice solo sobre el subconjunto «caliente» de filas — más compacto y más rápido de recorrer.

CREATE INDEX i ON orders (customer_id, created_at DESC);

Cubre el filtro y la ordenación en una sola lectura. El orden de las columnas importa.

-- bad : WHERE EXTRACT(YEAR FROM ts) = 2024
-- good: WHERE ts >= '2024-01-01' AND ts < '2025-01-01'

No envuelvas la columna en una función — el índice no se usará. Reescríbelo como un rango.

-- NOT IN collapses to 0 rows on any NULL
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NOT IN se rompe sin avisar ante cualquier NULL en la subconsulta. NOT EXISTS es seguro frente a NULL.

CREATE INDEX CONCURRENTLY i ON events (user_id, kind);

Construye un índice en una tabla caliente sin un bloqueo pesado. Está prohibido dentro de una transacción.

EXPLAIN SELECT * FROM orders WHERE user_id = 5;

Muestra el plan de la consulta sin ejecutarla: qué exploraciones (Seq Scan / Index Scan), orden de joins, filas estimadas.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 5;

Ejecuta la consulta y muestra el tiempo y las filas REALES frente a la estimación. Una gran diferencia estimación↔real indica estadísticas obsoletas o un índice ausente.

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

Bloquea filas hasta el final de la transacción. Estándar para las transferencias de dinero.

UPDATE accounts SET balance = balance - 200
WHERE id = 1 AND balance >= 200;

Comprobación y actualización en una única sentencia atómica. Si se actualizan 0 filas — muestra «fondos insuficientes».

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED LIMIT 1;

Cola de workers: cada worker toma su propia tarea, saltándose las filas bloqueadas por otros.

UPDATE counters SET n = n + 1 WHERE id = 1;

Un único UPDATE incrementa el contador de forma segura ante la concurrencia. SELECT seguido de UPDATE pierde incrementos.

GRANT SELECT, INSERT ON orders TO analyst;

Concede privilegios sobre un objeto a un rol/usuario. Enumera las acciones necesarias (SELECT, INSERT, UPDATE, DELETE).

REVOKE INSERT ON orders FROM analyst;

Retira privilegios concedidos anteriormente.

CREATE ROLE analyst LOGIN PASSWORD 'secret';

Crea un rol (usuario/grupo). Los privilegios se conceden al rol y los usuarios son miembros de él.

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

El patrón estándar de solo lectura: acceso al esquema + SELECT en todas las tablas + una regla para futuras tablas con ALTER DEFAULT PRIVILEGES.