Fonctions GREATEST() et LEAST() et NULL

Aujourd'hui, on plonge dans un sujet encore plus spécifique mais super important : les fonctions GREATEST() et LEAST(). Tu vas voir comment trouver les valeurs max et min parmi plusieurs colonnes, et surtout, comment NULL influence leur comportement.

Si t'as déjà cherché la chose la plus importante dans ta vie (l'amour, le job de rêve ou la meilleure recette de pizza), tu piges direct à quoi servent GREATEST() et LEAST(). Ces fonctions t'aident à trouver la plus grande ou la plus petite valeur dans une liste de trucs. Sauf qu'au lieu de la pizza, tu bosses avec des nombres, des dates, des chaînes de caractères et d'autres données dans PostgreSQL.

GREATEST()

GREATEST() te renvoie la plus grande valeur parmi celles que tu lui donnes.

Syntaxe :

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

LEAST()

LEAST() fait l'inverse : elle cherche la plus petite valeur.

Syntaxe :

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

Exemple :

Imaginons qu'on a une table students_scores où sont stockées les notes des étudiants pour trois exams :

Utilisation de GREATEST() et 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;

Résultat :

Comment NULL influence GREATEST() et LEAST()

Maintenant, on arrive à la partie la plus fun. Dans la table, tu peux avoir des valeurs NULL. Et comme tu le sais déjà, NULL c'est cette entité mystérieuse qui veut dire "pas de donnée" ou "valeur inconnue". Voyons ce qui se passe si NULL se retrouve dans GREATEST() ou LEAST() dans PostgreSQL.

Comportement de NULL :

Dans PostgreSQL, les fonctions GREATEST() et LEAST() ont un comportement spécial : elles ignorent les valeurs NULL quand elles cherchent la valeur max ou min parmi leurs arguments. Important : Le seul cas où ces fonctions renvoient NULL, c'est si tous leurs arguments sont NULL.

Exemple :

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

Résultat :

Comme tu vois, NULL a été ignoré, et les fonctions ont renvoyé les valeurs max et min parmi celles présentes (10, 20, 5).

Et voilà un exemple où tous les arguments sont NULL :

Exemple :

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

Résultat :

Comment éviter les soucis avec NULL ?

Même si PostgreSQL ignore NULL par défaut, parfois tu veux un autre comportement. Par exemple, tu veux que NULL soit considéré comme une valeur précise (genre 0 ou une autre valeur par défaut) quand tu cherches le max/min. Dans ce cas, tu peux utiliser la fonction COALESCE().

La fonction COALESCE(arg1, arg2, ...) renvoie le premier argument qui n'est pas NULL dans sa liste. Ça te permet de remplacer NULL par une valeur qui a du sens avant de passer à GREATEST() ou LEAST().

Exemple 1 : Remplacer NULL par 0

Imaginons qu'on veut considérer qu'une note absente (NULL) vaut 0. On peut utiliser COALESCE() pour mettre une valeur par défaut.

Voici notre table de départ :

Requête :

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;

Résultat :

Exemple 2 : Remplacer NULL par la valeur d'une autre colonne

Parfois, au lieu d'une valeur fixe (genre 0), tu veux mettre la valeur d'une autre colonne. Par exemple, si exam_3 manque, tu veux utiliser la valeur de exam_1.

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

Imaginons qu'on a cette table :

Résultat de la requête :

Cas pratiques

Cas 1 : Trouver la remise maximale

Tu bosses avec la table orders, où chaque commande peut avoir trois types de remise différents. Tu dois trouver la remise max pour chaque commande.

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

Résultat :

Cas 2 : Trouver le prix minimal d'un produit

Dans la table products, tu as les prix des produits dans trois devises (USD, EUR, GBP). Ton but : trouver le prix le plus bas pour chaque produit.

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

Si tous les prix sont NULL, le résultat sera aussi NULL

Erreurs classiques avec GREATEST() et LEAST()

Erreur 1 : Résultat inattendu à cause de NULL.

On a déjà vu plus haut comment NULL influence GREATEST() et LEAST() dans PostgreSQL. L'erreur principale, c'est que les gens qui ont l'habitude d'autres SGBD (où un seul NULL "contamine" tout le résultat) s'attendent à la même chose dans PostgreSQL.

Comment ça se manifeste : Tu peux croire à tort que si y'a un NULL dans la liste des arguments, la fonction va toujours renvoyer NULL. Du coup, tu risques d'utiliser COALESCE() partout pour rien, ce qui complique la requête et peut la ralentir, alors que dans ton cas NULL devrait juste être ignoré.

Erreur 2 : Utiliser GREATEST() et LEAST() avec des types incompatibles.

Les fonctions GREATEST() et LEAST() sont faites pour comparer des valeurs du même type de données ou des types qui peuvent être convertis implicitement entre eux. Si tu essaies de comparer des types totalement différents et incompatibles, tu vas avoir une erreur.

Comment ça se manifeste : Tu vas recevoir un message d'erreur qui te dit que les types de données sont incompatibles.