COALESCE(a, b, c) restituisce il primo dei suoi argomenti che non è NULL, quindi COALESCE(nickname, name) mostra il nickname quando c'è e altrimenti il nome. NULLIF(a, b) fa il contrario: restituisce NULL quando a è uguale a b. CASE WHEN condition THEN value ... ELSE value END sceglie un valore in base a una condizione.
Ben e Chen non hanno un nickname, quindi compare il loro nome completo. Ben riceve anche no phone.
Sostituire NULL con un valore predefinito
COALESCE è l'IFNULL, l'ISNULL e l'NVL di PostgreSQL. Usalo per trasformare NULL in 0 prima di un calcolo, perché qualsiasi operazione aritmetica con NULL dà NULL, e per dare un valore a un'aggregazione su zero righe:
L'ordine 2 mostra NULL senza COALESCE e 80 se lo usi, e la somma su zero righe diventa 0 invece di NULL.
Tutti gli argomenti devono ricondursi a un solo tipo. COALESCE(quantity, 'none') su una colonna intera fallisce con "invalid input syntax for type integer", perché PostgreSQL prova a leggere 'none' come intero. Converti prima la colonna: COALESCE(quantity::text, 'none'). La pagina su CAST spiega i cast.
Trasformare un valore in NULL con NULLIF
NULLIF(a, b) restituisce NULL se a = b, e altrimenti a. Evita la divisione per zero e trasforma le stringhe vuote in NULL, così COALESCE può sostituirle:
spring ottiene un tasso di clic di 5. summer ottiene NULL invece di un errore "division by zero", e il suo responsabile vuoto diventa unassigned.
Scegliere un valore con CASE WHEN
Questa forma verifica le condizioni in ordine e restituisce la prima che corrisponde. Le righe che non corrispondono a niente ricevono il valore di ELSE, oppure NULL quando manca ELSE:
Il CASE in ORDER BY spinge in fondo i prodotti esauriti, quindi mug arriva per ultimo. Una forma più corta confronta un'espressione con dei valori: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. Non può verificare NULL, perché NULL = NULL non è vero. Per quello usa WHEN x IS NULL nella forma completa.
Contare in base a una condizione
Un CASE dentro un'aggregazione conta o somma per categoria in un solo passaggio. PostgreSQL ha anche la clausola FILTER, che dice la stessa cosa in modo più diretto:
SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
count(*) FILTER (WHERE status = 'paid') AS paid_too
FROM orders;
Entrambe contano solo gli ordini pagati: count() salta il NULL che CASE restituisce per le altre righe.
GREATEST e LEAST ignorano NULL
GREATEST(a, b, ...) e LEAST(a, b, ...) restituiscono l'argomento più grande e quello più piccolo. In PostgreSQL saltano gli argomenti NULL e restituiscono NULL solo quando tutti gli argomenti sono NULL, quindi GREATEST(1, NULL, 3) vale 3. MySQL restituisce NULL quando anche un solo argomento è NULL, e questo conta quando porti le query da un database all'altro. Le regole complete sono nella documentazione di PostgreSQL sulle espressioni condizionali.
Domande frequenti
Qual è l'equivalente di IFNULL, ISNULL o NVL in PostgreSQL?
COALESCE(value, fallback). PostgreSQL non ha la funzione IFNULL (MySQL), ISNULL (SQL Server) o NVL (Oracle); COALESCE è SQL standard e accetta anche più di due argomenti.
Come sostituisco NULL con 0 in PostgreSQL?
Avvolgi la colonna: COALESCE(discount, 0). Per una somma su zero righe, che restituisce NULL, scrivi COALESCE(SUM(amount), 0).
COALESCE valuta tutti i suoi argomenti?
No. Valuta gli argomenti in ordine e si ferma al primo che non è NULL, quindi per quella riga gli argomenti successivi non vengono calcolati.
Posso usare CASE in una clausola WHERE?
Sì, un'espressione CASE può comparire ovunque possa stare un valore, compresi WHERE, ORDER BY e dentro le funzioni di aggregazione. In WHERE di solito una semplice condizione con AND/OR è più leggibile.