COALESCE(a, b, c) zwraca pierwszy z argumentów, który nie jest NULL, więc COALESCE(nickname, name) pokazuje pseudonim, jeśli istnieje, a w przeciwnym razie imię. NULLIF(a, b) działa odwrotnie: zwraca NULL, gdy a równa się b. CASE WHEN condition THEN value ... ELSE value END wybiera wartość według warunku.
Ben i Chen nie mają pseudonimu, więc widać ich pełne imiona. Ben dostaje też no phone.
Zamiana NULL na wartość domyślną
COALESCE to w PostgreSQL odpowiednik IFNULL, ISNULL i NVL. Użyj go, żeby zamienić NULL na 0 przed obliczeniami, bo każde działanie z NULL daje NULL, oraz żeby agregat po zerowej liczbie wierszy miał wartość:
Zamówienie 2 pokazuje NULL bez COALESCE i 80 z nim, a suma po braku wierszy staje się 0 zamiast NULL.
Wszystkie argumenty muszą sprowadzać się do jednego typu. COALESCE(quantity, 'none') na kolumnie całkowitej kończy się błędem "invalid input syntax for type integer", bo PostgreSQL próbuje odczytać 'none' jako liczbę całkowitą. Najpierw zrzutuj kolumnę: COALESCE(quantity::text, 'none'). Rzutowanie opisuje strona o CAST.
Zamiana wartości na NULL przez NULLIF
NULLIF(a, b) zwraca NULL, jeśli a = b, a w przeciwnym razie a. Zapobiega dzieleniu przez zero i zamienia puste napisy na NULL, żeby COALESCE mogło je zastąpić:
spring dostaje współczynnik klikalności 5. summer dostaje NULL zamiast błędu "division by zero", a jego pusty właściciel staje się unassigned.
Wybór wartości przez CASE WHEN
Ta forma sprawdza warunki po kolei i zwraca pierwsze dopasowanie. Wiersze, które do niczego nie pasują, dostają wartość z ELSE albo NULL, gdy ELSE nie ma:
CASE w ORDER BY przesuwa wyprzedane produkty na koniec, więc mug jest ostatni. Krótsza forma porównuje jedno wyrażenie z wartościami: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. Nie sprawdzi NULL, bo NULL = NULL nie jest prawdą. Do tego użyj WHEN x IS NULL w pełnej formie.
Liczenie według warunku
CASE wewnątrz agregatu liczy albo sumuje według kategorii w jednym przebiegu. PostgreSQL ma też klauzulę FILTER, która mówi to samo bardziej wprost:
SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
count(*) FILTER (WHERE status = 'paid') AS paid_too
FROM orders;
Obie liczą tylko opłacone zamówienia: count() pomija NULL, które CASE zwraca dla pozostałych wierszy.
GREATEST i LEAST ignorują NULL
GREATEST(a, b, ...) i LEAST(a, b, ...) zwracają największy i najmniejszy argument. W PostgreSQL pomijają argumenty NULL i zwracają NULL tylko wtedy, gdy każdy argument jest NULL, więc GREATEST(1, NULL, 3) to 3. MySQL zwraca NULL, gdy którykolwiek argument jest NULL, co ma znaczenie przy przenoszeniu zapytań. Pełne reguły znajdziesz w dokumentacji PostgreSQL o wyrażeniach warunkowych.
Najczęściej zadawane pytania
Jaki jest odpowiednik IFNULL, ISNULL albo NVL w PostgreSQL?
COALESCE(value, fallback). PostgreSQL nie ma funkcji IFNULL (MySQL), ISNULL (SQL Server) ani NVL (Oracle); COALESCE to standardowy SQL i przyjmuje też więcej niż dwa argumenty.
Jak zamienić NULL na 0 w PostgreSQL?
Opakuj kolumnę: COALESCE(discount, 0). Dla sumy po zerowej liczbie wierszy, która zwraca NULL, napisz COALESCE(SUM(amount), 0).
Czy COALESCE oblicza wszystkie argumenty?
Nie. Oblicza argumenty po kolei i zatrzymuje się na pierwszym, który nie jest NULL, więc dalsze argumenty nie są dla tego wiersza liczone.
Czy można użyć CASE w klauzuli WHERE?
Tak, wyrażenie CASE może stać wszędzie, gdzie może stać wartość, także w WHERE, ORDER BY i wewnątrz agregatów. W WHERE zwykły warunek z AND/OR jest zwykle czytelniejszy.