PostgreSQL P0002 Error: Causes and Solutions Complete Guide

PostgreSQL P0002: No Data Found — What It Means and How to Fix It

PostgreSQL error code P0002 (NO_DATA_FOUND) is a PL/pgSQL exception raised when a SELECT INTO STRICT statement or a FETCH from a cursor returns zero rows. Unlike a plain SELECT query that silently returns an empty result set, PL/pgSQL functions using STRICT enforce that exactly one row must be returned. If your function hits this error in production, it means your code assumed data would always exist — and that assumption was wrong.

---

Top 3 Causes

1. Using SELECT INTO STRICT Without Exception Handling

The most common cause. When STRICT is used and no rows match, PostgreSQL immediately raises NO_DATA_FOUND.

-- This will raise P0002 if user_id = 9999 does not exist
CREATE OR REPLACE FUNCTION get_user(p_id INT) RETURNS users AS $$
DECLARE
    v_user users%ROWTYPE;
BEGIN
    SELECT * INTO STRICT v_user FROM users WHERE user_id = p_id;
    RETURN v_user;
END;
$$ LANGUAGE plpgsql;

Fix: Always wrap with an EXCEPTION block.

CREATE OR REPLACE FUNCTION get_user_safe(p_id INT) RETURNS users AS $$
DECLARE
    v_user users%ROWTYPE;
BEGIN
    SELECT * INTO STRICT v_user FROM users WHERE user_id = p_id;
    RETURN v_user;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No user found for id: %', p_id;
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Multiple users found for id: %', p_id;
END;
$$ LANGUAGE plpgsql;

---

2. Cursor FETCH Without Checking FOUND

When manually fetching from a cursor, attempting to use the fetched variable without confirming data was actually retrieved causes unpredictable behavior or P0002.

-- Unsafe: no FOUND check after FETCH
CREATE OR REPLACE FUNCTION process_pending() RETURNS VOID AS $$
DECLARE
    v_rec  orders%ROWTYPE;
    cur    CURSOR FOR SELECT * FROM orders WHERE status = 'PENDING';
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_rec;

        -- Always check FOUND immediately after FETCH
        EXIT WHEN NOT FOUND;

        UPDATE orders SET status = 'DONE'
        WHERE order_id = v_rec.order_id;
    END LOOP;
    CLOSE cur;
END;
$$ LANGUAGE plpgsql;

---

3. Missing Reference Data Due to Soft/Hard Delete Conflicts

When a function looks up data that should always exist (e.g., a user profile or a product record) but the record was deleted — either by a hard delete or excluded by a soft-delete filter — P0002 is triggered.

-- Defensive approach: validate before querying
CREATE OR REPLACE FUNCTION get_active_product(p_id INT) RETURNS products AS $$
DECLARE
    v_product products%ROWTYPE;
BEGIN
    SELECT * INTO v_product
    FROM products
    WHERE product_id = p_id
      AND is_deleted = FALSE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Active product not found: %', p_id
            USING ERRCODE = 'P0002';
    END IF;

    RETURN v_product;
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Summary

Situation	Fix
SELECT INTO STRICT returns 0 rows	Add EXCEPTION WHEN NO_DATA_FOUND block
Cursor returns no more rows	Check EXIT WHEN NOT FOUND after every FETCH
Reference data missing	Use IF NOT FOUND guard after non-STRICT SELECT INTO
Need to avoid STRICT entirely	Use SELECT INTO without STRICT + FOUND variable

---

Prevention Tips

1. Adopt a standard function template with exception handling.
Every PL/pgSQL function in your codebase should include NO_DATA_FOUND and TOO_MANY_ROWS handlers by default. Use the plpgsql_check extension to statically analyze functions for missing exception handling during CI/CD.

-- Enable static analysis extension
CREATE EXTENSION IF NOT EXISTS plpgsql_check;
SELECT * FROM plpgsql_check_function('get_active_product(int)');

2. Enforce referential integrity at the database level.
Use foreign key constraints and, where soft deletes are required, add CHECK constraints or partial indexes to ensure your application queries are consistent with the actual data lifecycle. Never rely solely on application-layer validation to guarantee data existence.

---

Related Error Codes

• P0001 — raise_exception: Explicitly raised exceptions in PL/pgSQL; often paired with P0002 in error handling blocks.
• P0003 — too_many_rows: The counterpart to P0002; raised when SELECT INTO STRICT returns more than one row. Always handle both together.
• 02000 — no_data: The SQL-standard status code that P0002 is based on, promoted to a full exception in PL/pgSQL context.

---

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