PostgreSQL: Check If Array Contains NULL

Working with arrays, you sometimes need to know: “Does this array have any NULL values mixed in?” Maybe you’re validating data integrity, filtering out incomplete records, or building business logic based on whether a skill list is complete. An array with NULL values means something is missing or was marked as unknown.

PostgreSQL gives you several ways to detect NULL values within arrays. Some are more performant, some are more readable. Knowing which to use depends on your specific situation.

Problem

Array: {100, NULL, 250} - check if NULL exists anywhere in the array.

Solution 1: Using UNNEST with EXISTS

SELECT
    emp_id,
    first_name,
    skills,
    CASE
        WHEN EXISTS(SELECT 1 FROM UNNEST(skills) s WHERE s IS NULL)
        THEN 'Contains NULL'
        ELSE 'No NULL values'
    END as contains_null
FROM employees
LIMIT 5;

Result:

emp_id | first_name | skills                | contains_null
-------+------------+-----------------------+---------------
     1 | James      | {Python,SQL,NULL}     | Contains NULL
     2 | Mary       | {Java,C++}            | No NULL values
     3 | David      | {PostgreSQL,NULL}     | Contains NULL

How It Works

EXISTS checks if any element is NULL. If found, return ‘Contains NULL’.

Solution 2: Using ARRAY_AGG with Count

SELECT
    emp_id,
    first_name,
    skills,
    ARRAY_LENGTH(skills, 1) as total_elements,
    ARRAY_LENGTH(ARRAY_REMOVE(skills, NULL), 1) as non_null_elements,
    CASE
        WHEN ARRAY_LENGTH(skills, 1) > ARRAY_LENGTH(ARRAY_REMOVE(skills, NULL), 1)
        THEN 'Contains NULL'
        ELSE 'No NULL'
    END as has_null
FROM employees
WHERE skills IS NOT NULL;

Result:

emp_id | first_name | total_elements | non_null_elements | has_null
-------+------------+----------------+-------------------+----------
     1 | James      |              3 |                 2 | Contains NULL
     2 | Mary       |              2 |                 2 | No NULL

How it works: If removing NULLs reduces array length, NULLs existed.

Solution 3: Using Position (Simplest)

SELECT
    emp_id,
    first_name,
    CASE
        WHEN NULL = ANY(skills)
        THEN 'Has NULL'
        ELSE 'No NULL'
    END as contains_null
FROM employees;

The NULL = ANY(array) operator checks for NULL presence.

Solution 4: Filter Out NULLs

Remove NULL elements and compare:

SELECT
    emp_id,
    skills as original,
    ARRAY_REMOVE(skills, NULL) as without_nulls,
    CASE
        WHEN array_length(skills, 1) != array_length(ARRAY_REMOVE(skills, NULL), 1)
        THEN 'Has NULL'
        ELSE 'No NULL'
    END as contains_null
FROM employees
WHERE skills IS NOT NULL;

Real-World Example: Data Quality Check

Find employees with incomplete skill lists:

SELECT
    emp_id,
    first_name,
    skills,
    COUNT(*) FILTER (WHERE skill IS NULL) as null_count
FROM employees e,
LATERAL UNNEST(e.skills) AS skill
WHERE e.skills IS NOT NULL
GROUP BY e.emp_id, e.first_name, e.skills
HAVING COUNT(*) FILTER (WHERE skill IS NULL) > 0
ORDER BY emp_id;

Performance Comparison

Method Speed Readability
UNNEST + EXISTS Fast Good
ARRAY_REMOVE + LENGTH Medium Good
NULL = ANY Fast Excellent
LATERAL UNNEST Slow Best

FAQ

Q: Does NULL = ANY work? A: Yes, it’s the cleanest approach for checking NULL presence.

Q: How do I count NULLs in an array? A: Use COUNT(*) FILTER (WHERE element IS NULL) with UNNEST.

Q: Can I remove NULLs from an array? A: Yes, use ARRAY_REMOVE(array_col, NULL).