no data

Category: No DataVersions: All Postgres versions

What this means

SQLSTATE 02000 is raised when a SELECT INTO or FETCH statement returns no rows. In PL/pgSQL it can be tested via the FOUND variable. It does not abort the transaction but may indicate unexpected empty results.

Why it happens

1SELECT INTO or FETCH from a cursor returns zero rows
2A PL/pgSQL statement expected to return exactly one row returned none

How to reproduce

SELECT INTO in PL/pgSQL where no row matches the condition.

trigger — this will ERROR

DO $
DECLARE v_name TEXT;
BEGIN
  SELECT name INTO v_name FROM users WHERE id = 999; -- no such row
  IF NOT FOUND THEN
    RAISE NOTICE 'No row found (SQLSTATE 02000)';
  END IF;
END $;

NOTICE: No row found (SQLSTATE 02000)

Fix 1: Check FOUND after SELECT INTO

In PL/pgSQL when a row may or may not exist.

fix

IF NOT FOUND THEN
  -- handle missing row
END IF;

Why this works

The FOUND boolean is set to FALSE when SELECT INTO returns no rows, allowing graceful handling without exceptions.

Fix 2: Use SELECT INTO STRICT to raise an exception on no data

When the row is required and absence is a programming error.

fix

SELECT name INTO STRICT v_name FROM users WHERE id = p_id;

Why this works

STRICT raises NO_DATA_FOUND (02000) as an exception that must be caught, preventing silent mishandling.

What not to do

✗

Ignore the FOUND check after SELECT INTO

Why it's wrong: The variable retains its previous value when no row is found, leading to silent logic errors using stale data.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

🔧 Source ref: Class 02 — No Data

📖 Further reading: PL/pgSQL SELECT INTO

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE. Behaviour of FOUND and STRICT is well-documented and stable.