PostgreSQL No Data (SQLSTATE 02000)

In PL/pgSQL, SQLSTATE 02000 with condition name no_data (also written NO_DATA) is raised when a SELECT INTO statement, a FETCH from a cursor, or a PERFORM finds no rows. You will typically see it as an unhandled exception message like ERROR: query returned no rows or it surfaces silently as the special variable FOUND being set to false after the offending statement.

What This Error Means

SQLSTATE class 02 is the "No Data" warning class defined by the SQL standard. PostgreSQL maps this class to situations where a statement that was expected to return (or consume) at least one row returned nothing. The condition no_data (02000) is the generic member of this class; PostgreSQL also defines no_additional_dynamic_result_sets_returned (02001) in the same class, but 02000 is by far the one developers encounter in practice.

In plain SQL (outside PL/pgSQL), SELECT simply returning zero rows is normal — the client gets an empty result set. The behavior changes inside PL/pgSQL functions and procedures. When you use SELECT INTO, FETCH, or EXECUTE ... INTO to assign a row to a variable, PostgreSQL sets the boolean variable FOUND to false if nothing was returned. If your code does not check FOUND after such a statement, the target variable is left NULL and execution continues silently, which is a common source of data-loss bugs.

When STRICT mode is used with SELECT INTO or EXECUTE ... INTO, PostgreSQL enforces that exactly one row must be returned. If zero rows are found, PostgreSQL raises no_data_found (P0002) — a different, PL/pgSQL-specific SQLSTATE — rather than 02000. The no_data condition (02000) is typically encountered through cursor FETCH operations that reach the end of a result set, or when code explicitly raises it. Understanding this distinction is important when writing exception handlers.

Common Causes

1. FETCH past the end of a cursor. After all rows have been consumed from an open cursor, the next FETCH sets FOUND = false and leaves the target variable unchanged. Code that loops without checking FOUND will silently reprocess the last row.

2. SELECT INTO without checking FOUND. A SELECT INTO target_var FROM ... WHERE ... that matches no rows leaves target_var as NULL. Downstream code referencing that variable may behave incorrectly without any error being raised.

3. Explicit RAISE using SQLSTATE 02000. Some stored procedure libraries or frameworks explicitly raise 02000 as a signal that a lookup returned nothing, similar to how application code might throw a "not found" exception. If you are seeing it in logs and do not own the raising code, check third-party extensions or generated code.

4. JDBC / libpq drivers propagating the warning class. Some client drivers surface SQLSTATE 02 warnings from the server (for example, from PL/pgSQL procedures called via CALL) as exceptions in application code, even when the procedure itself handled the condition internally.

How to Fix no_data

1. Check FOUND after SELECT INTO or FETCH.

   CREATE OR REPLACE FUNCTION get_user(p_id INT)
   RETURNS TEXT LANGUAGE plpgsql AS $$
   DECLARE
     v_name TEXT;
   BEGIN
     SELECT name INTO v_name FROM users WHERE id = p_id;
     IF NOT FOUND THEN
       RETURN NULL;  -- or raise a meaningful error
     END IF;
     RETURN v_name;
   END;
   $$;
   

2. Use SELECT INTO STRICT when exactly one row is required. This converts a silent miss into a catchable no_data_found (P0002) exception, making bugs visible rather than hidden.

   BEGIN
     SELECT name INTO STRICT v_name FROM users WHERE id = p_id;
   EXCEPTION
     WHEN no_data_found THEN
       RAISE EXCEPTION 'User % not found', p_id;
   END;
   

3. Guard cursor loops with EXIT WHEN NOT FOUND.

   OPEN cur FOR SELECT id, name FROM users;
   LOOP
     FETCH cur INTO v_id, v_name;
     EXIT WHEN NOT FOUND;
     -- process row
   END LOOP;
   CLOSE cur;
   

   Alternatively, use a FOR rec IN SELECT ... loop, which handles cursor management and the FOUND check automatically.

4. Catch SQLSTATE 02000 explicitly when needed. If you are calling code that raises 02000 as a deliberate signal, add an exception handler:

   BEGIN
     -- some operation that may raise 02000
   EXCEPTION
     WHEN SQLSTATE '02000' THEN
       -- handle the no-data case
       RETURN NULL;
   END;
   

5. Prefer set-returning functions over single-row SELECT INTO where practical. If the caller can handle zero or more rows naturally, returning a SETOF type or a TABLE(...) result avoids the silent-null problem entirely.

Additional Information

• SQLSTATE class 02 and the no_data condition have been part of the SQL standard and PostgreSQL since at least PostgreSQL 7.x. There is no version-specific behavior change to be aware of for modern versions (12+).
• The closely related condition no_data_found (SQLSTATE P0002) is the PL/pgSQL-specific variant raised by SELECT INTO STRICT and is more commonly seen in application logs than 02000 itself. P0002 is an error (severity ERROR); 02000 is technically a warning class in the SQL standard.
• JDBC drivers (pgjdbc) map SQLSTATE 02000 to java.sql.SQLWarning by default. If you are seeing it as an exception in Java, check whether your connection or statement error handling is treating warnings as errors.
• ORM frameworks (Hibernate, SQLAlchemy, ActiveRecord) that call PL/pgSQL procedures via CALL may surface this condition differently depending on whether they inspect getWarnings() on the statement or connection object.
• There are no direct performance implications. no_data is a control-flow signal, not a sign of table bloat, missing indexes, or query inefficiency.

Frequently Asked Questions

Why does my PL/pgSQL function return NULL without any error when a row is not found?

This is the default behavior of SELECT INTO without STRICT: if no rows match, the target variable is set to NULL and FOUND is set to false, but no exception is raised. Add an IF NOT FOUND THEN check immediately after the statement, or switch to SELECT INTO STRICT to make the absence of a row raise an explicit exception.

What is the difference between SQLSTATE 02000 (no_data) and P0002 (no_data_found)?

02000 is a SQL-standard warning-class code meaning "the statement returned no data." P0002 is a PostgreSQL-specific error raised only by SELECT INTO STRICT and EXECUTE ... INTO STRICT inside PL/pgSQL when zero rows are returned. P0002 has ERROR severity and is caught with WHEN no_data_found. 02000 is more of a signal-level condition and is less commonly caught in practice.

How do I detect that a cursor FETCH has reached the end of results?

Check the FOUND variable immediately after the FETCH statement. FOUND is true if a row was retrieved, false if the cursor was exhausted. The idiomatic pattern is FETCH cur INTO vars; EXIT WHEN NOT FOUND; inside a LOOP.

Can no_data (02000) cause a transaction rollback?

No. SQLSTATE class 02 is a warning class, not an error class. If the condition is unhandled inside a PL/pgSQL block, it does not abort the transaction. However, if you explicitly RAISE EXCEPTION using SQLSTATE '02000', that does abort the current transaction (because of RAISE EXCEPTION severity), so be careful not to confuse the condition with its severity when writing handlers.