PostgreSQL NULLIF Function

The NULLIF() function in PostgreSQL returns NULL if two arguments are equal, otherwise returns the first argument. It's useful for converting specific values to NULL, avoiding division by zero, and data normalization.

Syntax

NULLIF(value1, value2)
-- Returns NULL if value1 = value2, otherwise returns value1

Official Documentation

Example usage

-- Convert empty strings to NULL
SELECT
    username,
    NULLIF(email, '') AS email
FROM users;

-- Avoid division by zero
SELECT
    total_sales,
    total_orders,
    total_sales / NULLIF(total_orders, 0) AS average_order_value
FROM sales_summary;

-- Convert specific value to NULL
SELECT
    product_name,
    NULLIF(status, 'unknown') AS status
FROM products;

-- Clean imported data (convert placeholder values to NULL)
UPDATE customers
SET
    phone = NULLIF(phone, 'N/A'),
    email = NULLIF(email, 'none'),
    address = NULLIF(address, '');

-- Combine with COALESCE for value replacement
SELECT
    product_name,
    COALESCE(NULLIF(discount, 0), sale_price, regular_price) AS final_price
FROM products;

-- Convert zero to NULL in calculations
SELECT
    user_id,
    NULLIF(login_count, 0) AS login_count,
    last_login
FROM user_activity;

-- Handle sentinel values
SELECT
    employee_name,
    NULLIF(salary, -1) AS salary,  -- -1 might indicate "not disclosed"
    NULLIF(department_id, 0) AS department_id  -- 0 might indicate "unassigned"
FROM employees;

-- Data validation and cleaning
SELECT
    NULLIF(TRIM(first_name), '') AS first_name,
    NULLIF(TRIM(last_name), '') AS last_name,
    NULLIF(LOWER(TRIM(email)), '') AS email
FROM raw_user_data;

-- Conditional NULL in aggregations
SELECT
    category,
    AVG(NULLIF(rating, 0)) AS avg_rating  -- Ignore 0 ratings
FROM products
GROUP BY category;

-- Multiple NULLIF for different sentinel values
SELECT
    measurement,
    COALESCE(
        NULLIF(NULLIF(NULLIF(value, -999), -1), 0),
        previous_value
    ) AS clean_value
FROM sensor_data;

Common issues

1. Type comparison: Arguments must be comparable types. Comparing incompatible types will error.
2. Case sensitivity: String comparisons are case-sensitive by default.
3. Not a NULL check: NULLIF returns value1 or NULL, not a boolean.
4. Single purpose: NULLIF only compares two values; use CASE for more complex conditions.

Best practices

1. Use NULLIF to convert empty strings to NULL: NULLIF(column, '').
2. Prevent division by zero errors: value / NULLIF(divisor, 0).
3. Combine with COALESCE for replacement: COALESCE(NULLIF(col, 'N/A'), 'Unknown').
4. Use in data cleaning to convert sentinel/placeholder values to NULL.

Frequently Asked Questions

Q: What's the difference between NULLIF and COALESCE?
A: NULLIF converts a specific value to NULL: NULLIF(col, '') returns NULL if col is empty. COALESCE replaces NULL with a value: COALESCE(col, 'default') returns 'default' if col is NULL. They're complementary functions.

Q: How do I convert multiple values to NULL?
A: Chain NULLIF calls: NULLIF(NULLIF(NULLIF(col, ''), 'N/A'), 'unknown') or use CASE for better readability: CASE WHEN col IN ('', 'N/A', 'unknown') THEN NULL ELSE col END.

Q: Can NULLIF prevent division by zero?
A: Yes, amount / NULLIF(quantity, 0) returns NULL instead of causing a division by zero error when quantity is 0.

Q: Is NULLIF case-sensitive for strings?
A: Yes, string comparison is case-sensitive by default. Use NULLIF(LOWER(col), 'value') for case-insensitive comparison.

Q: What's the equivalent CASE expression for NULLIF?
A: NULLIF(a, b) is equivalent to CASE WHEN a = b THEN NULL ELSE a END. NULLIF is more concise for this specific pattern.