Menu

COALESCE, NULLIF, dan CASE WHEN di PostgreSQL

COALESCE(a, b, ...) mengembalikan argumen pertama yang bukan NULL, NULLIF(a, b) mengubah sebuah nilai menjadi NULL, dan CASE WHEN memilih nilai berdasarkan kondisi.

Halaman ini dilengkapi editor yang bisa dijalankan. Edit, jalankan, dan lihat output-nya seketika.

COALESCE(a, b, c) mengembalikan argumen pertama yang bukan NULL, jadi COALESCE(nickname, name) menampilkan nama panggilan kalau ada, dan nama lengkap kalau tidak. NULLIF(a, b) melakukan kebalikannya: ia mengembalikan NULL saat a sama dengan b. CASE WHEN condition THEN value ... ELSE value END memilih nilai berdasarkan kondisi.

Ben dan Chen tidak punya nama panggilan, jadi nama lengkap mereka yang muncul. Ben juga mendapat no phone.

Mengganti NULL dengan nilai default

COALESCE adalah IFNULL, ISNULL, dan NVL-nya PostgreSQL. Pakai untuk mengubah NULL menjadi 0 sebelum aritmetika, karena aritmetika apa pun dengan NULL menghasilkan NULL, dan untuk memberi nilai pada agregat atas nol baris:

Pesanan 2 menampilkan NULL tanpa COALESCE dan 80 dengan COALESCE, dan penjumlahan atas nol baris menjadi 0, bukan NULL.

Semua argumen harus bermuara ke satu tipe. COALESCE(quantity, 'none') pada kolom integer gagal dengan "invalid input syntax for type integer", karena PostgreSQL mencoba membaca 'none' sebagai integer. Cast dulu kolomnya: COALESCE(quantity::text, 'none'). Halaman CAST membahas cast.

Mengubah nilai menjadi NULL dengan NULLIF

NULLIF(a, b) mengembalikan NULL jika a = b, dan a jika tidak. Fungsi ini mencegah pembagian dengan nol, dan mengubah string kosong menjadi NULL supaya COALESCE bisa menggantinya:

spring mendapat rasio klik 5. summer mendapat NULL alih-alih error "division by zero", dan pemiliknya yang kosong menjadi unassigned.

Memilih nilai dengan CASE WHEN

Bentuk ini menguji kondisi secara berurutan dan mengembalikan yang pertama cocok. Baris yang tidak cocok dengan apa pun mendapat nilai ELSE, atau NULL jika tidak ada ELSE:

CASE di ORDER BY mendorong produk yang habis ke akhir, jadi mug muncul paling akhir. Bentuk yang lebih pendek membandingkan satu ekspresi dengan beberapa nilai: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. Bentuk ini tidak bisa menguji NULL, karena NULL = NULL tidak bernilai true. Untuk itu, pakai WHEN x IS NULL dalam bentuk lengkap.

Menghitung berdasarkan kondisi

CASE di dalam agregat menghitung atau menjumlahkan per kategori dalam satu kali jalan. PostgreSQL juga punya klausa FILTER, yang menyatakan hal yang sama dengan lebih langsung:

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

Keduanya hanya menghitung pesanan yang sudah dibayar: count() melewati NULL yang dikembalikan CASE untuk baris lainnya.

GREATEST dan LEAST mengabaikan NULL

GREATEST(a, b, ...) dan LEAST(a, b, ...) mengembalikan argumen terbesar dan terkecil. Di PostgreSQL, keduanya melewati argumen NULL dan hanya mengembalikan NULL saat setiap argumen bernilai NULL, jadi GREATEST(1, NULL, 3) bernilai 3. MySQL mengembalikan NULL saat ada argumen yang NULL, yang penting diperhatikan saat memindahkan query. Lihat dokumentasi PostgreSQL tentang ekspresi kondisional untuk aturan lengkapnya.

Pertanyaan yang sering diajukan

Apa padanan IFNULL, ISNULL, atau NVL di PostgreSQL?

COALESCE(value, fallback). PostgreSQL tidak punya fungsi IFNULL (MySQL), ISNULL (SQL Server), atau NVL (Oracle); COALESCE adalah SQL standar dan juga menerima lebih dari dua argumen.

Bagaimana cara mengganti NULL dengan 0 di PostgreSQL?

Bungkus kolomnya: COALESCE(discount, 0). Untuk penjumlahan atas nol baris, yang mengembalikan NULL, tulis COALESCE(SUM(amount), 0).

Apakah COALESCE mengevaluasi semua argumennya?

Tidak. Ia mengevaluasi argumen secara berurutan dan berhenti di argumen pertama yang bukan NULL, jadi argumen setelahnya tidak dihitung untuk baris itu.

Bisakah memakai CASE di klausa WHERE?

Bisa, ekspresi CASE bisa muncul di mana pun sebuah nilai bisa muncul, termasuk WHERE, ORDER BY, dan di dalam agregat. Di WHERE, kondisi AND/OR biasa umumnya lebih mudah dibaca.

Ilustrasi bahasa pemrograman di Coddy

Belajar coding dengan Coddy

Mulai