SQLSTATE P0002 ERROR Class P0: PL/pgSQL Error

no_data_found query returned no rows — P0002

PostgreSQL error "query returned no rows" (SQLSTATE P0002): what it means, common causes, and how to fix it.

PG 12, 13, 14, 15, 16, 17, 18 Official docs
Last reviewed May 2025 Grounded in source

Diagnostic Queries

Symptoms

A PL/pgSQL SELECT … INTO STRICT (or similar strict context) expected exactly one row but the query returned none. PostgreSQL raises SQLSTATE P0002 (no_data_found).

  • Only occurs with the STRICT option in PL/pgSQL.
  • The query matched zero rows when one was required.
  • Common in lookups that assume the key always exists.

What the server log shows

ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function get_customer(integer) line 5 at SQL statement

Why PostgreSQL raises this — what the manual says

Section 41.5.3 Executing a Command with a Single-Row Result:

“If the STRICT option is specified, the command must return exactly one row or a run-time error will be reported, either NO_DATA_FOUND (no rows) or TOO_MANY_ROWS (more than one row).”

INTO STRICT guarantees a single-row result. When the query returns no rows, PL/pgSQL cannot assign the target variables and raises P0002 rather than silently leaving them null.

Common causes

  • A lookup key that does not exist in the table.
  • Over-restrictive WHERE conditions in the SELECT INTO.
  • Assuming a row exists when it may not.

How to fix it

  1. Handle the case with EXCEPTION WHEN NO_DATA_FOUND THEN ….
  2. Drop STRICT if zero rows is acceptable, and check NOT FOUND.
  3. Validate that the key exists before the strict lookup.

Related & next steps

Reference: PostgreSQL 18 Section 43.5 “Basic Statements”.

Was this helpful?