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

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