PostgreSQL COALESCE : NULL et valeurs de remplacement

Dans PostgreSQL, COALESCE renvoie la première valeur non NULL de sa liste d’arguments. Si tous les arguments sont NULL, le résultat est NULL. Cette expression permet de choisir un libellé, de remplacer un total absent ou de combiner des champs facultatifs, à condition que les arguments puissent être convertis vers un type commun.

L’emplacement de la valeur de remplacement compte. Remplacer un montant absent par zéro avant de calculer une moyenne change le sens du résultat. Fournir un libellé dans un SELECT ne modifie pas la ligne enregistrée. Les exemples suivants rendent ces différences visibles.

Nous avons exécuté 32 vérifications sur une instance isolée de PostgreSQL 18.6. Les sorties proviennent de psql en mode non aligné, sans en-têtes : les colonnes sont séparées par |, les booléens apparaissent sous la forme t ou f, et SQL NULL est affiché comme <NULL>.

Choisir la première valeur non NULL

SELECT COALESCE(NULL::text, 'fallback', 'later') AS chosen,
       COALESCE(0, 99) AS zero_value,
       COALESCE(false, true) AS boolean_value,
       COALESCE(NULL::text, NULL::text) IS NULL AS all_null;

fallback|0|f|t

La première expression ignore NULL et renvoie fallback. Zéro et false sont conservés, car ce sont des valeurs à part entière. La dernière expression confirme que deux arguments NULL typés donnent toujours NULL.

Utilisez COALESCE pour choisir simplement la première valeur disponible. Préférez CASE si le choix dépend d’une autre condition, comme un statut ou un seuil numérique. La documentation des expressions conditionnelles de PostgreSQL décrit les deux formes. COALESCE ressemble à un appel de fonction, mais PostgreSQL le traite comme une expression conditionnelle.

Distinguer NULL, les chaînes vides et les espaces

Créez ces objets de test dans une base jetable, avec un nom de schéma encore inutilisé :

CREATE SCHEMA coalesce_lab;
CREATE TABLE coalesce_lab.contacts (
    id integer PRIMARY KEY,
    display_name text,
    legal_name text,
    amount numeric
);
INSERT INTO coalesce_lab.contacts VALUES
    (1, NULL, 'Ada', 10),
    (2, '', 'Bea', NULL),
    (3, NULL, NULL, 20);

La deuxième ligne contient un nom d’affichage vide. Une chaîne vide n’est pas NULL : un simple COALESCE ne passe donc pas au nom légal. Comparons ce résultat à une règle explicite de normalisation :

SELECT id,
       quote_nullable(COALESCE(display_name, legal_name, '[unnamed]')) AS raw_label,
       quote_nullable(COALESCE(NULLIF(btrim(display_name), ''),
                               legal_name, '[unnamed]')) AS normalized_label
FROM coalesce_lab.contacts
ORDER BY id;

1|'Ada'|'Ada'
2|''|'Bea'
3|'[unnamed]'|'[unnamed]'

quote_nullable rend les valeurs textuelles visibles : '' représente une chaîne vide, tandis que les apostrophes autour d’Ada servent à l’affichage. L’expression normalisée retire les espaces ordinaires en début et en fin de chaîne, transforme un résultat vide en NULL avec NULLIF, puis examine le nom légal.

Cette règle est un choix propre au champ de nom d’affichage. Elle transforme aussi ' Ada ' en 'Ada', ce que nos vérifications ont confirmé. Ne l’appliquez pas sans réflexion à des identifiants ou à des champs où les espaces ont un sens. Un test distinct composé uniquement de trois espaces a renvoyé la valeur de remplacement après normalisation ; COALESCE seul a conservé les trois espaces. Il ne s’agit pas d’un nettoyage général de tous les caractères d’espacement Unicode.

