El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Conversió de tipus i tractament dels NULL: CAST i COALESCE

Següent ►
►

Fa tres lliçons que escrius ::TEXT, ::NUMERIC i ::DATE sense que ningú t'hagi explicat què són. I fa dos mòduls que arrossegues una promesa: 04-03 va tancar el tema dels nuls dient que COALESCE i NULLIF —les eines que substitueixen un nul per alguna cosa presentable— s'explicarien aquí. Els dos temes van junts perquè són el mateix vist des de dos angles: què fer quan un valor no té la forma que necessites. De vegades té el tipus equivocat i cal convertir-lo; d'altres no hi ha valor en absolut i cal substituir-lo. Són el pegament de tot l'anterior: sense ells, les funcions de cadena es trenquen davant d'un nul, les numèriques fallen en dividir per zero i les de data es neguen a llegir un text.

Contingut

  1. Conversió implícita enfront d'explícita
  2. CAST i l'operador ::
  3. Les conversions habituals i les seves trampes
  4. to_number, to_char, to_date: conversió controlada
  5. COALESCE: el primer valor no nul
  6. NULLIF i els seus dos usos canònics
  7. COALESCE enfront de CASE
  8. COALESCE en agregacions: el deute de 04-04
  9. Taula comparativa per motor
  10. Errors habituals i consells
  11. Exercicis
  12. Conclusió

  1. Conversió implícita enfront d'explícita

Una conversió implícita la fa el motor pel seu compte, sense que la hi demanis. Una d'explícita l'escrius tu.

SELECT 1 + '2' AS suma_implicita, 10 > '9' AS numero_contra_literal,
       '10' > '9' AS literal_contra_literal;
suma_implicita numero_contra_literal literal_contra_literal
3 true false

Les tres columnes fan servir el mateix '9' o '2' i es comporten de tres maneres. 1 + '2' dona 3 perquè PostgreSQL veu un enter a l'esquerra i resol el literal com a enter; 10 > '9' dona true pel mateix mecanisme; i '10' > '9' dona false, perquè allà no hi ha cap tipus que doni la pista, els dos literals es resolen com a text i en ordre alfabètic '10' va abans que '9'.

Aquest últim és l'exemple de per què la conversió implícita és perillosa: no falla, retorna una altra cosa. Un WHERE codi > '9' sobre una columna de text que conté números filtra a l'inrevés del que et penses, i ningú no t'avisa.

Conversió implícita Conversió explícita
Qui decideix El motor, segons el context Tu
Visible en llegir el codi No Sí
Portable entre motors No: cadascun té les seves regles Sí

La regla: quan dos tipus es barregen, converteix-los tu. Que el motor sàpiga endevinar no vol dir que endevini el que vols, i encara menys que el motor següent endevini igual.

  1. CAST i l'operador ::

Hi ha dues sintaxis per al mateix:

SELECT CAST('123' AS INTEGER) AS forma_estandard, '123'::INTEGER     AS forma_postgresql,
       CAST(12.9 AS INTEGER)  AS numeric_a_enter, CAST(42 AS TEXT)   AS numero_a_text;
forma_estandard forma_postgresql numeric_a_enter numero_a_text
123 123 13 42

CAST(expr AS tipus) és SQL estàndard i funciona a tots els motors; expr::tipus és l'abreviatura de PostgreSQL, més curta i còmoda en encadenar però no portable. El consell pràctic: :: en consultes d'anàlisi, CAST en codi que pugui acabar en un altre motor. I compte amb la precedència: :: s'aplica abans que els operadors aritmètics, així que a + b::NUMERIC converteix només b; per convertir la suma, (a + b)::NUMERIC.

Fixa't ja en la tercera columna: CAST(12.9 AS INTEGER) retorna 13, no 12. És la primera trampa.

  1. Les conversions habituals i les seves trampes

Conversió Exemple Resultat Trampa
Text → enter '123'::INTEGER 123 Falla amb qualsevol caràcter no numèric
Text invàlid → enter 'abc'::INTEGER ERROR Vegeu més avall
Text amb coma → numèric '12,50'::NUMERIC ERROR El separador decimal ha de ser .
Numèric → enter 12.9::INTEGER / 12.4::INTEGER 13 / 12 Arrodoneix, no trunca
Número → text, text ISO → data 42::TEXT, '2025-03-04'::DATE '42', 2025-03-04 Sense problema
Text ambigu → data '03/04/2025'::DATE Depèn de DateStyle Vegeu més avall
Text → VARCHAR(n) 'Oli d''oliva'::VARCHAR(3) 'Oli' Trunca en silenci

