PostgreSQL 02000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 02000: No Data — What It Means and How to Fix It

PostgreSQL SQLSTATE 02000 ("no data") is raised when a query or cursor operation returns zero rows in a context that expects data, most commonly inside PL/pgSQL functions and stored procedures. Unlike a hard error, it acts as a status signal — but ignoring it silently can corrupt your business logic. Understanding when and why it fires is essential for writing robust database code.

---

Top 3 Causes

1. SELECT INTO Returns No Rows

The most common trigger: a SELECT INTO inside a PL/pgSQL block finds no matching rows, leaving the target variable as NULL without raising a visible error unless you check.

DO $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO v_name
    FROM users
    WHERE id = 9999; -- Non-existent ID

    -- v_name is NULL here; no error raised automatically
    IF NOT FOUND THEN
        RAISE NOTICE 'No user found. Handling gracefully.';
        v_name := 'UNKNOWN';
    END IF;

    RAISE NOTICE 'Result: %', v_name;
END;
$$;

Always check the FOUND special variable immediately after SELECT INTO.

---

2. Cursor FETCH Exhausted All Rows

When iterating a cursor with a manual LOOP, fetching beyond the last row triggers the no data condition. Without an explicit exit condition, your loop may misbehave silently.

DO $$
DECLARE
    cur CURSOR FOR SELECT id, amount FROM orders WHERE status = 'PENDING';
    v_id     INT;
    v_amount NUMERIC;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_id, v_amount;
        EXIT WHEN NOT FOUND; -- Critical: exit when no more rows

        RAISE NOTICE 'Order %, Amount %', v_id, v_amount;
    END LOOP;
    CLOSE cur;
END;
$$;

Pro tip: Use a FOR rec IN SELECT ... loop instead — it handles NOT FOUND automatically.

---

3. SELECT INTO STRICT With No Matching Row

Adding STRICT tells PostgreSQL to expect exactly one row. Zero rows raises NO_DATA_FOUND (internally mapped to 02000/P0002). Without an EXCEPTION block, this will abort your function entirely.

CREATE OR REPLACE FUNCTION get_user(p_id INT)
RETURNS TEXT LANGUAGE plpgsql AS
$$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO STRICT v_name
    FROM users
    WHERE id = p_id;

    RETURN v_name;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE WARNING 'No user found for ID %', p_id;
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Multiple rows found for ID %', p_id;
END;
$$;

-- Usage
SELECT get_user(1);     -- Returns name
SELECT get_user(9999);  -- Returns NULL with warning

---

Quick Fix Solutions

-- Fix 1: Defensive COALESCE at SQL level
SELECT COALESCE(
    (SELECT name FROM users WHERE id = 9999),
    'DEFAULT_USER'
) AS safe_name;

-- Fix 2: LEFT JOIN instead of relying on inner join presence
SELECT o.order_id,
       COALESCE(u.name, 'Deleted User') AS user_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;

-- Fix 3: Dynamic SQL with FOUND check
DO $$
DECLARE v_result TEXT;
BEGIN
    EXECUTE 'SELECT name FROM users WHERE id = $1'
    INTO v_result USING 9999;

    IF NOT FOUND THEN
        RAISE NOTICE 'Dynamic query returned no data.';
    END IF;
END;
$$;

---

Prevention Tips

1. Enforce IF NOT FOUND as a coding standard.
Add a rule to your team's PL/pgSQL style guide: every SELECT INTO must be followed by an IF NOT FOUND check. Enforce it in code reviews and automate it with pgTAP unit tests covering "empty result" scenarios.

2. Prefer FOR loops over manual cursor management.
The FOR record IN SELECT ... pattern automatically exits when data is exhausted, eliminating the need to manually handle EXIT WHEN NOT FOUND and reducing the risk of 02000 slipping through unhandled.

---

Related Error Codes

Code	Name	Notes
P0002	NO_DATA_FOUND	PL/pgSQL-specific; raised by SELECT INTO STRICT with no rows
P0003	TOO_MANY_ROWS	Counterpart to P0002; always handle both together
02001	no additional dynamic result sets	Related to dynamic result set handling

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.