How to fix No data found in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 28, 2025· 271 views

The "no data found" error occurs when a PL/pgSQL SELECT INTO STRICT statement returns zero rows. Handle it using the FOUND variable or catch the NO_DATA_FOUND exception in your stored procedures.

Quick fix

Fixes No data found

SELECT * FROM employees WHERE employee_id = 42;
  1. Check your query returns expected rows
  2. Use the FOUND variable instead of STRICT
  3. Catch NO_DATA_FOUND exception with STRICT
  4. Validate data exists before operations
postgresql — error
SELECT * FROM employees WHERE employee_id = 42;
No data found

What this error means

In PostgreSQL PL/pgSQL, "no data found" (SQLSTATE P0002) indicates that a query expected to return at least one row returned zero rows instead. This commonly occurs when using SELECT INTO STRICT in stored procedures or functions that fail to match any records. Unlike Oracle, PostgreSQL distinguishes between the FOUND variable approach and strict mode, giving you flexible options for handling missing data.

Typical symptoms

  • PL/pgSQL function fails with "query returned no rows" error
  • SELECT INTO STRICT statement raises exception when no matching records exist
  • Function executes but receives NULL values unexpectedly
  • Stored procedure crashes when querying for a specific record that does not exist
  • SQLSTATE P0002 error appears in application logs

Common causes

  1. SELECT INTO STRICT expects exactly one row but the query returns zero rows
  2. Function queries a table with a WHERE clause that matches no records
  3. Using SELECT INTO without checking the FOUND variable to validate results
  4. Deleting or updating records that no longer exist when using STRICT mode
  5. Foreign key reference points to a record that was deleted

How to fix "No data found"

1Check your query returns expected rows

First, verify the query logic by running it directly in psql to confirm whether records exist:

sql
SELECT * FROM employees WHERE employee_id = 42;

If the query returns no rows, the issue is that no matching data exists in the table.

2Use the FOUND variable instead of STRICT

Replace SELECT INTO STRICT with SELECT INTO and check the FOUND variable afterward:

sql
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE employee_id = 42;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Employee not found';
  END IF;
  -- Process v_emp here
END;$$

This gives you explicit control over how to handle missing data.

3Catch NO_DATA_FOUND exception with STRICT

If you prefer using STRICT mode, wrap it in an exception handler:

sql
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO STRICT v_emp FROM employees WHERE employee_id = 42;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE EXCEPTION 'Employee % not found', 42;
  WHEN TOO_MANY_ROWS THEN
    RAISE EXCEPTION 'Multiple employees found';
END;$$

This explicitly handles both no rows and multiple rows cases.

4Validate data exists before operations

For UPDATE or DELETE operations, check row count before proceeding:

sql
UPDATE employees SET salary = 50000 WHERE employee_id = 42;
IF NOT FOUND THEN
  RAISE EXCEPTION 'Employee % not found', 42;
END IF;

Alternatively, use the row count variable:

sql
UPDATE employees SET salary = 50000 WHERE employee_id = 42;
IF NOT FOUND THEN
  RAISE NOTICE 'No employee updated';
END IF;
5Handle optional data with COALESCE

If the missing data is acceptable, return a default value:

sql
SELECT COALESCE(name, 'Unknown') INTO v_name
FROM employees WHERE employee_id = 42;

Or use LEFT JOIN to ensure a row is always returned:

sql
SELECT e.name INTO v_name
FROM (SELECT 1) AS dummy
LEFT JOIN employees e ON e.employee_id = 42;

Advanced notes

PostgreSQL PL/pgSQL handles "no data" differently than Oracle. Oracle uses the NO_DATA_FOUND exception by default, while PostgreSQL requires explicit handling via FOUND or STRICT mode. When migrating from Oracle, update your exception blocks to use WHEN NO_DATA_FOUND THEN syntax, which PostgreSQL supports in PL/pgSQL. For ECPG (Embedded SQL in C), SQLSTATE 02000 indicates query returned zero rows but is typically not fatal—check sqlstate 02000 explicitly if you need to handle it. The P0002 error code is PostgreSQL's internal representation; the 02000 SQLSTATE is the SQL standard equivalent for "no data found" conditions.

Frequently asked questions

What does "No data found" mean?
In PostgreSQL PL/pgSQL, "no data found" (SQLSTATE P0002) indicates that a query expected to return at least one row returned zero rows instead. This commonly occurs when using SELECT INTO STRICT in stored procedures or functions that fail to match any records. Unlike Oracle, PostgreSQL distinguishes between the FOUND variable approach and strict mode, giving you flexible options for handling missing data.
How do I fix "No data found"?
The "no data found" error occurs when a PL/pgSQL SELECT INTO STRICT statement returns zero rows. Handle it using the FOUND variable or catch the NO_DATA_FOUND exception in your stored procedures.
What causes "No data found"?
SELECT INTO STRICT expects exactly one row but the query returns zero rows Function queries a table with a WHERE clause that matches no records Using SELECT INTO without checking the FOUND variable to validate results
Is the "No data found" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading