PostgreSQL 2201X Error: Causes and Solutions Complete Guide

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

PostgreSQL error code 2201X is thrown when the OFFSET clause in a SQL query receives an invalid value — most commonly a negative number, NULL, or a non-integer type. The OFFSET clause is designed to skip a specified number of rows in a result set, and PostgreSQL strictly requires this value to be a non-negative integer. This error most frequently surfaces in pagination logic, dynamic query generation, or when application-layer parameter handling is insufficient.

---

Top 3 Causes

1. Negative OFFSET Value

The most common cause is a miscalculated page offset in pagination logic. When a page number is 0 or goes negative due to a calculation bug, the resulting OFFSET becomes negative, which PostgreSQL will immediately reject.

-- ERROR: OFFSET must not be negative
SELECT * FROM orders
ORDER BY created_at DESC
OFFSET -10 LIMIT 20;

-- FIX: Use GREATEST() to enforce a minimum of 0
SELECT * FROM orders
ORDER BY created_at DESC
OFFSET GREATEST(0, -10) LIMIT 20;
-- Result: runs safely with OFFSET 0

2. NULL Passed to the OFFSET Clause

When using parameter binding in application code or stored procedures, an uninitialized or explicitly NULL offset parameter will trigger this error. This is especially common with ORM frameworks that auto-generate queries without validating pagination parameters.

-- ERROR: NULL causes invalid row count
DO $$
DECLARE
  v_offset INTEGER := NULL;
BEGIN
  EXECUTE format(
    'SELECT * FROM orders OFFSET %s LIMIT 20',
    v_offset
  );
END;
$$;

-- FIX: Use COALESCE() to default NULL to 0
DO $$
DECLARE
  v_offset INTEGER := NULL;
BEGIN
  EXECUTE format(
    'SELECT * FROM orders OFFSET %s LIMIT 20',
    COALESCE(v_offset, 0)
  );
END;
$$;

3. Non-Integer or Float Value Passed as OFFSET

Some languages return floating-point results from page calculations (e.g., (page - 1) * size in Python can yield a float). Passing a float or non-castable string directly into the OFFSET clause will cause this error, since PostgreSQL only accepts integer types.

-- ERROR: float value in OFFSET
SELECT * FROM products
ORDER BY price
OFFSET 10.5 LIMIT 10;

-- ERROR: non-numeric string
SELECT * FROM products
ORDER BY price
OFFSET 'ten' LIMIT 10;

-- FIX: Explicitly cast to integer
SELECT * FROM products
ORDER BY price
OFFSET FLOOR(10.9)::BIGINT LIMIT 10;
-- Result: OFFSET 10, runs cleanly

---

Quick Fix Solutions

Combine GREATEST(), COALESCE(), and explicit casting for a bulletproof pagination query:

-- Safe pagination pattern
SELECT *
FROM orders
ORDER BY created_at DESC
OFFSET GREATEST(0, COALESCE($1, 0)::BIGINT)
LIMIT  GREATEST(1, COALESCE($2, 20)::BIGINT);

For stored procedures, add explicit input validation:

CREATE OR REPLACE FUNCTION get_orders_page(
  p_page      INTEGER DEFAULT 1,
  p_page_size INTEGER DEFAULT 20
)
RETURNS SETOF orders AS $$
BEGIN
  IF p_page < 1    THEN p_page      := 1;  END IF;
  IF p_page_size < 1 THEN p_page_size := 20; END IF;

  RETURN QUERY
  SELECT *
  FROM orders
  ORDER BY created_at DESC
  OFFSET ((p_page - 1) * p_page_size)
  LIMIT  p_page_size;
END;
$$ LANGUAGE plpgsql;

---

Prevention Tips

1. Always validate and sanitize pagination parameters at the application layer. Never pass user-supplied values directly into OFFSET without confirming they are non-negative integers. Use parameterized queries ($1, $2) rather than string interpolation to prevent both this error and SQL injection risks.

2. Consider switching to Keyset (Cursor-based) Pagination. For large datasets, OFFSET-based pagination is both slow and error-prone. Keyset pagination eliminates OFFSET entirely by filtering on the last seen record's unique key, making it faster and immune to this class of errors.
   

-- Keyset pagination: no OFFSET needed
-- First page
SELECT id, created_at, title
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: use last record's values as the cursor
SELECT id, created_at, title
FROM articles
WHERE (created_at, id) < ('2024-06-01 12:00:00', 9876)
ORDER BY created_at DESC, id DESC
LIMIT 20;

---

Related Errors

Code	Name	Description
2201W	invalid row count in limit clause	Same issue, but in the LIMIT clause instead of OFFSET
22003	numeric_value_out_of_range	OFFSET value exceeds the bigint range
22012	division_by_zero	Can occur when computing the OFFSET value in a formula

---

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