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

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');

Fix 1: Add LIMIT 1 if exactly one row is acceptable

Any matching row is sufficient

SELECT id INTO uid FROM users WHERE name = uname LIMIT 1;

Fix 2: Handle too_many_rows in an EXCEPTION block

Multiple rows indicate a data integrity problem

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;

What not to do

Sources

Confidence assessment

See also