La conversió que falla

SELECT 'abc'::INTEGER;
ERROR:  invalid input syntax for type integer: "abc"
LINE 1: SELECT 'abc'::INTEGER;
               ^

I això és una bona notícia: l'error atura la consulta i t'obliga a mirar la dada. Compara-ho amb SQLite, que per a la mateixa expressió retorna 0 sense dir res, de manera que un SUM sobre aquesta columna donaria un total plausible i equivocat. Que PostgreSQL sigui estricte és una característica, no una molèstia.

El cas real apareix en importar una columna de text amb '12,50' en format català. '12,50'::NUMERIC falla, i la solució és normalitzar abans amb les funcions de 06-01:

SELECT REPLACE('12,50', ',', '.')::NUMERIC                        AS import,
       REPLACE(REPLACE('1.234,56', '.', ''), ',', '.')::NUMERIC   AS import_amb_milers;
import import_amb_milers
12.50 1234.56

NUMERIC → INTEGER arrodoneix

12.4::INTEGER és 12, però 12.5::INTEGER i 12.9::INTEGER són tots dos 13. És coherent amb 06-02 —NUMERIC arrodoneix half-up— i contradiu la intuïció de qui ve d'un llenguatge de programació, on convertir a enter trunca. Si vols truncar, escriu TRUNC(12.9)::INTEGER, que sí que dona 12.

La data ambigua

'03/04/2025'::DATE no té una resposta única: depèn del paràmetre de sessió DateStyle, que per omissió val ISO, MDY (sortida ISO, entrada mes-dia-any) i es consulta amb SHOW DateStyle;. Amb aquest valor, '03/04/2025'::DATE és el 4 de març; amb SET DateStyle = 'ISO, DMY'; passa a ser el 3 d'abril. La mateixa cadena, dues dates, segons una variable de sessió que gairebé ningú no mira.

La regla, ja vista a 06-03: per a dates ambigües fes servir sempre TO_DATE(text, patro) amb el patró explícit, o exigeix el format ISO AAAA-MM-DD a l'origen. No deixis mai que la interpretació depengui de la configuració del servidor.

El truncament silenciós

SELECT 'Oli d''oliva verge extra'::VARCHAR(3); retorna Oli. Sense error i sense avís. I aquí hi ha la incoherència que cal conèixer: si intentes inserir aquesta mateixa cadena en una columna VARCHAR(3), PostgreSQL sí que falla amb value too long for type character varying(3). La conversió explícita trunca; la inserció, no. És l'única conversió de PostgreSQL que perd dades en silenci. Finalment, l'avís de sempre: un WHERE data_comanda::TEXT LIKE '2025%' embolcalla la columna en una conversió i no pot fer servir l'índex, igual que les funcions de 06-01 (08-03).

  1. to_number, to_char, to_date: conversió controlada

Quan el format no és l'estàndard, les funcions to_* reben un patró i fan la conversió segons les teves regles, no segons les del motor.

Funció Què fa Exemple Resultat
TO_CHAR(valor, patro) Número o data → text formatat TO_CHAR(1234.5, 'FM999G999D00') 1.234,50 (amb lc_numeric català)
TO_NUMBER(text, patro) Text → NUMERIC TO_NUMBER('12500', '99999') 12500
TO_DATE(text, patro) Text → DATE TO_DATE('04/03/2025', 'DD/MM/YYYY') 2025-03-04
TO_TIMESTAMP(text, patro) Text → TIMESTAMPTZ TO_TIMESTAMP('04/03/2025 18:30', 'DD/MM/YYYY HH24:MI') 2025-03-04 18:30:00+01

Els símbols G (milers) i D (decimal) dels patrons numèrics depenen de lc_numeric: en un servidor en català produeixen 1.234,50 i en un d'anglès, 1,234.50. Si necessites un resultat independent del servidor, fes servir els literals , i . al patró o normalitza amb REPLACE. TO_CHAR sobre números completa la parella amb el TO_CHAR sobre dates de 06-03: la mateixa funció, patrons diferents.

  1. COALESCE: el primer valor no nul

COALESCE(a, b, c, …) retorna el primer dels seus arguments que no sigui nul, i NULL només si tots ho són. Admet qualsevol nombre d'arguments.

