PostgreSQL 2201X Error: Causes and Solutions Complete Guide

PostgreSQL Error 2201X: invalid row count in result offset clause

PostgreSQL error code 2201X (invalid_row_count_in_result_offset_clause) is thrown when the value provided to an OFFSET clause is not a valid non-negative integer. This commonly surfaces in applications that implement pagination using dynamic queries or user-supplied parameters, where the offset value may be negative, NULL, or a non-integer type.

---

Top 3 Causes

1. Negative OFFSET Value

The most frequent cause is a miscalculated page offset. When computing (page - 1) * page_size, a bad page number can produce a negative result.

-- Triggers error 2201X
SELECT *
FROM orders
ORDER BY created_at DESC
OFFSET -5 LIMIT 20;
-- ERROR:  invalid row count in result offset clause

-- Fix: Use GREATEST to clamp the value to 0
SELECT *
FROM orders
ORDER BY created_at DESC
OFFSET GREATEST(0, -5) LIMIT 20;

2. NULL Passed as OFFSET

When an application fails to initialize or bind the offset parameter, NULL gets passed to the query. This often happens with ORMs or query builders where optional parameters are not explicitly set.

-- Triggers error 2201X
SELECT *
FROM products
ORDER BY id
OFFSET NULL LIMIT 10;
-- ERROR:  invalid row count in result offset clause

-- Fix: Use COALESCE to provide a default value
SELECT *
FROM products
ORDER BY id
OFFSET COALESCE(NULL, 0) LIMIT 10;

-- Parameterized query with safe fallback
SELECT *
FROM products
ORDER BY id
OFFSET COALESCE($1::BIGINT, 0) LIMIT COALESCE($2::BIGINT, 10);

3. Non-Integer Type (Float or String)

Passing a float or a string that cannot be cleanly cast to a whole number causes this error. This typically happens when Python float values or unvalidated user input strings are interpolated directly into a query.

-- Triggers error 2201X
SELECT *
FROM employees
ORDER BY last_name
OFFSET 10.7 LIMIT 5;
-- ERROR:  invalid row count in result offset clause

-- Fix: Use FLOOR and explicit cast to BIGINT
SELECT *
FROM employees
ORDER BY last_name
OFFSET FLOOR(10.7)::BIGINT LIMIT 5;

---

Quick Fix Solutions

Wrap all three defenses into a single reusable function:

CREATE OR REPLACE FUNCTION safe_paginate(
    p_page      INTEGER DEFAULT 1,
    p_page_size INTEGER DEFAULT 20
)
RETURNS TABLE (
    id         BIGINT,
    name       TEXT,
    created_at TIMESTAMPTZ
) AS $$
DECLARE
    v_limit  BIGINT;
    v_offset BIGINT;
BEGIN
    -- Normalize inputs: clamp page_size between 1 and 100, default page to 1
    v_limit  := GREATEST(1, LEAST(COALESCE(p_page_size, 20), 100));
    v_offset := GREATEST(0, (COALESCE(p_page, 1) - 1) * v_limit);

    RETURN QUERY
    SELECT p.id, p.name, p.created_at
    FROM products p
    ORDER BY p.created_at DESC
    LIMIT v_limit
    OFFSET v_offset;
END;
$$ LANGUAGE plpgsql;

-- Safe even with bad inputs
SELECT * FROM safe_paginate(-1, 0);
SELECT * FROM safe_paginate(NULL, NULL);

---

Prevention Tips

1. Validate before the query reaches PostgreSQL. Always sanitize pagination parameters at the application layer. Ensure page >= 1, page_size >= 1, and neither is NULL before constructing or executing any query. A shared utility function across your codebase prevents repeated mistakes.

2. Consider Keyset Pagination over OFFSET. For large datasets, replace OFFSET entirely with cursor-based (keyset) pagination. This not only eliminates the risk of 2201X but also delivers significantly better performance at scale.
   

-- Keyset pagination: no OFFSET needed
SELECT id, name, created_at
FROM products
WHERE created_at < $1          -- cursor from last fetched row
ORDER BY created_at DESC
LIMIT 20;

---

Related Errors

Code	Name	Description
2201W	invalid_row_count_in_limit_clause	Same class of error but for the LIMIT clause — always validate both.
22003	numeric_value_out_of_range	Can occur when an astronomically large integer overflows BIGINT in the OFFSET position.
22012	division_by_zero	May appear alongside 2201X if offset computation involves division by a zero page size.

---

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