Menu

COALESCE, NULLIF e CASE WHEN no PostgreSQL

COALESCE(a, b, ...) retorna o primeiro argumento que não é NULL, NULLIF(a, b) transforma um valor em NULL e CASE WHEN escolhe um valor por condição.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

COALESCE(a, b, c) retorna o primeiro dos argumentos que não é NULL, então COALESCE(nickname, name) mostra o apelido quando existe e o nome nos outros casos. NULLIF(a, b) faz o contrário: retorna NULL quando a é igual a b. CASE WHEN condition THEN value ... ELSE value END escolhe um valor por condição.

Ben e Chen não têm apelido, então aparecem os nomes completos. Ben também recebe no phone.

Substituir NULL por um valor padrão

COALESCE é o IFNULL, ISNULL e NVL do PostgreSQL. Use para transformar NULL em 0 antes de fazer contas, já que qualquer conta com NULL dá NULL, e para dar um valor a uma agregação sobre nenhuma linha:

O pedido 2 mostra NULL sem COALESCE e 80 com ele, e a soma sobre nenhuma linha vira 0 em vez de NULL.

Todos os argumentos precisam resultar num mesmo tipo. COALESCE(quantity, 'none') numa coluna inteira falha com "invalid input syntax for type integer", porque o PostgreSQL tenta ler 'none' como inteiro. Converta a coluna antes: COALESCE(quantity::text, 'none'). A página de CAST explica as conversões.

Transformar um valor em NULL com NULLIF

NULLIF(a, b) retorna NULL se a = b, e a nos outros casos. Ele evita divisão por zero e transforma strings vazias em NULL para que o COALESCE possa substituí-las:

spring fica com taxa de cliques 5. summer fica com NULL em vez de um erro "division by zero", e o responsável vazio vira unassigned.

Escolher um valor com CASE WHEN

Essa forma testa as condições em ordem e retorna a primeira que bate. As linhas que não batem com nenhuma recebem o valor do ELSE, ou NULL quando não há ELSE:

O CASE no ORDER BY empurra os produtos esgotados para o fim, então mug fica por último. Uma forma mais curta compara uma expressão com valores: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. Ela não consegue testar NULL, já que NULL = NULL não é verdadeiro. Para isso, use WHEN x IS NULL na forma completa.

Contar por condição

Um CASE dentro de uma agregação conta ou soma por categoria numa única passada. O PostgreSQL também tem a cláusula FILTER, que diz a mesma coisa de forma mais direta:

SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
       count(*) FILTER (WHERE status = 'paid')      AS paid_too
FROM orders;

As duas contam só os pedidos pagos: count() ignora o NULL que o CASE retorna para as outras linhas.

GREATEST e LEAST ignoram NULL

GREATEST(a, b, ...) e LEAST(a, b, ...) retornam o maior e o menor argumento. No PostgreSQL eles ignoram argumentos NULL e só retornam NULL quando todos são NULL, então GREATEST(1, NULL, 3) é 3. O MySQL retorna NULL quando qualquer argumento é NULL, o que importa quando você migra consultas. Veja a documentação do PostgreSQL sobre expressões condicionais para todas as regras.

Perguntas frequentes

Qual é o equivalente de IFNULL, ISNULL ou NVL no PostgreSQL?

COALESCE(value, fallback). O PostgreSQL não tem a função IFNULL (MySQL), ISNULL (SQL Server) nem NVL (Oracle); COALESCE é SQL padrão e ainda aceita mais de dois argumentos.

Como substituir NULL por 0 no PostgreSQL?

Envolva a coluna: COALESCE(discount, 0). Para uma soma sobre nenhuma linha, que retorna NULL, escreva COALESCE(SUM(amount), 0).

O COALESCE avalia todos os argumentos?

Não. Ele avalia os argumentos em ordem e para no primeiro que não é NULL, então os argumentos seguintes não são calculados para aquela linha.

Posso usar CASE numa cláusula WHERE?

Pode. Uma expressão CASE pode aparecer em qualquer lugar onde cabe um valor, inclusive WHERE, ORDER BY e dentro de agregações. No WHERE, uma condição simples com AND/OR costuma ser mais fácil de ler.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar