Menu

COALESCE, ‏NULLIF ו-CASE WHEN ב-PostgreSQL

COALESCE(a, b, ...) מחזיר את הארגומנט הראשון שאינו NULL, ‏NULLIF(a, b) הופך ערך ל-NULL, ו-CASE WHEN בוחר ערך לפי תנאי.

בדף הזה יש עורכים שאפשר להריץ. לערוך, להריץ ולראות את הפלט מיד.

COALESCE(a, b, c) מחזיר את הראשון מבין הארגומנטים שלו שאינו NULL, כך ש-COALESCE(nickname, name) מציג את הכינוי כשיש כזה ואת השם אחרת. NULLIF(a, b) עושה את ההפך: הוא מחזיר NULL כש-a שווה ל-b. CASE WHEN condition THEN value ... ELSE value END בוחר ערך לפי תנאי.

ל-Ben ול-Chen אין כינוי, ולכן מופיעים השמות המלאים שלהם. Ben מקבל גם no phone.

להחליף NULL בערך ברירת מחדל

COALESCE הוא ה-IFNULL, ה-ISNULL וה-NVL של PostgreSQL. השתמשו בו כדי להפוך NULL ל-0 לפני חישוב, כי כל חישוב עם NULL נותן NULL, וכדי לתת ערך לפונקציית צבירה על אפס שורות:

הזמנה 2 מציגה NULL בלי COALESCE ו-80 איתו, והסכום על אפס שורות הופך ל-0 במקום NULL.

כל הארגומנטים חייבים להתפרש לאותו טיפוס. COALESCE(quantity, 'none') על עמודה מספרית נכשל עם "invalid input syntax for type integer", כי PostgreSQL מנסה לקרוא את 'none' כמספר שלם. המירו קודם את העמודה: COALESCE(quantity::text, 'none'). הדף על CAST מסביר המרות.

להפוך ערך ל-NULL עם NULLIF

NULLIF(a, b) מחזיר NULL אם a = b, ואת a אחרת. הוא מונע חילוק באפס, והוא הופך מחרוזות ריקות ל-NULL כדי ש-COALESCE יוכל להחליף אותן:

spring מקבל שיעור הקלקות של 5. summer מקבל NULL במקום שגיאת "division by zero", והבעלים הריק שלו הופך ל-unassigned.

לבחור ערך עם CASE WHEN

הצורה הזו בודקת תנאים לפי הסדר ומחזירה את ההתאמה הראשונה. שורות שלא מתאימות לשום תנאי מקבלות את ערך ה-ELSE, או NULL כשאין ELSE:

ה-CASE שב-ORDER BY דוחף מוצרים שאזלו לסוף, ולכן mug מופיע אחרון. צורה קצרה יותר משווה ביטוי אחד לערכים: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. היא לא יכולה לבדוק NULL, כי NULL = NULL הוא לא true. לשם כך השתמשו ב-WHEN x IS NULL בצורה המלאה.

ספירה לפי תנאי

CASE בתוך פונקציית צבירה סופר או מסכם לפי קטגוריה במעבר אחד. ל-PostgreSQL יש גם את פסוקית FILTER, שאומרת את אותו דבר בצורה ישירה יותר:

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

שתיהן סופרות רק הזמנות ששולמו: count() מדלג על ה-NULL ש-CASE מחזיר לשאר השורות.

GREATEST ו-LEAST מתעלמים מ-NULL

GREATEST(a, b, ...) ו-LEAST(a, b, ...) מחזירים את הארגומנט הגדול ביותר ואת הקטן ביותר. ב-PostgreSQL הם מדלגים על ארגומנטים שהם NULL ומחזירים NULL רק כשכל הארגומנטים NULL, כך ש-GREATEST(1, NULL, 3) הוא 3. ‏MySQL מחזיר NULL כשארגומנט כלשהו הוא NULL, וזה חשוב כשמעבירים שאילתות. ראו את התיעוד של PostgreSQL על ביטויים מותנים לכל הכללים.

שאלות נפוצות

מה המקבילה של IFNULL, ‏ISNULL או NVL ב-PostgreSQL?

COALESCE(value, fallback). ב-PostgreSQL אין את הפונקציות IFNULL (MySQL), ‏ISNULL (SQL Server) או NVL (Oracle); ‏COALESCE הוא SQL סטנדרטי וגם מקבל יותר משני ארגומנטים.

איך מחליפים NULL ב-0 ב-PostgreSQL?

עוטפים את העמודה: COALESCE(discount, 0). לסכום על אפס שורות, שמחזיר NULL, כתבו COALESCE(SUM(amount), 0).

האם COALESCE מחשב את כל הארגומנטים שלו?

לא. הוא מחשב את הארגומנטים לפי הסדר ועוצר בראשון שאינו NULL, כך שהארגומנטים שאחריו לא מחושבים עבור השורה הזו.

אפשר להשתמש ב-CASE בתוך WHERE?

כן, ביטוי CASE יכול להופיע בכל מקום שערך יכול, כולל WHERE, ‏ORDER BY ובתוך פונקציות צבירה. ב-WHERE תנאי רגיל עם AND/OR בדרך כלל קריא יותר.

איור של שפות התכנות ב-Coddy

ללמוד תכנות עם Coddy

להתחיל