PostgreSQL P0003 Error: Causes and Solutions Complete Guide

PostgreSQL Error P0003: too_many_rows — What It Is and How to Fix It

PostgreSQL error code P0003 (too_many_rows) occurs inside PL/pgSQL functions or DO blocks when a SELECT INTO statement returns more than one row, but the code expects exactly one. This is a runtime error, meaning it only surfaces when actual data violates the single-row assumption in your code. It is one of the most common PL/pgSQL pitfalls encountered in production environments.

---

Top 3 Causes

1. SELECT INTO Without a Unique Filter

Using SELECT INTO with a non-unique column as the filter condition is the most frequent cause. If the WHERE clause does not narrow results down to a single row, PostgreSQL raises P0003 immediately.

-- This will raise P0003 if multiple users have status = 'active'
DO $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO v_name
    FROM users
    WHERE status = 'active';  -- Non-unique filter!
END;
$$;

2. Using SELECT INTO STRICT with Multiple Results

The STRICT modifier enforces exactly one row. It is a great practice for data integrity, but it will throw P0003 the moment your query returns two or more rows — even if it used to return just one during development.

-- Raises P0003 if more than one row matches
DO $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO STRICT v_name
    FROM users
    WHERE department = 'Engineering';
END;
$$;

3. JOIN-Induced Row Multiplication

Joins — especially on 1:N relationships — can silently multiply rows. A query that returned one row during testing with a small dataset can return many rows in production as data grows.

-- Dangerous: order_items join multiplies rows per order
DO $$
DECLARE
    v_total NUMERIC;
BEGIN
    SELECT o.amount INTO STRICT v_total
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.id = 999;  -- Multiple items = multiple rows!
END;
$$;

---

Quick Fix Solutions

Fix 1 — Add LIMIT 1 or use a unique column:

DO $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO v_name
    FROM users
    WHERE status = 'active'
    ORDER BY created_at DESC
    LIMIT 1;
END;
$$;

Fix 2 — Catch TOO_MANY_ROWS in an EXCEPTION block:

CREATE OR REPLACE FUNCTION get_user_by_email(p_email TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_name TEXT;
BEGIN
    SELECT name INTO STRICT v_name
    FROM users
    WHERE email = p_email;

    RETURN v_name;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
        -- Fallback: return the most recently created match
        SELECT name INTO v_name
        FROM users
        WHERE email = p_email
        ORDER BY created_at DESC
        LIMIT 1;
        RETURN v_name;
END;
$$;

Fix 3 — Use aggregate functions to collapse JOIN results:

CREATE OR REPLACE FUNCTION get_order_total(p_order_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC;
BEGIN
    -- Aggregate eliminates the multi-row problem entirely
    SELECT SUM(price * quantity) INTO v_total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN COALESCE(v_total, 0);
END;
$$;

---

Prevention Tips

Always pair STRICT with an EXCEPTION block. Never use SELECT INTO STRICT without handling both NO_DATA_FOUND (P0002) and TOO_MANY_ROWS (P0003). Make this a team coding standard.

Enforce uniqueness at the schema level. If your PL/pgSQL code expects a single row from a column, that column should have a UNIQUE constraint or index. This prevents duplicate data from ever causing a P0003 at runtime.

-- Add a unique constraint to prevent the root cause
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Or a partial unique index for conditional uniqueness
CREATE UNIQUE INDEX uix_active_users_email
    ON users (email)
    WHERE status = 'active';

-- Proactively find duplicates before they cause P0003
SELECT email, COUNT(*) AS cnt
FROM users
WHERE status = 'active'
GROUP BY email
HAVING COUNT(*) > 1;

---

Related Errors

• P0002 (no_data_found) — The counterpart to P0003; raised when SELECT INTO STRICT returns zero rows.
• P0001 (raise_exception) — General PL/pgSQL exception, often used in the same EXCEPTION blocks.
• 21000 (cardinality_violation) — Raised when a scalar subquery returns more than one row at the plain SQL level, similar in nature to P0003.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.