Menu

PostgreSQL COALESCE, NULLIF et CASE WHEN

COALESCE(a, b, ...) renvoie le premier argument qui n'est pas NULL, NULLIF(a, b) transforme une valeur en NULL, et CASE WHEN choisit une valeur selon une condition.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

COALESCE(a, b, c) renvoie le premier de ses arguments qui n'est pas NULL, donc COALESCE(nickname, name) affiche le pseudo quand il existe et le nom sinon. NULLIF(a, b) fait l'inverse : il renvoie NULL quand a est égal à b. CASE WHEN condition THEN value ... ELSE value END choisit une valeur selon une condition.

Ben et Chen n'ont pas de pseudo, donc leur nom complet s'affiche. Ben obtient aussi no phone.

Remplacer NULL par une valeur par défaut

COALESCE est le IFNULL, le ISNULL et le NVL de PostgreSQL. Utilisez-le pour transformer NULL en 0 avant un calcul, puisque tout calcul avec NULL donne NULL, et pour donner une valeur à un agrégat sur zéro ligne :

La commande 2 affiche NULL sans COALESCE et 80 avec, et la somme sur zéro ligne devient 0 au lieu de NULL.

Tous les arguments doivent se ramener à un seul type. COALESCE(quantity, 'none') sur une colonne entière échoue avec « invalid input syntax for type integer », car PostgreSQL essaie de lire 'none' comme un entier. Convertissez d'abord la colonne : COALESCE(quantity::text, 'none'). La page sur CAST couvre les conversions.

Transformer une valeur en NULL avec NULLIF

NULLIF(a, b) renvoie NULL si a = b, et a sinon. Il évite la division par zéro, et il transforme les chaînes vides en NULL pour que COALESCE puisse les remplacer :

spring obtient un taux de clic de 5. summer obtient NULL au lieu d'une erreur « division by zero », et son propriétaire vide devient unassigned.

Choisir une valeur avec CASE WHEN

Cette forme teste les conditions dans l'ordre et renvoie la première qui correspond. Les lignes qui ne correspondent à rien reçoivent la valeur du ELSE, ou NULL quand il n'y a pas de ELSE :

Le CASE dans ORDER BY repousse les produits épuisés à la fin, donc mug arrive en dernier. Une forme plus courte compare une expression à des valeurs : CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. Elle ne peut pas tester NULL, puisque NULL = NULL n'est pas vrai. Pour cela, utilisez WHEN x IS NULL dans la forme complète.

Compter selon une condition

Un CASE à l'intérieur d'un agrégat compte ou additionne par catégorie en une seule passe. PostgreSQL a aussi la clause FILTER, qui dit la même chose plus directement :

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

Les deux ne comptent que les commandes payées : count() ignore le NULL que CASE renvoie pour les autres lignes.

GREATEST et LEAST ignorent NULL

GREATEST(a, b, ...) et LEAST(a, b, ...) renvoient le plus grand et le plus petit argument. Dans PostgreSQL, ils ignorent les arguments NULL et ne renvoient NULL que si tous les arguments sont NULL, donc GREATEST(1, NULL, 3) vaut 3. MySQL renvoie NULL dès qu'un argument est NULL, ce qui compte quand vous portez des requêtes. Consultez la documentation PostgreSQL sur les expressions conditionnelles pour toutes les règles.

Questions fréquentes

Quel est l'équivalent PostgreSQL de IFNULL, ISNULL ou NVL ?

COALESCE(value, fallback). PostgreSQL n'a pas de fonction IFNULL (MySQL), ISNULL (SQL Server) ni NVL (Oracle) ; COALESCE est du SQL standard et accepte aussi plus de deux arguments.

Comment remplacer NULL par 0 dans PostgreSQL ?

Entourez la colonne : COALESCE(discount, 0). Pour une somme sur zéro ligne, qui renvoie NULL, écrivez COALESCE(SUM(amount), 0).

COALESCE évalue-t-il tous ses arguments ?

Non. Il évalue les arguments dans l'ordre et s'arrête au premier qui n'est pas NULL, donc les arguments suivants ne sont pas calculés pour cette ligne.

Peut-on utiliser CASE dans une clause WHERE ?

Oui, une expression CASE peut apparaître partout où une valeur est possible, y compris dans WHERE, ORDER BY et à l'intérieur des agrégats. Dans WHERE, une simple condition AND/OR est en général plus lisible.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer