Tabelas, consultas e índices
Execute o exemplo em um banco de teste. A coluna de identidade gera o identificador da expedição. A restrição de status limita o texto permitido, enquanto NOT NULL rejeita a ausência de valor separadamente. timestamptz representa um instante; o horário exibido depende do fuso da sessão.
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';
A consulta agrupada filtra as linhas antes de agregá-las e aplica HAVING depois do agrupamento. O segundo critério de ordenação resolve os empates de forma estável. COUNT(*) conta linhas; COUNT(coluna) conta valores não nulos. Para identificar um valor ausente, use IS NULL; = NULL não retorna true.
A escolha do índice
O índice B-tree parcial contém as expedições abertas e atende ao filtro de filial correspondente. O índice de expressão usa o texto extraído da prioridade. O índice GIN permite operações de JSONB como o teste de contenção do exemplo. Criar os três demonstra a sintaxe; isso não significa que uma aplicação precise de todos.
Cada índice consome armazenamento e acrescenta trabalho às escritas.
A existência de um índice não garante seu uso. Examine o plano e a distribuição dos dados na lição de B-tree e na de índices parciais e de expressão. Para um índice de várias colunas, considere as classes de operadores, a ordem das colunas e os predicados da consulta.
Transações e planos
ROLLBACK TO SAVEPOINT desfaz a atualização e mantém a transação aberta. COMMIT encerra a transação. Se ela falhar, faça rollback antes de executar instruções sem relação com ela. Os contadores de sequência são uma exceção ao comportamento normalmente esperado de um rollback.
EXPLAIN estima um plano. Ao acrescentar ANALYZE, você executa a instrução, inclusive as alterações de dados que ela contiver. Em um banco de teste, envolva uma escrita exploratória em BEGIN e ROLLBACK quando isso for adequado.
JSONB e segurança
-> retorna um valor JSON; ->> retorna texto. @> testa contenção. Uma chave ausente e um null do JSON são casos diferentes: escolha o operador antes de definir um valor padrão. Pratique com os operadores JSONB.
Permissões e políticas de linha são verificações separadas. O papel precisa da permissão na tabela; a política restringe as linhas disponíveis. O proprietário da tabela normalmente ignora essas políticas, assim como superusuários e papéis com BYPASSRLS. Teste com o papel da aplicação em segurança em nível de linha.
Os exercícios de PostgreSQL trazem uma tarefa executável com resultados esperados. O curso acrescenta correção automática; seu ambiente não é um terminal com todos os comandos do psql.