Tablas, consultas e índices
Ejecuta el ejemplo en una base de datos de prueba. La columna de identidad genera el identificador del envío. La restricción de estado limita el texto permitido, mientras que NOT NULL rechaza, por separado, la ausencia de valor. timestamptz representa un instante; la hora mostrada depende de la zona horaria de la sesión.
CREATE TABLE dispatches (
dispatch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch text NOT NULL,
status text NOT NULL CHECK (status IN ('open', 'closed')),
scheduled_at timestamptz,
details jsonb NOT NULL DEFAULT '{}'::jsonb
);
SELECT branch, COUNT(*) AS open_jobs
FROM dispatches
WHERE status = 'open'
GROUP BY branch
HAVING COUNT(*) > 1
ORDER BY open_jobs DESC, branch;
CREATE INDEX dispatch_open_branch
ON dispatches (branch) WHERE status = 'open';
CREATE INDEX dispatch_priority
ON dispatches ((details ->> 'priority'));
CREATE INDEX dispatch_details_gin
ON dispatches USING gin (details);
SELECT dispatch_id FROM dispatches
WHERE details @> '{"priority":"high"}'::jsonb;
BEGIN;
SAVEPOINT before_change;
UPDATE dispatches SET status = 'closed' WHERE dispatch_id = 1;
ROLLBACK TO SAVEPOINT before_change;
COMMIT;
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT dispatch_id FROM dispatches
WHERE branch = 'West' AND status = 'open';
La consulta agrupada filtra las filas antes de agregarlas y aplica HAVING después de agrupar. El segundo criterio de ordenación resuelve los empates de forma estable. COUNT(*) cuenta filas; COUNT(columna) cuenta valores no nulos. Para detectar un valor ausente, usa IS NULL; = NULL no devuelve true.
La elección del índice
El índice B-tree parcial contiene los envíos abiertos y sirve para el filtro de sucursal correspondiente. El índice de expresión usa el texto extraído de la prioridad. El índice GIN permite operaciones de JSONB como la comprobación de contención del ejemplo. Crear los tres demuestra su sintaxis; no significa que una aplicación necesite todos.
Cada índice consume almacenamiento y añade trabajo a las escrituras.
Que un índice exista no garantiza que se use. Revisa el plan y la distribución de los datos en la lección de B-tree y la de índices parciales y de expresión. Para un índice de varias columnas, considera las clases de operadores, el orden de las columnas y los predicados de la consulta.
Transacciones y planes
ROLLBACK TO SAVEPOINT deshace la actualización y mantiene abierta la transacción. COMMIT la termina. Si una transacción falla, haz rollback antes de ejecutar instrucciones ajenas a ella. Los contadores de secuencia son una excepción a lo que normalmente se espera de un rollback.
EXPLAIN calcula un plan estimado. Si añades ANALYZE, ejecuta la sentencia, incluidas las modificaciones de datos que contenga. En una base de prueba, puedes envolver una escritura exploratoria en BEGIN y ROLLBACK cuando proceda.
JSONB y seguridad
-> devuelve un valor JSON; ->> devuelve texto. @> comprueba la contención. Una clave ausente y un null de JSON son casos distintos: elige el operador antes de decidir un valor predeterminado. Practícalo con los operadores de JSONB.
Los permisos y las políticas de fila son comprobaciones distintas. El rol necesita permisos sobre la tabla; la política limita las filas disponibles. El propietario de la tabla normalmente elude estas políticas, igual que los superusuarios y los roles con BYPASSRLS. Comprueba el resultado con el rol de la aplicación en seguridad a nivel de fila.
Los ejercicios de PostgreSQL incluyen una tarea ejecutable con resultados esperados. El curso añade corrección automática; su espacio de trabajo no es un terminal con todos los comandos de psql.