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.
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.
First, verify the query logic by running it directly in psql to confirm whether records exist:
SELECT * FROM employees WHERE employee_id = 42;If the query returns no rows, the issue is that no matching data exists in the table.
Replace SELECT INTO STRICT with SELECT INTO and check the FOUND variable afterward:
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.
If you prefer using STRICT mode, wrap it in an exception handler:
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.
For UPDATE or DELETE operations, check row count before proceeding:
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:
UPDATE employees SET salary = 50000 WHERE employee_id = 42;
IF NOT FOUND THEN
RAISE NOTICE 'No employee updated';
END IF;If the missing data is acceptable, return a default value:
SELECT COALESCE(name, 'Unknown') INTO v_name
FROM employees WHERE employee_id = 42;Or use LEFT JOIN to ensure a row is always returned:
SELECT e.name INTO v_name
FROM (SELECT 1) AS dummy
LEFT JOIN employees e ON e.employee_id = 42;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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL