PostgreSQL 2201W Error: Causes and Solutions Complete Guide

PostgreSQL Error 2201W: invalid row count in limit clause

PostgreSQL error 2201W (invalid_row_count_in_limit_clause) is thrown when the LIMIT clause receives an invalid value — most commonly a negative integer. Unlike some other databases that treat LIMIT -1 as "no limit," PostgreSQL strictly requires a non-negative integer, and will raise this error immediately upon encountering an invalid row count.

---

Top 3 Causes

1. Passing a Negative Value Directly to LIMIT

The most straightforward cause is supplying a literal or bound negative number to the LIMIT clause.

-- This will fail immediately
SELECT * FROM users LIMIT -1;
-- ERROR:  invalid row count in limit clause

SELECT * FROM users LIMIT -100;
-- ERROR:  invalid row count in limit clause

-- Safe alternative: use GREATEST to floor at 0
SELECT * FROM users LIMIT GREATEST(0, -1);  -- returns 0 rows, no error

2. Unvalidated Pagination Logic

Pagination calculations like (page - 1) * page_size can silently produce negative values when page = 0 or a negative page number is passed in from a request parameter.

-- Dangerous pattern — if p_page = 0, offset becomes negative
-- and if passed as LIMIT, causes 2201W
CREATE OR REPLACE FUNCTION bad_paginate(p_page INT, p_size INT)
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM users
    LIMIT p_size
    OFFSET (p_page - 1) * p_size;  -- negative if p_page < 1
END;
$$ LANGUAGE plpgsql;

-- Safe version with validation
CREATE OR REPLACE FUNCTION safe_paginate(p_page INT, p_size INT)
RETURNS SETOF users AS $$
DECLARE
    v_limit  INT := GREATEST(1, COALESCE(p_size, 10));
    v_offset INT := GREATEST(0, (COALESCE(p_page, 1) - 1) * v_limit);
BEGIN
    RETURN QUERY
    SELECT * FROM users
    ORDER BY id
    LIMIT v_limit
    OFFSET v_offset;
END;
$$ LANGUAGE plpgsql;

3. Dynamic SQL with Unvalidated Parameters

When building dynamic SQL with EXECUTE, an unvalidated variable passed as the LIMIT value can cause this error at runtime, making it harder to debug.

-- Risky: no validation before executing
CREATE OR REPLACE FUNCTION risky_query(p_limit INT)
RETURNS void AS $$
BEGIN
    EXECUTE 'SELECT * FROM orders LIMIT ' || p_limit;
    -- If p_limit is -5, this raises 2201W
END;
$$ LANGUAGE plpgsql;

-- Safe: validate first, use USING clause
CREATE OR REPLACE FUNCTION safe_query(p_limit INT)
RETURNS SETOF orders AS $$
DECLARE
    v_limit INT := GREATEST(0, COALESCE(p_limit, 10));
BEGIN
    RETURN QUERY EXECUTE
        'SELECT * FROM orders ORDER BY id LIMIT $1'
    USING v_limit;
END;
$$ LANGUAGE plpgsql;

-- Test it
SELECT * FROM safe_query(-99);   -- returns 0 rows, no error
SELECT * FROM safe_query(NULL);  -- returns 10 rows safely

---

Quick Fix Solutions

-- Option 1: GREATEST() guard
SELECT * FROM orders LIMIT GREATEST(0, :user_input);

-- Option 2: COALESCE + GREATEST combo
SELECT * FROM orders LIMIT GREATEST(1, COALESCE(:user_input, 10));

-- Option 3: Reusable utility function
CREATE OR REPLACE FUNCTION safe_limit(p_val INT, p_default INT DEFAULT 10)
RETURNS INT AS $$
    SELECT GREATEST(1, COALESCE(p_val, p_default));
$$ LANGUAGE sql IMMUTABLE;

SELECT * FROM orders LIMIT safe_limit(-5);    -- LIMIT 1
SELECT * FROM orders LIMIT safe_limit(NULL);  -- LIMIT 10
SELECT * FROM orders LIMIT safe_limit(25);    -- LIMIT 25

---

Prevention Tips

Validate at the database layer, not just the application layer.
Always use GREATEST(0, COALESCE(input, default)) around any externally supplied LIMIT value inside your functions. Never trust that the application layer has already validated the input.

Add boundary-value tests to your CI pipeline.
Include test cases for LIMIT 0, LIMIT -1, and LIMIT NULL in your integration test suite. Also monitor your PostgreSQL logs for SQLSTATE 2201W and set up alerting so you catch regressions immediately in production.

---

Related Errors

• 2201X — invalid_row_count_in_result_offset_clause: The OFFSET equivalent of 2201W. If you're fixing 2201W in a pagination query, always guard the OFFSET value too.
• 22003 — numeric_value_out_of_range: Broader numeric range violation in the same Class 22 (Data Exception) family.
• 42601 — syntax_error: May appear instead of 2201W when the LIMIT value is malformed at parse time rather than execution time.

---

📖 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.