EC
P0003
PostgreSQLERRORNotablePL/pgSQL ErrorHIGH confidence

too_many_rows

What this means

A SELECT INTO STRICT statement in PL/pgSQL returned more than one row, violating the expectation that exactly one row would be returned.

Why it happens
  1. 1SELECT INTO STRICT query matched multiple rows when only one was expected
  2. 2Missing or insufficiently selective WHERE clause in SELECT INTO STRICT
  3. 3Data grew to include duplicates since the function was written, now multiple rows match
  4. 4Unique constraint missing on the queried column
How to reproduce

PL/pgSQL function using SELECT INTO STRICT where the query can return multiple rows

trigger — this will error
trigger — this will error
CREATE OR REPLACE FUNCTION get_user_by_name(uname text) RETURNS int AS $
DECLARE
  uid int;
BEGIN
  SELECT id INTO STRICT uid FROM users WHERE name = uname;
  RETURN uid;
END;
$ LANGUAGE plpgsql;

-- If multiple users have the same name:
SELECT get_user_by_name('Alice');

expected output

ERROR:  P0003: query returned more than one row

Fix 1

Add LIMIT 1 if exactly one row is acceptable

WHEN Any matching row is sufficient

Add LIMIT 1 if exactly one row is acceptable
SELECT id INTO uid FROM users WHERE name = uname LIMIT 1;

Why this works

Removes the STRICT constraint and limits to one row; eliminates the too_many_rows error

Fix 2

Handle too_many_rows in an EXCEPTION block

WHEN Multiple rows indicate a data integrity problem

Handle too_many_rows in an EXCEPTION block
BEGIN
  SELECT id INTO STRICT uid FROM users WHERE name = uname;
EXCEPTION WHEN too_many_rows THEN
  RAISE EXCEPTION 'Duplicate user name found: %', uname;
END;

Why this works

Surfaces the ambiguity as an application error with a clear message

What not to do

✕ Do not silently take the first row with LIMIT 1 if duplicate rows indicate a data corruption problem

Hiding the ambiguity can mask data integrity issues

Sources
Official documentation ↗

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

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors