PostgreSQL COALESCE Function

The COALESCE() function in PostgreSQL returns the first non-NULL value from a list of arguments. It's essential for handling NULL values, providing default values, and simplifying conditional logic.

Syntax

COALESCE(value1, value2, ..., valueN)
-- Returns the first non-NULL value, or NULL if all are NULL

Official Documentation

Example usage

-- Basic NULL handling with default value
SELECT
    username,
    COALESCE(email, 'no-email@example.com') AS contact_email
FROM users;

-- Multiple fallback values
SELECT
    product_name,
    COALESCE(sale_price, regular_price, msrp, 0.00) AS display_price
FROM products;

-- Replace NULL in calculations
SELECT
    order_id,
    quantity * COALESCE(unit_price, 0) AS total
FROM order_items;

-- Fallback to different columns
SELECT
    customer_name,
    COALESCE(mobile_phone, home_phone, work_phone, 'No phone') AS contact_number
FROM customers;

-- String concatenation with NULLs
SELECT
    COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM contacts;

-- Better approach for concatenation (use CONCAT instead)
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM contacts;

-- Default values in aggregations
SELECT
    category,
    COALESCE(SUM(sales), 0) AS total_sales
FROM products
LEFT JOIN sales ON products.id = sales.product_id
GROUP BY category;

-- Complex fallback logic
SELECT
    user_id,
    COALESCE(
        preferred_name,
        nickname,
        first_name,
        username,
        'User #' || user_id::TEXT
    ) AS display_name
FROM users;

-- INSERT with default values
INSERT INTO settings (user_id, theme, language)
VALUES (
    123,
    COALESCE(:theme, 'light'),
    COALESCE(:language, 'en')
);

-- UPDATE with conditional replacement
UPDATE users
SET last_login = COALESCE(:new_login_time, last_login);

-- NULL-safe comparisons in WHERE
SELECT * FROM products
WHERE COALESCE(discount, 0) > 0;

Common issues

1. Type compatibility: All arguments must be compatible types or convertible to a common type.
2. Performance: COALESCE evaluates arguments left to right and stops at the first non-NULL.
3. Empty strings: COALESCE doesn't treat empty strings as NULL; use NULLIF for conversion.
4. Not a NULL check: COALESCE returns a value, not a boolean. For NULL checks, use IS NULL.

Best practices

1. Use COALESCE instead of complex CASE WHEN expressions for simple NULL handling.
2. Place most likely non-NULL values first for better performance.
3. For string concatenation, prefer CONCAT which handles NULLs automatically.
4. Combine with NULLIF to treat empty strings as NULL: COALESCE(NULLIF(column, ''), 'default').

Frequently Asked Questions

Q: What's the difference between COALESCE and IFNULL/NVL?
A: COALESCE is SQL standard and accepts multiple arguments. IFNULL (MySQL) and NVL (Oracle) accept only two arguments. In PostgreSQL, COALESCE is the standard function. There's no IFNULL or NVL built-in.

Q: Does COALESCE work with empty strings?
A: COALESCE only handles NULL values. Empty strings ('') are not considered NULL. To treat empty strings as NULL, use NULLIF: COALESCE(NULLIF(column, ''), 'default').

Q: How many arguments can COALESCE accept?
A: COALESCE can accept any number of arguments (limited only by PostgreSQL's expression complexity limits). It returns the first non-NULL value from left to right.

Q: What does COALESCE return if all arguments are NULL?
A: It returns NULL. If you need a guaranteed non-NULL result, make sure the last argument is a literal value: COALESCE(col1, col2, 'default').

Q: Is COALESCE the same as CASE WHEN?
A: COALESCE(a, b, c) is shorthand for CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END. COALESCE is more concise for NULL handling.