SELECT COALESCE(NULL, NULL, 'tercer', 'quart') AS primer_no_nul,
       COALESCE(NULL, NULL, NULL) AS tots_nuls, COALESCE(1, 1/0) AS peresosa;
primer_no_nul tots_nuls peresosa
tercer (null) 1

La tercera columna mereix atenció: 1/0 provocaria un error de divisió per zero… si arribés a avaluar-se. COALESCE és peresosa: tan bon punt troba un argument no nul deixa de mirar els següents, cosa que permet posar càlculs cars o perillosos com a últim recurs. I un requisit que sorprèn: tots els arguments han de ser de tipus compatibles, així que COALESCE(empleat_id, 'Venda web') falla perquè empleat_id és un enter.

El patró de presentació

Això és el que 04-03 va deixar pendent: les deu comandes web amb empleat_id IS NULL.

SELECT co.id, co.data_comanda, co.estat,
       CONCAT_WS(' ', e.nom, e.cognoms)                        AS comercial_cru,
       COALESCE(CONCAT_WS(' ', e.nom, e.cognoms), 'Venda web') AS canal_malament,
       COALESCE(NULLIF(CONCAT_WS(' ', e.nom, e.cognoms), ''),
                'Venda web')                                   AS canal_be
FROM comandes       AS co
LEFT JOIN empleats  AS e ON e.id = co.empleat_id
WHERE co.id <= 4 ORDER BY co.id;
id data_comanda estat comercial_cru canal_malament canal_be
1 2025-03-04 lliurat Venda web
2 2025-03-12 lliurat Óscar Peris Blasco Óscar Peris Blasco Óscar Peris Blasco
3 2025-04-02 lliurat Venda web
4 2025-04-19 lliurat Laia Puig Sanchis Laia Puig Sanchis Laia Puig Sanchis

Aquí es tanca el cercle de 06-01. CONCAT_WS ignora els nuls, així que per a les comandes web retorna la cadena buida i no NULL; i COALESCE no la substitueix, perquè '' no és nul, de manera que canal_malament surt buida. La combinació COALESCE(NULLIF(x, ''), 'Venda web') és la forma correcta i la fórmula que cal memoritzar: NULLIF converteix la cadena buida en nul i llavors COALESCE pot fer la seva feina. Si concatenes amb || en lloc de CONCAT_WS el nul es propaga i COALESCE(e.nom || ' ' || e.cognoms, 'Venda web') funciona directament; les dues vies són vàlides, el que no val és barrejar-les sense adonar-se'n.

El mateix patró amb els set clients sense recomanador (04-03), aquesta vegada amb ||:

SELECT c.id, CONCAT_WS(' ', c.nom, c.cognoms) AS client,
       COALESCE(ref.nom || ' ' || ref.cognoms, 'Registre directe') AS origen
FROM clients AS c
LEFT JOIN clients AS ref ON ref.id = c.referit_per_id
WHERE c.id <= 5 ORDER BY c.id;
id client origen
1 Lucía Martínez Soler Registre directe
2 Carlos Ferrer Ibáñez Lucía Martínez Soler
3 Marta Sanchis Gil Lucía Martínez Soler
4 Javier Ortega Ruiz Registre directe
5 Ana Belmonte Roca Carlos Ferrer Ibáñez

Important: COALESCE és una eina de presentació, no d'anàlisi. Substituir un nul per un text està bé per a un informe que llegeix una persona; substituir-lo per un 0 dins d'un càlcul canvia el resultat, i això és la secció 8.

  1. NULLIF i els seus dos usos canònics

NULLIF(a, b) retorna NULL si a = b, i a en cas contrari. És exactament el contrari de COALESCE: en lloc de treure nuls, els fabrica.

SELECT NULLIF(5, 5) AS iguals, NULLIF(5, 3) AS diferents, NULLIF('', '') AS cadena_buida;
iguals diferents cadena_buida
(null) 5 (null)

Sembla inútil fins que veus per a què s'utilitza. Té dos casos canònics i pràcticament cap més.

Ús 1: evitar la divisió per zero

Recorda l'avís de 04-03: no et pots protegir amb AND stock <> 0, perquè el planificador reordena les condicions. NULLIF sí que protegeix, perquè actua dins de l'expressió:

