Fixing PostgreSQL FOR Loop "Upper Bound Cannot Be Null" Error: A Guide to Array Handling and NULL Safety

Original: https://www.jacobchak.com/blog/postgresql-array-null-safety

Published: Feb 13, 2025

Read Time: 5 min

If you’ve ever seen this error in PostgreSQL:

ERROR: 22004: upper bound of FOR loop cannot be null

it usually means your loop bound came from array_length() on an empty or NULL array.

There’s a simple fix: make the bound NULL-safe.

Why This Matters Even If You Use AI

Yes, you could paste this error into an AI tool and get a one-line fix.

But here’s why it’s worth understanding yourself:

  • AI gives you a fix, but not always the safest one for your data.
  • Knowing why PostgreSQL behaves this way means you can spot and prevent similar bugs elsewhere.
  • You’ll see the same NULL-handling pattern in strings, numbers, and dates — not just arrays.
  • When you hit edge cases (e.g., unusual array bounds), you’ll know exactly which pattern to reach for.

This post is about making you faster and more confident the next time the error appears.

Quick Fix

Before (fails on empty arrays):

FOR i IN 1..array_length(my_items, 1) LOOP
  item := my_items[i]; -- process the item
END LOOP;

After (NULL-safe):

FOR i IN 1..COALESCE(array_length(my_items, 1), 0) LOOP
  item := my_items[i]; -- process the item
END LOOP;

Here, my_items could be a list of anything—IDs to update, names to link, or settings to apply.
If the array is empty, COALESCE(..., 0) makes the loop range 1..0, so it simply doesn’t run instead of throwing an error.

Why This Happens

PostgreSQL’s array_length() behaves differently from what many expect—it returns NULL for both empty arrays and NULL arrays.

-- Non-empty array
SELECT array_length(ARRAY['a','b','c'], 1);  -- 3

-- Empty array
SELECT array_length(ARRAY[]::TEXT[], 1);     -- NULL

-- NULL array
SELECT array_length(NULL, 1);                -- NULL

PostgreSQL treats “no array” (NULL) and “array with no elements” (empty) as different things,
but in both cases, array_length() gives you NULL. And FOR loops can’t use NULL as a bound.

The takeaway: don’t assume empty arrays return 0—in PostgreSQL, they don’t.

COALESCE to the Rescue

COALESCE(expr, fallback) returns the first non-NULL value from its arguments:

SELECT COALESCE(array_length(ARRAY[]::TEXT[], 1), 0);  -- 0
SELECT COALESCE(array_length(ARRAY['a','b'], 1), 0);   -- 2

By wrapping array_length() in COALESCE(..., 0), you guarantee a number—0 for empty or NULL arrays—so your loop runs safely.
It’s a small change, but it eliminates a class of annoying runtime errors.

Alternative Approaches

If you don’t need to access array elements by index, FOREACH can be simpler:

FOREACH element IN ARRAY my_items LOOP
  -- process each element
END LOOP;

Or, check the length first:

IF array_length(my_items, 1) IS NOT NULL THEN
  FOR i IN 1..array_length(my_items, 1) LOOP
    -- process each element
  END LOOP;
END IF;

These avoid NULL loop bounds without relying on COALESCE.

NULL Safety Beyond Arrays

This NULL-handling pattern shows up all over PostgreSQL.
A few examples:

-- Strings
SELECT COALESCE(NULLIF(trim(user_input), ''), 'default_value');

-- Aggregates
SELECT COALESCE(SUM(amount), 0) FROM orders WHERE status = 'pending';

-- Dates
SELECT COALESCE(MAX(updated_at), NOW()) FROM logs WHERE user_id = 42;

Once you start looking, you’ll see COALESCE everywhere.

Key Takeaways

  1. Empty arrays return NULL length — array_length() doesn’t give you 0.
  2. FOR loops need a real number — wrap array_length() in COALESCE(..., 0) or use FOREACH.
  3. NULL-safety is a habit — the same thinking applies to strings, numbers, and dates.

Handle NULLs up front, and your PostgreSQL functions will fail less and read cleaner.

When to Use a Different Pattern

The COALESCE(array_length(...), 0) approach is great for most indexed loops,
but sometimes a different tool is better.

1. You don’t need indexes — use FOREACH:

FOREACH element IN ARRAY COALESCE(my_items, '{}') LOOP
  -- process element
END LOOP;

2. You care about true array bounds: PostgreSQL arrays can have non-1 lower bounds. If that’s possible in your code:

l := COALESCE(array_lower(my_items, 1), 1);
u := COALESCE(array_upper(my_items, 1), 0);
FOR i IN l..u LOOP
  element := my_items[i];
END LOOP;

3. You want indexes without array quirks — use unnest with ordinality:

FOR r IN SELECT elem, ord
         FROM unnest(COALESCE(my_items, '{}')) WITH ORDINALITY AS t(elem, ord)
LOOP
  -- r.elem is the value, r.ord is the index (1-based)
END LOOP;

Pick the one that matches how you actually use the data — it’ll be cleaner and safer.