Incident brief
No data found
A PL/pgSQL SELECT ... INTO STRICT found zero matching rows. PostgreSQL raised an error instead of silently leaving the target null.
What lands in your log
ERROR: query returned no rows
In 10 seconds
- What triggers it
- Run SELECT ... INTO STRICT against a query that matches no rows.
- Fix
- Wrap the STRICT select in a BEGIN/EXCEPTION block and catch WHEN NO_DATA_FOUND if a missing row is an expected, handleable case.
- Proof
- Reproduced on PostgreSQL 16.14 → A SELECT ... INTO STRICT that matched zero rows was rejected with SQLSTATE P0002. The session was unaffected afterward.
Fix
What to do right now
Application-level steps for this error.
- Wrap the STRICT select in a BEGIN/EXCEPTION block and catch WHEN NO_DATA_FOUND if a missing row is an expected, handleable case.
- Or check existence first with an ordinary (non-STRICT) SELECT INTO plus IF NOT FOUND, if you'd rather branch than raise.
INSERT INTO batch7_p0002_accounts(id, balance)
VALUES (999, 42);
DO $$
DECLARE
v_balance numeric;
BEGIN
SELECT balance INTO STRICT v_balance
FROM batch7_p0002_accounts
WHERE id = 999;
RAISE NOTICE 'balance = %', v_balance;
END $$;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
No data found is raised by SELECT ... INTO STRICT. Count the rows for the exact predicate without STRICT before deciding whether the data or the query contract is wrong.
Cardinality of the STRICT predicate
Replace the read-only query from the function; the predicate is invalid for STRICT when this test is true.
SELECT count(*) AS rows_returned,
count(*) = 0 AS would_raise_p0002
FROM (<strict_select_without_into>) AS candidate;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 16 Documentation, Executing a Command with a Single-Row Result (43.5.3)
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).Read the full section on postgresql.org →
The STRICT select with zero rows
Per the manual, STRICT requires exactly one row back, zero rows raises NO_DATA_FOUND (SQLSTATE P0002) instead of silently leaving v_balance null.Confirming the session still works
The STRICT SELECT INTO block is what raised NO_DATA_FOUND, the session itself has no open transaction to clean up, so the next statement runs fine.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Run SELECT ... INTO STRICT against a query that matches no rows.
- 2SQLSTATE P0002 is reported; the block aborts.
One client: a DO block runs SELECT ... INTO STRICT against a WHERE clause matching no rows.
CREATE TABLE batch7_p0002_accounts (id int PRIMARY KEY, balance numeric);DO $$
DECLARE
v_balance numeric;
BEGIN
SELECT balance INTO STRICT v_balance FROM batch7_p0002_accounts WHERE id = 999;
RAISE NOTICE 'balance = %', v_balance;
END $$;SELECT 1 AS session_still_alive;What PostgreSQL actually returned
CREATE TABLEERROR: query returned no rows
CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement session_still_alive
---------------------
1
(1 row)The deeper lab audit for this error
- Fix that hides the error but leaves a silent NULL: exact SQL, output, and verdict
- A manual-grounded production interpretation of the lab result
Card required. Cancel before day 7 and you are not charged.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.