SELECT p.id, p.nom, p.stock,
       SUM(lc.quantitat)                                       AS venudes,
       ROUND(SUM(lc.quantitat) * 100.0 / NULLIF(p.stock, 0), 2) AS rotacio_pct
FROM productes           AS p
LEFT JOIN linies_comanda AS lc ON lc.producte_id = p.id
WHERE p.id IN (2, 13, 15, 18)
GROUP BY p.id, p.nom, p.stock ORDER BY p.id;
id nom stock venudes rotacio_pct
2 Arròs integral ecològic 1 kg 200 14 7.00
13 Espelmes de cera de soja (pack 2) 0 (null) (null)
15 Te verd matcha cerimonial 30 g 40 4 10.00
18 Raspall de dents de bambú 240 9 3.75

El producte 13 té stock 0: sense NULLIF, aquella fila provocaria ERROR: division by zero i la consulta sencera fallaria. Amb NULLIF(p.stock, 0) el denominador es torna nul, la divisió retorna NULL i l'informe diu honestament "no es pot calcular", que és la veritat: la rotació d'un producte sense estoc no és zero, és indefinida.

Ús 2: tractar la cadena buida com a nul

És el problema del segon cognom que va quedar obert a 06-01:

SELECT id, cognoms,
       SPLIT_PART(cognoms, ' ', 2)                            AS segon_cru,
       NULLIF(SPLIT_PART(cognoms, ' ', 2), '')                AS segon_net,
       COALESCE(NULLIF(SPLIT_PART(cognoms, ' ', 2), ''), '—') AS segon_presentable
FROM clients WHERE id IN (1, 9, 10) ORDER BY id;
id cognoms segon_cru segon_net segon_presentable
1 Martínez Soler Soler Soler Soler
9 Dubois (null) —
10 Moreau (null) —

Molts sistemes heretats desen '' on haurien de desar NULL —formularis web amb camps buits, importacions de CSV—. NULLIF(columna, '') és la neteja estàndard; en un UPDATE de sanejament seria SET ciutat = NULLIF(TRIM(ciutat), '').

  1. COALESCE enfront de CASE

COALESCE és sucre sintàctic sobre un CASE. Aquestes dues expressions són exactament equivalents:

COALESCE(a, b)

CASE WHEN a IS NOT NULL THEN a ELSE b END
COALESCE CASE
Llegibilitat Molt alta per a "substituir el nul" Verbosa per a aquest cas
Condició Només "és nul?" Qualsevol condició
Quan fer-lo servir Substituir nuls Classificar, comparar rangs, decidir per valor

La regla és simple: si la pregunta és "és nul?", COALESCE; si és qualsevol altra, CASE — la lliçó següent. Escriure CASE WHEN x IS NULL THEN 0 ELSE x END no està malament, però és quatre vegades més llarg que COALESCE(x, 0) i amaga la intenció.

  1. COALESCE en agregacions: el deute de 04-04

Aquí COALESCE deixa de ser cosmètic i canvia el resultat. A 04-04 vas aprendre que les funcions d'agregació ignoren els nuls; vegem què passa si els hi dones convertits en zeros. La puntuació mitjana dels productes, unint les 20 files del catàleg amb les 12 ressenyes:

SELECT COUNT(*)                                AS files,
       COUNT(r.puntuacio)                      AS amb_ressenya,
       SUM(r.puntuacio)                        AS suma,
       ROUND(AVG(r.puntuacio), 4)              AS avg_ignorant_nuls,
       ROUND(AVG(COALESCE(r.puntuacio, 0)), 4) AS avg_comptant_zeros
FROM productes      AS p
LEFT JOIN ressenyes AS r ON r.producte_id = p.id;
files amb_ressenya suma avg_ignorant_nuls avg_comptant_zeros
23 12 49 4.0833 2.1304

4,08 enfront de 2,13. La mateixa dada, dues respostes que no s'assemblen gens. AVG(r.puntuacio) divideix 49 entre 12: la mitjana de les ressenyes que existeixen, la resposta a "què opinen els clients que han opinat?". AVG(COALESCE(r.puntuacio, 0)) divideix 49 entre 23, comptant els 11 productes sense ressenya com si els haguessin posat un zero.

La primera és gairebé sempre la correcta, perquè "ningú no ha opinat" no és "tothom ha opinat malament". La segona és exactament l'error que 04-03 descrivia en parlar de valors sentinella: inventar una dada per no haver de gestionar-ne l'absència.

El mateix efecte amb els salaris, unint cada comanda amb el seu comercial:

Expressió Divideix entre Resultat Què significa
AVG(e.salari) 10 (els no nuls) 27 420,00 € Salari mitjà de qui gestiona comandes amb comercial
AVG(COALESCE(e.salari, 0)) 20 (totes les files) 13 710,00 € Com si les comandes web les gestionés algú que cobra 0 €

La regla: fes servir COALESCE fora de l'agregat per presentar (COALESCE(SUM(x), 0)) i dins només quan el zero sigui un valor real, no un buit. La pregunta que cal fer-se és: "aquest buit significa zero, o significa que no hi ha dada?".

COALESCE fora de l'agregat: el conjunt buit

Just el cas contrari. SUM sobre zero files no retorna 0, retorna NULL:

SELECT cat.id, cat.nom,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio,
       COALESCE(ROUND(SUM(lc.quantitat * lc.preu_unitari
                          * (1 - lc.descompte)), 2), 0)                   AS facturacio_presentable
FROM categories          AS cat
LEFT JOIN productes      AS p  ON p.categoria_id = cat.id
LEFT JOIN linies_comanda AS lc ON lc.producte_id = p.id
GROUP BY cat.id, cat.nom ORDER BY cat.id;
id nom facturacio facturacio_presentable
1 Alimentació 256.27 256.27
2 Cosmètica natural 156.32 156.32
3 Llar sostenible 88.58 88.58
4 Begudes 195.28 195.28
5 Higiene personal 31.50 31.50
6 Complements (null) 0.00

Les cinc primeres sumen 727,95 €, la xifra canònica. La categoria Complements només conté el producte 20, descatalogat i mai venut: SUM sobre un conjunt buit dona NULL. I aquí COALESCE(…, 0) sí que és correcte, perquè "no s'ha venut res" és exactament zero euros. La diferència amb el cas de les ressenyes és de significat, no de sintaxi.

  1. Taula comparativa per motor

Tasca PostgreSQL 16 MySQL 8 SQLite SQL Server Oracle
Primer no nul (n arguments) COALESCE COALESCE COALESCE COALESCE COALESCE
Versió de dos arguments COALESCE(a,b) IFNULL(a,b) IFNULL(a,b) ISNULL(a,b) NVL(a,b)
"Si no és nul X, si ho és Y" CASE IF(a IS NULL, y, x) IIF(...) IIF(...) NVL2(a, x, y)
Convertir en nul si són iguals NULLIF NULLIF NULLIF NULLIF NULLIF
Conversió / la que falla CAST, :: → ERROR CAST, CONVERT → 0 + avís CAST → 0 en silenci CAST, CONVERT → ERROR CAST, TO_* → ERROR
Conversió tolerant — (no existeix TRY_CAST) — — TRY_CAST, TRY_CONVERT CAST(… DEFAULT … ON CONVERSION ERROR)
'' enfront de NULL Són diferents Són diferents Són diferents Són diferents '' ÉS NULL

Tres avisos. L'ISNULL de SQL Server no és IS NULL: són coses diferents i s'escriuen gairebé igual. A Oracle, '' és NULL, així que l'ús 2 de NULLIF allà hi sobra… i tot el codi que depengui de distingir-los deixa de funcionar en migrar. I PostgreSQL no té TRY_CAST: per a una conversió tolerant cal validar abans amb una expressió regular (text ~ '^[0-9]+$', de 04-01) o escriure una funció pròpia (mòdul 10).

Errors habituals i consells

  • Confiar en la conversió implícita. '10' > '9' és false. Converteix tu i quedarà escrit el que vols dir.
  • Esperar que NUMERIC::INTEGER trunqui. Arrodoneix: 12.9::INTEGER és 13. Per truncar, TRUNC.
  • Convertir a VARCHAR(n) sense comptar els caràcters. És l'única conversió de PostgreSQL que perd dades en silenci.
  • Deixar que '03/04/2025'::DATE decideixi per tu. Depèn de DateStyle. Fes servir TO_DATE amb patró, o ISO a l'origen.
  • Creure que COALESCE arregla les cadenes buides. '' no és nul: la fórmula correcta és COALESCE(NULLIF(x, ''), 'valor'). I tots els arguments han de ser de tipus compatibles.
  • Ficar COALESCE(col, 0) dins d'un agregat sense pensar-hi. Canvia el denominador: 4,08 va passar a 2,13. Només si el zero és un valor real.
  • Oblidar COALESCE(SUM(x), 0) en informes. SUM sobre zero files retorna NULL, no 0, i la cel·la surt buida.
  • Protegir-se de la divisió per zero amb un AND. El planificador reordena. NULLIF(divisor, 0) sí que protegeix.
  • Aplicar un CAST a una columna al WHERE. Igual que una funció: impedeix fer servir l'índex (08-03).
  • Consell: normalitza a l'entrada, no a cada consulta. Si el CSV porta '12,50', arregla-ho en carregar; no reparteixis REPLACE per cinquanta informes.
  • Consell: COALESCE per presentar, mai per calcular; i si hi calcules, escriu en un comentari per què el zero és legítim.
  • Consell: fes servir CAST en lloc de :: en el codi que hagis de publicar o portar. Costa cinc caràcters més i funciona a tot arreu.

Exercicis

Exercici 1

Prepara el llistat de comandes per a direcció: id, data, estat, nom del client i una columna gestionat_per amb el nom complet del comercial o el text 'Canal web' quan no n'hi hagi. Afegeix-hi metode amb el mètode de pagament en majúscules, mostra les comandes 15 a 20 i comprova que cap cel·la no queda buida.

Exercici 2

Sobre productes, calcula el marge percentual defensiu —(preu - cost) / preu * 100 a dos decimals— de manera que la consulta no falli mai encara que algun dia preu valgui 0 o cost sigui NULL. Mostra el cost amb COALESCE a 0.00 i explica per què aquesta substitució concreta és discutible.

Exercici 3

Un company presenta aquest informe de satisfacció i conclou que "la valoració mitjana del catàleg és de 2,13 sobre 5, un desastre":

SELECT ROUND(AVG(COALESCE(r.puntuacio, 0)), 2) AS mitjana
FROM productes      AS p
LEFT JOIN ressenyes AS r ON r.producte_id = p.id;

(1) D'on surt el 2,13 i per què és enganyós? (2) Escriu la consulta correcta i dona'n la xifra. (3) Escriu-ne una tercera que doni les dues mètriques útils alhora: la mitjana dels productes valorats i quants productes no tenen cap ressenya.

Solucions

Solució 1

SELECT co.id, co.data_comanda, co.estat,
       CONCAT_WS(' ', c.nom, c.cognoms)                                    AS client,
       COALESCE(NULLIF(CONCAT_WS(' ', e.nom, e.cognoms), ''), 'Canal web') AS gestionat_per,
       UPPER(co.metode_pagament)                                           AS metode
FROM comandes      AS co
JOIN clients       AS c ON c.id = co.client_id
LEFT JOIN empleats AS e ON e.id = co.empleat_id
WHERE co.id BETWEEN 15 AND 20 ORDER BY co.id;
id data_comanda estat client gestionat_per metode
15 2025-12-02 lliurat Lucía Martínez Soler Canal web TARGETA
16 2025-12-19 enviat Javier Ortega Ruiz Óscar Peris Blasco TARGETA
17 2026-01-13 enviat Sofia Moreira Costa Canal web PAYPAL
18 2026-01-27 pagat Ana Belmonte Roca Laia Puig Sanchis TRANSFERENCIA
19 2026-02-09 pagat Pau Llorens Vidal Canal web TARGETA
20 2026-02-21 pendent Camille Dubois Marc Estévez Roig CONTRAREEMBORSAMENT

El JOIN amb clients pot ser intern perquè client_id és NOT NULL; el d'empleats ha de ser LEFT JOIN, o perdries la meitat de les comandes (03-03). I el NULLIF és imprescindible: sense ell, les tres files web mostrarien una cel·la buida en comptes de 'Canal web'.

Solució 2

SELECT id, nom, preu,
       COALESCE(cost, 0.00)                                            AS cost_presentat,
       ROUND((preu - COALESCE(cost, 0)) * 100.0 / NULLIF(preu, 0), 2)  AS marge_pct
FROM productes WHERE id IN (1, 5, 15, 18) ORDER BY id;
id nom preu cost_presentat marge_pct
1 Oli d'oliva verge extra 500 ml 12.50 7.80 37.60
5 Tomàquet triturat ecològic 400 g 1.95 0.90 53.85
15 Te verd matcha cerimonial 30 g 22.00 12.50 43.18
18 Raspall de dents de bambú 3.50 1.20 65.71

NULLIF(preu, 0) blinda la divisió i 100.0 evita la divisió entera de 06-02. Però el COALESCE(cost, 0) és discutible, i molt: un cost desconegut no és un cost de zero euros. Amb aquesta substitució, un producte del qual el proveïdor encara no ha comunicat el preu de compra apareixeria amb un marge del 100 %, la xifra més optimista possible i la més falsa. L'honest és deixar que el marge surti NULL —(preu - cost) ja ho fa tot sol— i que l'informe mostri "sense dades".

Solució 3

1. El LEFT JOIN produeix 23 files: les 12 ressenyes més una fila fabricada per cadascun dels 11 productes sense ressenya. COALESCE(r.puntuacio, 0) converteix aquestes 11 absències en onze zeros, així que la mitjana és 49 / 23 = 2,13. És enganyós perquè un producte sense ressenyes no ha rebut un zero: no ha rebut res. L'informe mesura la manca de ressenyes, no la satisfacció.

2. La consulta correcta és la que deixa que AVG faci el que fa des de 04-04 —ignorar els nuls— i retorna 4,08 sobre 5, la xifra canònica del mòdul 4. No és cap desastre: és una valoració bona. 3. I les dues mètriques separades, cadascuna responent a la seva pregunta:

SELECT ROUND(AVG(r.puntuacio), 4)                          AS mitjana_valorats,
       COUNT(r.id)                                         AS ressenyes,
       COUNT(DISTINCT r.producte_id)                       AS productes_valorats,
       COUNT(DISTINCT p.id) - COUNT(DISTINCT r.producte_id) AS productes_sense_ressenya
FROM productes      AS p
LEFT JOIN ressenyes AS r ON r.producte_id = p.id;
mitjana_valorats ressenyes productes_valorats productes_sense_ressenya
4.0833 12 9 11

Ara l'informe diu la veritat completa: els productes valorats treuen 4,08 de mitjana, però 11 dels 20 no tenen cap ressenya. Aquest segon número és el problema real de BotigaVerda, i estava amagat dins del 2,13. Quan un COALESCE barreja dues preguntes en una xifra, la solució no és afinar la xifra: és separar les preguntes.

Conclusió

Ja tens el pegament:

  • Distingeixes la conversió implícita —que el motor fa sense avisar i pot retornar una altra cosa, com ara '10' > '9' essent false— de l'explícita, que escrius tu i queda documentada. Converteixes amb CAST(expr AS tipus) (estàndard) o expr::tipus (PostgreSQL), sabent que :: s'aplica abans que l'aritmètica.
  • Coneixes les trampes: el text no numèric produeix ERROR (i això és bo), NUMERIC::INTEGER arrodoneix en comptes de truncar, '03/04/2025' depèn de DateStyle i la conversió a VARCHAR(n) trunca en silenci. I controles el format amb TO_NUMBER, TO_CHAR i TO_DATE quan l'estàndard no n'hi ha prou.
  • Substitueixes nuls amb COALESCE, que és peresosa, admet diversos arguments i exigeix tipus compatibles; i fabriques nuls amb NULLIF, els dos usos del qual són evitar la divisió per zero i tractar '' com a nul.
  • Tens memoritzada la fórmula COALESCE(NULLIF(x, ''), 'valor'), que tanca definitivament el problema del || i del CONCAT_WS que arrossegaves des de 02-02. I saps que COALESCE equival a un CASE, i quan fer servir cadascun.
  • I sobretot, saps que COALESCE dins d'un agregat canvia el resultat: 4,08 es va convertir en 2,13 per comptar com a zeros onze productes que ningú no havia valorat. COALESCE fora de l'agregat presenta; a dins, decideix.

Queda una última peça. COALESCE només sap respondre una pregunta —"és nul?"— i totes les altres continuen fora del teu abast: classificar un producte com a "barat", "mitjà" o "car" segons el seu preu; posar un semàfor d'estoc; ordenar els estats d'una comanda pel seu ordre de flux i no alfabèticament; o convertir les files d'un GROUP BY en columnes d'un informe. Per a això cal lògica condicional dins de la consulta, i és el que porta l'última lliçó del mòdul: CASE, expressions condicionals.

◄ Anterior
◄
Següent ►
►

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2026. Tots els drets reservats