NULLIF

Kirish: Ushbu qo'llanmada siz PostgreSQL NULLIF() funksiyasi yordamida ikkita ifodani solishtirish va ular teng bo'lgan taqdirda NULL qaytarishni o'rganasiz.

PostgreSQL NULLIF() funksiyasiga kirish

PostgreSQLda NULLIF() funksiyasi ikkita ifodani taqqoslaydi. Agar ikkala ifoda bir-biriga teng bo'lsa, funksiya NULL qaytaradi. Agar ular teng bo'lmasa, birinchi ifodaning o'zini qaytaradi.

Funksiyaning sintaksisi:

NULLIF(expression1, expression2)

• Agar expression1 = expression2 bo'lsa -> NULL qaytadi.
• Agar expression1 != expression2 bo'lsa -> expression1 qaytadi.

Eslatma: Funksiya taqqoslash uchun tenglik (=) operatoridan foydalangani sababli, ikkala ifoda bir-biriga mos keluvchi ma'lumot turlariga ega bo'lishi kerak.

Oddiy misollar

Ikkala qiymat teng bo'lganda NULL qaytadi:

SELECT NULLIF(0, 0) AS result;
-- NULL

Qiymatlar teng bo'lmaganda birinchi qiymat qaytadi:

SELECT NULLIF(1, 0) AS result;
-- 1

Ma'lumotlarni tozalashda NULLIF dan foydalanish

Ko'pincha foydalanuvchilar maydonlarga bo'sh satr ('') yoki 'N/A' deb yozib ketishadi. Ma'lumotlar bazasida bunday noto'g'ri qiymatlarni haqiqiy NULL ga aylantirish (data cleansing) uchun NULLIF juda qo'l keladi.

Masalan, quyidagi kontaktlar jadvalini yaratamiz:

CREATE TABLE contacts(
    contact_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(25),
    email VARCHAR(255)
);

INSERT INTO contacts (name, phone, email) 
VALUES
('Alice Johnson', 'N/A', ''),
('Bob Smith', '123-456-789', 'bob@example.com'),
('Carol White', '123-456-788', 'carol@example.com'),
('David Brown', '123-456-787', 'david@example.com'),
('Eve Black', '123-456-786', '');

Endi 'N/A' va bo'sh satrlarni NULL ga almashtiramiz:

UPDATE contacts
SET
  phone = NULLIF(phone, 'N/A'),
  email = NULLIF(email, '') 
RETURNING *;

Natija:

contact_id |     name      |    phone    |       email
------------+---------------+-------------+-------------------
          1 | Alice Johnson | NULL        | NULL
          2 | Bob Smith     | 123-456-789 | bob@example.com
          3 | Carol White   | 123-456-788 | carol@example.com
          4 | David Brown   | 123-456-787 | david@example.com
          5 | Eve Black     | 123-456-786 | NULL

Nolga bo'lish xatosini (division by zero) oldini olish

Matematikada va SQLda sonni 0 ga bo'lish division by zero xatoligini keltirib chiqaradi va butun so'rovni to'xtatadi.

Buning oldini olish uchun maxrajda NULLIF(denominator, 0) dan foydalaniladi. Agar maxraj 0 bo'lsa, NULLIF uni NULL ga aylantiradi, har qanday sonni NULL ga bo'lish esa xatolik emas, shunchaki NULL natija beradi:

WITH stocks AS (
    SELECT
      product_name,
      safety_stock,
      SUM(quantity) AS inventory_qty
    FROM
      products
      INNER JOIN inventories USING (product_id)
    GROUP BY
      product_name,
      safety_stock
)
SELECT
  product_name,
  inventory_qty,
  safety_stock,
  inventory_qty / NULLIF(safety_stock, 0) AS stock_coverage
FROM
  stocks
ORDER BY
  product_name;

Natija:

        product_name        | inventory_qty | safety_stock | stock_coverage
----------------------------+---------------+--------------+----------------
 Apple AirPods Pro 3        |           180 |           45 |              4
 Apple iMac 24"             |           320 |            0 |           NULL
 Apple iPad Pro 12.9        |           170 |           15 |             11
 Apple iPhone 15            |           150 |           20 |              7

safety_stock = 0 bo'lgan mahsulotlarda so'rov to'xtab qolmasdan, stock_coverage ustuniga xavfsiz tarzda NULL yoziladi.

Xulosa

• Ikkita qiymat teng bo'lsa NULL, aks holda birinchi qiymatni olish uchun NULLIF(v1, v2) funksiyasidan foydalaning.
• Nolga bo'lish xatoligining oldini olish uchun maxrajni NULLIF(maxraj, 0) ko'rinishida yozing.
• Ma'lumotlar bazasida noto'g'ri yoki bo'sh qiymatlarni tozalashda NULLIF juda samarali.