Sql-Da-Semana

Sistemas legados e integrações nem sempre entregam datas em uma coluna do tipo date. É comum encontrar um campo de texto com oito caracteres no formato AAAAMMDD, além de valores vazios e conteúdos que parecem datas, mas não existem no calendário. Converter tudo diretamente com ::date torna a consulta frágil: basta uma linha inválida para interromper o relatório inteiro. Ignorar o problema também não resolve. Se os valores ruins desaparecerem silenciosamente, o total exibido pode parecer correto sem representar toda a carga recebida. ...

Uma integração recebe documentos XML e precisa distribuí-los entre vários workers. Cada documento deve ser processado por apenas um consumidor, mas todos consultam a mesma tabela em busca do próximo item pendente. Se as sessões tentarem bloquear a primeira linha da fila com um FOR UPDATE comum, elas podem acabar aguardando o mesmo item enquanto existem outros documentos disponíveis. O PostgreSQL oferece SKIP LOCKED para outro tipo de coordenação: em vez de esperar por uma linha já bloqueada, a consulta a ignora e procura a próxima candidata. ...

Duas instâncias de uma integração recebem solicitações quase ao mesmo tempo. Ambas consultam o mesmo saldo disponível, verificam que existe limite suficiente e aprovam uma reserva. Cada consulta, isoladamente, está correta. O problema aparece porque as duas decisões foram tomadas sobre o mesmo estado inicial. Quando uma leitura serve de base para uma alteração posterior, um SELECT comum pode não ser suficiente. No PostgreSQL, SELECT ... FOR UPDATE permite bloquear as linhas selecionadas até o fim da transação e serializar decisões concorrentes sobre elas. ...

Um painel consulta pedidos várias vezes ao dia para mostrar quantidade e valor por data e situação. Quando a consulta precisa percorrer e agrupar muitas linhas a cada acesso, recalcular sempre o mesmo resultado pode deixar de ser uma boa troca. Uma materialized view permite armazenar o resultado dessa consulta no PostgreSQL. A leitura passa a usar dados já consolidados, mas surge uma decisão nova: quando atualizar essa cópia? Neste episódio, vamos criar indicadores de pedidos, alterar os dados de origem e observar que a materialized view permanece desatualizada até receber um REFRESH. ...

Uma integração que recebe documentos fiscais costuma registrar mais do que o documento: origem da importação, tentativas de processamento e eventos recebidos ao longo do caminho. Parte desses dados tem estrutura estável. Outra parte varia conforme a origem ou a etapa do processamento. Neste episódio, vamos consultar essa segunda parte com jsonb, mantendo a identificação do documento em colunas relacionais. O laboratório do episódio 09 já está disponível no GitHub, com os cinco documentos e as consultas deste artigo. Execute os scripts para observar as diferenças entre chave ausente, JSON null e array vazio. ...

Para testar uma consulta, muitas vezes precisamos de registros que ainda não existem. Para montar um relatório diário, precisamos mostrar inclusive as datas em que nenhum registro foi criado. O PostgreSQL oferece uma função útil nos dois casos: generate_series(). Ela produz um conjunto de linhas a partir de limites e de um passo. Esse conjunto pode alimentar um INSERT, compor um calendário ou participar de uma junção. No Lab 02 do PostgreSQL Reliability Lab , usei essa função na carga inicial de categorias, clientes e produtos. Neste episódio, vamos partir desse uso e construir um relatório que também mostra dias sem pedidos. ...

Subconsultas colocadas no FROM normalmente são independentes das tabelas que aparecem antes delas. Mas alguns problemas exigem que a subconsulta seja avaliada para cada linha externa. Um exemplo comum é buscar os três produtos mais vendidos de cada categoria. No PostgreSQL, LATERAL permite expressar essa dependência diretamente. O problema Considere as tabelas: CREATE TABLE categories ( category_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE ); CREATE TABLE products ( product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, category_id bigint NOT NULL REFERENCES categories, name text NOT NULL, sales_count integer NOT NULL DEFAULT 0 ); Queremos listar cada categoria acompanhada dos três produtos com maior sales_count. ...

Agregações tradicionais reduzem várias linhas a um resultado por grupo. Isso é exatamente o que queremos ao calcular o total de vendas por vendedor. Mas e se precisarmos manter cada venda e, ao mesmo tempo, mostrar sua posição no ranking e o valor acumulado? Para esse tipo de problema, o SQL oferece funções de janela. O problema Considere uma tabela de vendas: CREATE TABLE vendas ( venda_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, vendedor_id bigint NOT NULL, realizada_em timestamptz NOT NULL, valor numeric(12, 2) NOT NULL CHECK (valor > 0) ); Queremos exibir cada venda com: ...

Consultas SQL costumam crescer de maneira incremental. Primeiro precisamos filtrar os dados. Depois agregar por cliente, calcular uma média, comparar cada resultado com essa média e ordenar somente os casos mais relevantes. Quando todas essas etapas ficam aninhadas em uma única expressão, a consulta pode continuar correta, mas se torna difícil de ler e modificar. Uma Common Table Expression, ou CTE, permite dividir esse raciocínio em resultados nomeados. O problema Considere uma tabela com execuções de pipelines: ...

Integrações, cargas de dados e consumidores de eventos frequentemente precisam executar uma operação simples de descrever: Se o registro ainda não existe, insira. Se já existe, atualize. Esse comportamento é conhecido como upsert, combinação de update e insert. No PostgreSQL, ele pode ser implementado com INSERT ... ON CONFLICT. O problema Considere uma tabela que registra o estado mais recente de cada pipeline em uma data de referência: CREATE TABLE pipeline_status ( pipeline text NOT NULL, reference_date date NOT NULL, status text NOT NULL, attempts integer NOT NULL DEFAULT 1, updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT pipeline_status_uk UNIQUE (pipeline, reference_date) ); Uma mesma execução pode ser informada novamente por retry, reprocessamento ou entrega duplicada de mensagem. ...