La troisième ligne ne contient aucun des deux noms : les deux expressions renvoient donc le libellé prévu. Aucun SELECT ne modifie les données enregistrées. Les deux noms d’affichage initialement NULL le restent après les requêtes.

Choisir un type de résultat compatible

SELECT COALESCE(NULL::integer, 2.5::numeric) AS chosen,
       pg_typeof(COALESCE(NULL::integer, 2.5::numeric)) AS result_type;
SELECT COALESCE(NULL::integer::text, 'unknown'::text) AS display_label;

2.5|numeric
unknown

La première requête combine un argument integer et un argument numeric ; le résultat est numeric. La seconde convertit explicitement l’entier nullable en text avant de proposer un libellé textuel. Elle sépare ainsi la présentation des calculs numériques.

Le test a aussi vérifié deux erreurs utiles. COALESCE(1, 'unknown') a échoué avec le SQLSTATE 22P02, car le littéral ne pouvait pas être converti en entier. Avec un second argument explicitement text, comme dans COALESCE(1, 'unknown'::text), l’erreur était 42804 : les types ne pouvaient pas être harmonisés. Le premier argument non NULL n’a pas permis de contourner la vérification des types.

Un littéral compatible peut fonctionner : COALESCE(NULL::integer, '42') a renvoyé 42. Des conversions explicites rendent toutefois l’intention plus facile à relire. Les règles de résolution des types expliquent comment les expressions apparentées choisissent un type commun. Gardez des valeurs de remplacement numériques pour les calculs et textuelles pour les libellés.

Placer le remplacement au bon niveau d’agrégation

SELECT count(*) AS rows_seen,
       count(amount) AS known_amounts,
       sum(amount) AS total,
       round(avg(amount), 2) AS average_known,
       round(avg(COALESCE(amount, 0)), 2) AS average_with_zero
FROM coalesce_lab.contacts;
SELECT COALESCE(sum(amount), 0) AS default_after_sum,
       sum(COALESCE(amount, 0)) AS default_before_sum
FROM coalesce_lab.contacts
WHERE id < 0;

3|2|30|15.00|10.00
0|<NULL>

Les trois lignes contiennent deux montants connus, 10 et 20. Leur somme vaut 30 et leur moyenne 15. Remplacer le montant absent par zéro avant AVG fournit trois valeurs numériques : la moyenne devient 10. Ce résultat n’est adapté que si le montant absent doit effectivement être interprété comme zéro.

La seconde requête ne reçoit aucune ligne. Remplacer le résultat de SUM donne zéro, tandis que placer COALESCE dans SUM donne encore NULL : aucune ligne ne permet d’évaluer l’expression interne. La documentation des fonctions d’agrégation précise le comportement sur une entrée vide.

Nos vérifications supplémentaires distinguent trois situations. Pour l’unique ligne dont le montant est NULL, les deux expressions de somme donnent zéro. Sans aucune ligne, COUNT(*) et COUNT(amount) donnent tous deux zéro. Avec GROUP BY sur une entrée vide, aucun groupe n’est créé : COALESCE ne peut pas inventer une catégorie absente. Un rapport qui doit afficher toutes les catégories ou toutes les dates doit fournir ces lignes séparément.

Dans ce jeu de données, COALESCE(AVG(amount), 0) conserve une moyenne de 15, car le remplacement intervient après l’agrégation. Déterminez si NULL signifie inconnu, sans objet ou réellement zéro avant de changer le dénominateur.

Séparer les libellés de remplacement des valeurs par défaut

CREATE TABLE coalesce_lab.preferences (
    id integer PRIMARY KEY,
    label text DEFAULT 'standard'
);
INSERT INTO coalesce_lab.preferences (id) VALUES (1);
INSERT INTO coalesce_lab.preferences (id, label) VALUES (2, NULL);
SELECT id, quote_nullable(label) AS stored,
       COALESCE(label, 'display fallback') AS displayed
FROM coalesce_lab.preferences
ORDER BY id;

