Funções GREATEST() e LEAST() e NULL

Hoje a gente vai mergulhar num assunto mais específico, mas super importante: as funções GREATEST() e LEAST(). Você vai aprender como achar os maiores e menores valores entre várias colunas e, o mais importante, como o NULL afeta o resultado delas.

Se você já procurou o mais importante na sua vida (amor, o emprego dos sonhos ou a melhor receita de pizza), vai sacar rapidinho pra que servem as funções GREATEST() e LEAST(). Essas funções ajudam a encontrar o maior ou menor valor numa lista de coisas. Só que, em vez de pizza, você vai trabalhar com números, datas, strings e outros dados no PostgreSQL.

GREATEST()

GREATEST() retorna o maior valor do conjunto que você passar.

Sintaxe:

GREATEST(value1, value2, ..., valueN)

LEAST()

LEAST() faz o contrário: ela procura o menor valor.

Sintaxe:

LEAST(value1, value2, ..., valueN)

Exemplo:

Imagina que a gente tem uma tabela students_scores, onde ficam as notas dos alunos em três provas:

Usando GREATEST() e LEAST():

SELECT
    student_id,
    GREATEST(exam_1, exam_2, exam_3) AS highest_score,
    LEAST(exam_1, exam_2, exam_3) AS lowest_score
FROM students_scores;

Resultado:

Como o NULL afeta o GREATEST() e LEAST()

Agora vem a parte mais interessante. Junto com os valores na tabela, pode aparecer NULL. E como a gente já sabe, NULL é aquele bicho estranho que significa dado ausente ou valor desconhecido. Bora ver o que rola se NULL cair dentro das funções GREATEST() e LEAST() no PostgreSQL.

Comportamento do NULL:

No PostgreSQL, as funções GREATEST() e LEAST() têm um comportamento especial: elas ignoram os valores NULL quando estão procurando o maior ou menor valor entre os argumentos. Importante: O único caso em que essas funções vão retornar NULL é se todos os argumentos forem NULL.

Exemplo:

SELECT
    GREATEST(10, 20, NULL, 5) AS greatest_value,
    LEAST(10, 20, NULL, 5) AS least_value;

Resultado:

Como você vê, o NULL foi ignorado e as funções devolveram o maior e o menor valor dos que estavam lá (10, 20, 5).

Agora um exemplo onde todos os argumentos são NULL:

Exemplo:

SELECT
    GREATEST(NULL, NULL) AS greatest_nulls,
    LEAST(NULL, NULL) AS least_nulls;

Resultado:

Como evitar problemas com NULL?

Mesmo que o PostgreSQL ignore o NULL por padrão, às vezes você pode querer um comportamento diferente. Por exemplo, se você quiser que o NULL seja tratado como um valor específico (tipo 0 ou outro valor padrão) na hora de achar o maior/menor. Nesses casos, dá pra usar a função COALESCE().

A função COALESCE(arg1, arg2, ...) retorna o primeiro argumento que não for NULL da lista. Isso permite trocar o NULL por um valor que faça sentido antes de passar pra GREATEST() ou LEAST().

Exemplo 1: Trocando NULL por 0

Vamos supor que a gente quer considerar que a ausência de nota (NULL) é igual a 0. Dá pra usar COALESCE() pra colocar o valor padrão.

Aqui está nossa tabela original:

Query:

SELECT
    student_id,
    GREATEST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS highest_score,
    LEAST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS lowest_score
FROM students_scores;

Resultado:

Exemplo 2: Trocando NULL pelo valor de outra coluna

Às vezes, em vez de um valor fixo (tipo 0), você quer colocar o valor de outra coluna. Por exemplo, se exam_3 estiver faltando, a gente usa o valor de exam_1.

SELECT
    student_id,
    GREATEST(
        exam_1, 
        exam_2, 
        COALESCE(exam_3, exam_1)
    ) AS highest_score
FROM students_scores;

Imagina que a tabela é assim:

Resultado da query:

Casos práticos

Caso 1: Procurando o maior desconto

Você está trabalhando com a tabela orders, onde cada pedido pode ter três tipos diferentes de desconto. Você precisa achar o maior desconto de cada pedido.

SELECT
    order_id,
    GREATEST(discount_1, discount_2, discount_3) AS max_discount
FROM orders;

Resultado:

Caso 2: Procurando o menor preço do produto

Na tabela products ficam os preços dos produtos em três moedas (USD, EUR, GBP). Sua missão é achar o menor preço de cada produto.

SELECT
    product_id,
    LEAST(price_usd, price_eur, price_gbp) AS lowest_price
FROM products;

Se todos os preços forem NULL, o resultado também é NULL

Erros comuns usando GREATEST() e LEAST()

Erro 1: Resultado inesperado por causa do NULL.

Lá no começo da aula a gente já explicou como o NULL afeta o GREATEST() e o LEAST() no PostgreSQL. O erro mais comum é que quem está acostumado com o comportamento do NULL em outros bancos (onde um NULL "contamina" o resultado todo) espera que o PostgreSQL faça igual.

Como esse erro aparece: Você pode achar que se tiver NULL na lista de argumentos, a função sempre vai devolver NULL. Daí, você pode acabar usando COALESCE() em tudo sem precisar, o que deixa a query mais complicada e pode até deixar ela mais lenta, se no seu caso o NULL deveria só ser ignorado.

Erro 2: Usar GREATEST() e LEAST() com tipos incompatíveis.

As funções GREATEST() e LEAST() servem pra comparar valores do mesmo tipo de dado ou tipos que podem ser convertidos automaticamente entre si. Se você tentar comparar valores de tipos totalmente diferentes, vai dar erro.

Como esse erro aparece: Você vai receber uma mensagem de erro dizendo que os tipos de dados são incompatíveis.