PostgreSQL 02000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 02000: No Data

PostgreSQL SQLSTATE 02000 ("no data") occurs when a query or cursor operation returns no rows in a context where data retrieval was attempted. This is most commonly encountered inside PL/pgSQL functions when using SELECT INTO, FETCH from a cursor, or any operation expecting at least one result row. While not a critical system error, unhandled "no data" conditions can silently corrupt business logic.

---

Top 3 Causes

1. SELECT INTO with No Matching Rows

When using SELECT INTO inside a PL/pgSQL block, if no rows match the WHERE clause, the target variable is set to NULL. With the STRICT modifier, PostgreSQL explicitly raises a NO_DATA_FOUND (P0002) exception derived from 02000.

-- Problematic: No error raised, but v_name is silently NULL
DO $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT username INTO v_name
    FROM users WHERE id = 99999;

    -- v_name is NULL here, no error raised without STRICT
    RAISE NOTICE 'Name: %', v_name;
END;
$$;

-- With STRICT: explicitly raises NO_DATA_FOUND
DO $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT username INTO STRICT v_name
    FROM users WHERE id = 99999;
-- raises P0002 / 02000 if no row found
END;
$$;

2. Cursor FETCH Beyond Last Row

When fetching rows from a cursor, attempting a FETCH after all rows have been consumed triggers the "no data" condition. Without checking FOUND after each FETCH, the loop can mishandle the end-of-data state.

DO $$
DECLARE
    cur CURSOR FOR SELECT id FROM orders WHERE status = 'NEW';
    v_id INT;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_id;
        EXIT WHEN NOT FOUND; -- Must check FOUND after every FETCH
        RAISE NOTICE 'Processing order: %', v_id;
    END LOOP;
    CLOSE cur;
END;
$$;

3. Function Returning No Rows

A function declared to return a single row (RETURNS record or a table type) that internally finds no data will produce a "no data" condition for the caller if not handled properly.

CREATE OR REPLACE FUNCTION get_user_by_email(p_email TEXT)
RETURNS users AS $$
DECLARE
    v_user users%ROWTYPE;
BEGIN
    SELECT * INTO STRICT v_user
    FROM users WHERE email = p_email;
    RETURN v_user;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL; -- Gracefully return NULL instead of raising
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Solutions

Always pair SELECT INTO STRICT with an exception handler:

BEGIN
    SELECT col INTO STRICT v_var FROM tbl WHERE condition;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle missing data gracefully
        v_var := 'default_value';
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Unexpected duplicate data found.';
END;

Use FOR loops instead of manual cursor FETCH to avoid "no data" edge cases:

-- Safer: FOR loop handles empty result sets automatically
FOR rec IN SELECT * FROM orders WHERE status = 'NEW' LOOP
    -- process rec
END LOOP;

---

Prevention Tips

1. Always check FOUND immediately after any SELECT INTO or FETCH operation. Treat FOUND = false as a valid, expected state and write explicit handling code for it rather than assuming data always exists.

2. Standardize exception handling in all PL/pgSQL functions. Establish a team coding convention that every function using STRICT must include NO_DATA_FOUND and TOO_MANY_ROWS in its EXCEPTION block. Include "no data" scenarios in your integration test suite to catch regressions before deployment.

---

Related Errors

• P0002 (no_data_found): The PL/pgSQL-specific exception raised when SELECT INTO STRICT finds no rows; a direct descendant of 02000.
• P0003 (too_many_rows): The counterpart error when STRICT returns more than one row — always handle both together.
• 02001: "No additional dynamic result sets returned," a sibling code in the same 02 class.

---

📖 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.