PostgreSQL raises P0002 (no_data_found) when a PL/pgSQL statement that assumes at least one affected row finds none.
PostgreSQL Error P0002 (no_data_found) occurs when a SELECT INTO, UPDATE, or DELETE in PL/pgSQL affects zero rows while your code expects at least one. Handle the NOT FOUND condition with IF NOT FOUND or EXCEPTION blocks to stop the error.
PostgreSQL Error P0002
PostgreSQL throws P0002 with the condition name no_data_found when a PL/pgSQL command that must return or modify a row actually touches none. The runtime engine stops execution and raises the exception so you can react or fail fast.
The error usually appears inside procedures, functions, or anonymous DO blocks that use SELECT INTO, UPDATE, or DELETE and immediately reference the outcome.
Because no rows qualified, the NOT FOUND flag is true and PostgreSQL escalates the situation to an exception.
Most occurrences stem from expecting a specific key or status that is missing. Data drift, incorrect joins, and overly strict WHERE clauses all set NOT FOUND. Concurrent deletes or isolation levels that hide uncommitted rows can also trigger the condition.
Check your WHERE predicate first.
Confirm that at least one row should match under normal circumstances. If zero rows is an acceptable outcome, wrap the statement in IF NOT FOUND logic or use GET DIAGNOSTICS to silence the exception. Otherwise, update your query or seed the missing data.
Missing primary keys often cause SELECT INTO ... WHERE id = x to fail. Supply the correct id or insert the row. Batch maintenance jobs sometimes DELETE rows then UPDATE the same ids.
Use RETURNING in the DELETE to skip a second statement.
Always test WHERE clauses in plain SQL before embedding them in PL/pgSQL. Use ASSERT or RAISE with custom messages to pinpoint unexpected gaps. Embrace defensive coding: IF NOT FOUND THEN INSERT, or gracefully log and continue.
P0003 no_data_target is similar but refers to COPY operations. 23503 foreign_key_violation appears when the missing row is referenced elsewhere.
Handle each with comparable validation checks.
.
No. UPDATE, DELETE, and FETCH statements inside PL/pgSQL also set NOT FOUND and can raise P0002.
You cannot change core behavior, but you can catch the exception with BEGIN ... EXCEPTION WHEN no_data_found THEN ... END.
The function may run under different search_path, role, or isolation level, yielding a different result set.
Galaxy's AI copilot previews row counts and flags queries that return zero rows, helping you catch NOT FOUND scenarios before they reach production.
.avif)
.avif){kind=logo}