1|'standard'|standard
2|NULL|display fallback

Omettre le libellé dans le premier INSERT utilise la valeur par défaut de la colonne. Insérer explicitement NULL dans le second INSERT enregistre NULL. COALESCE fournit un libellé lors de la lecture de cette seconde ligne, mais la valeur enregistrée reste NULL.

Un remplacement à l’affichage n’impose donc aucune règle aux données. Si une colonne doit toujours contenir une valeur, définissez le comportement d’écriture et les contraintes appropriés. Si des lignes existantes doivent être corrigées, prévoyez une modification distincte et vérifiée : un SELECT ne les a pas nettoyées.

Repérer les filtres qui incluent les valeurs absentes

WITH numbers(id, value) AS (
    VALUES (1, NULL::integer), (2, 0), (3, 5)
)
SELECT id, (value = 0) AS equals_zero,
       (COALESCE(value, 0) = 0) AS null_as_zero,
       (value IS NULL) AS missing
FROM numbers
ORDER BY id;

1|<NULL>|t|t
2|t|t|f
3|f|f|f

Pour la valeur absente, value = 0 produit SQL NULL, affiché comme <NULL>. Une clause WHERE conserve les lignes dont la condition est vraie. Dans nos vérifications, l’égalité simple ne sélectionne que le zéro connu ; COALESCE(value, 0) = 0 sélectionne aussi la valeur absente. IS NULL ne sélectionne que cette dernière.

Utilisez les prédicats de comparaison avec NULL lorsque l’absence elle-même constitue la condition. Une valeur de remplacement dans un filtre définit les lignes à inclure ; elle ne doit pas être ajoutée uniquement pour faire disparaître NULL de l’affichage.

Nous n’avons mesuré ni les performances de ces petits exemples ni l’intérêt d’un index. Si la modification d’un filtre affecte une requête de production, examinez son plan avec le guide PostgreSQL EXPLAIN ANALYZE, en anglais. EXPLAIN ANALYZE exécute la requête : tenez-en compte pour les instructions qui modifient des données.

Observer l’évaluation du remplacement

Une séquence de test permet de voir si l’argument de remplacement est évalué. Elle appartient uniquement à cet exercice jetable :

CREATE SEQUENCE coalesce_lab.probe START 1;
SELECT COALESCE(7::bigint, nextval('coalesce_lab.probe')) AS chosen;
SELECT is_called FROM coalesce_lab.probe;
SELECT COALESCE(NULL::bigint, nextval('coalesce_lab.probe')) AS fallback;
SELECT is_called FROM coalesce_lab.probe;

7
f
1
t

Le premier appel renvoie 7 ; la séquence indique encore qu’elle n’a pas été appelée. Avec un premier argument NULL, l’appel suivant atteint nextval, renvoie 1 et fait passer is_called à true. Un test ultérieur avec zéro en premier argument laisse également la valeur de la séquence inchangée.

Ces expressions valides montrent que l’argument de remplacement inutilisé n’est pas évalué. Cela ne rend pas les types incompatibles acceptables, comme les erreurs précédentes l’ont montré. Évitez les effets de bord dans les expressions d’affichage ordinaires : cette séquence sert de témoin de test, pas de modèle pour une application.

Nettoyer les objets et choisir la règle voulue

DROP SEQUENCE coalesce_lab.probe;
DROP TABLE coalesce_lab.preferences;
DROP TABLE coalesce_lab.contacts;
DROP SCHEMA coalesce_lab;

Nos vérifications ont confirmé que les lignes d’exemple étaient intactes avant le nettoyage, supprimé les objets de l’exercice et arrêté le serveur isolé. Pour votre requête, contrôlez le type du résultat, la différence entre valeur absente et chaîne vide, et le niveau auquel intervient le remplacement. Testez à la fois des valeurs toutes NULL et une absence totale de lignes : ce sont deux situations différentes.