The PostgreSQL error 2201W "Invalid row count in LIMIT clause" occurs when a non-integer, negative, or otherwise invalid value is passed to the LIMIT clause in a SQL query. LIMIT expects a non-negative integer or NULL, and providing strings, floating-point numbers, negative values, or other invalid types triggers this error. Fixing requires ensuring the LIMIT value is properly validated and cast to an integer in your application code.
PostgreSQL raises SQLSTATE 2201W when the query parser encounters an invalid value in the LIMIT clause. The LIMIT clause is used to restrict the maximum number of rows returned by a query. PostgreSQL requires the LIMIT value to be a non-negative integer (or NULL to indicate no limit). When a LIMIT clause receives: - A string value that cannot be converted to an integer - A negative integer - A floating-point number - A non-numeric value from a variable or parameter - An explicit type that is not compatible with an integer PostgreSQL cannot process the query and returns this error. This is distinct from other row count errors (like 2201X for OFFSET); 2201W specifically applies to the LIMIT clause.
The most direct fix is to explicitly cast the LIMIT parameter to an integer using the :: operator.
-- INCORRECT: LIMIT expects an integer
SELECT * FROM users LIMIT $1;
-- If $1 is a string like "10", error occurs: 2201W
-- CORRECT: Cast to integer
SELECT * FROM users LIMIT $1::integer;
SELECT * FROM users LIMIT $1::int;
-- Also works with OFFSET
SELECT * FROM users LIMIT $1::integer OFFSET $2::integer;
-- For hardcoded values
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT CAST(10 AS integer);This is the most reliable approach because PostgreSQL performs the type conversion at the database level, ensuring type safety.
Convert user input to integers before passing to the database query.
// Node.js + pg library
const { Client } = require('pg');
const client = new Client();
// INCORRECT: Raw user input to LIMIT
const pageSize = req.query.limit; // "10" from URL
await client.query('SELECT * FROM users LIMIT $1', [pageSize]);
// ERROR: 2201W if pageSize is a string
// CORRECT: Parse and validate to integer
let pageSize = parseInt(req.query.limit, 10);
if (isNaN(pageSize) || pageSize < 1) {
pageSize = 10; // Default value
}
if (pageSize > 100) {
pageSize = 100; // Max limit
}
await client.query('SELECT * FROM users LIMIT $1', [pageSize]);# Python + psycopg2
import psycopg2
page_size = request.args.get('limit', '10')
# INCORRECT: String passed to LIMIT
cur.execute("SELECT * FROM users LIMIT %s", (page_size,))
# CORRECT: Convert to integer and validate
try:
page_size = int(page_size)
if page_size < 1 or page_size > 100:
page_size = 10
except ValueError:
page_size = 10
cur.execute("SELECT * FROM users LIMIT %s", (page_size,))// Java + JDBC
String limitStr = request.getParameter("limit");
int limit = 10; // default
try {
limit = Integer.parseInt(limitStr);
if (limit < 1 || limit > 100) {
limit = 10;
}
} catch (NumberFormatException e) {
limit = 10;
}
PreparedStatement pst = connection.prepareStatement(
"SELECT * FROM users LIMIT ?"
);
pst.setInt(1, limit);
ResultSet rs = pst.executeQuery();Verify that LIMIT values are always greater than zero. Negative numbers cause this error.
-- INCORRECT: Negative LIMIT values are invalid
SELECT * FROM users LIMIT -5;
-- ERROR: 2201W: invalid_row_count_in_limit_clause
SELECT * FROM users LIMIT $1::integer;
-- If $1 = -10, same error
-- CORRECT: Use positive integers only
SELECT * FROM users LIMIT 5;
SELECT * FROM users LIMIT GREATEST(1, $1::integer);
-- Apply minimum/maximum bounds
SELECT * FROM users LIMIT LEAST(100, GREATEST(1, $1::integer));
-- This enforces: 1 <= LIMIT <= 100In application code:
let limit = parseInt(userInput, 10);
// Enforce minimum (cannot be 0 or negative)
if (limit < 1) {
limit = 1;
}
// Enforce maximum (prevent abuse)
if (limit > 100) {
limit = 100;
}
await query('SELECT * FROM users LIMIT $1::integer', [limit]);Ensure your database driver correctly passes integer types, not strings.
// Node.js + pg with explicit integer casting
const { Client } = require('pg');
const client = new Client();
// INCORRECT: No type information
const result = await client.query(
'SELECT * FROM users LIMIT $1 OFFSET $2',
[pageSize, offset] // May be strings if from user input
);
// CORRECT: With explicit SQL casting
const result = await client.query(
'SELECT * FROM users LIMIT $1::integer OFFSET $2::integer',
[pageSize, offset]
);
// Or validate in code before sending
const validPageSize = Math.max(1, Math.min(100, parseInt(pageSize, 10)));
const validOffset = Math.max(0, parseInt(offset, 10));
const result = await client.query(
'SELECT * FROM users LIMIT $1 OFFSET $2',
[validPageSize, validOffset]
);# Python + psycopg2 with proper conversion
import psycopg2
from psycopg2 import sql
# INCORRECT: Passing string directly
cur.execute("SELECT * FROM users LIMIT %s", (request.args['limit'],))
# CORRECT: Parse and validate first
limit = int(request.args.get('limit', 10))
limit = max(1, min(100, limit)) # Enforce bounds
cur.execute("SELECT * FROM users LIMIT %s", (limit,))While this error specifically references LIMIT, OFFSET has similar requirements and causes related error 2201X.
-- INCORRECT: Negative or non-integer OFFSET
SELECT * FROM users LIMIT 10 OFFSET -5;
-- ERROR: 2201X (same family as 2201W)
SELECT * FROM users LIMIT $1::integer OFFSET $2::string;
-- ERROR: 2201W or 2201X
-- CORRECT: Both LIMIT and OFFSET are non-negative integers
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users LIMIT $1::integer OFFSET GREATEST(0, $2::integer);Always cast both LIMIT and OFFSET:
const limit = Math.max(1, Math.min(100, parseInt(req.query.limit, 10)));
const offset = Math.max(0, parseInt(req.query.offset, 10));
await client.query(
'SELECT * FROM users LIMIT $1 OFFSET $2',
[limit, offset]
);Debug by logging the exact value being sent to the LIMIT clause.
// Node.js
const pageSize = req.query.limit;
console.log(`LIMIT value: ${pageSize}, type: ${typeof pageSize}`);
// Add error handling
try {
const result = await client.query(
'SELECT * FROM users LIMIT $1::integer',
[pageSize]
);
} catch (err) {
console.error('Query failed:', err.message);
console.error('LIMIT value was:', pageSize);
console.error('Parsed as:', parseInt(pageSize, 10));
}# Python
page_size = request.args.get('limit')
logger.info(f"LIMIT value: {page_size}, type: {type(page_size)}")
try:
cur.execute("SELECT * FROM users LIMIT %s", (page_size,))
except psycopg2.Error as e:
logger.error(f"Query error: {e}")
logger.error(f"LIMIT was: {page_size}")Enable PostgreSQL query logging to see the exact values:
-- In postgresql.conf
log_statement = 'all'
log_min_duration_statement = 0
-- Or at session level
SET log_min_duration_statement = 0;Then check /var/log/postgresql/ (on Linux) for detailed query logs.
Different ORMs handle LIMIT differently; ensure proper type conversion.
// Sequelize (Node.js)
// INCORRECT: String limit
const users = await User.findAll({ limit: "10" });
// May cause 2201W
// CORRECT: Integer limit
const pageSize = Math.max(1, Math.min(100, parseInt(req.query.limit, 10)));
const offset = Math.max(0, parseInt(req.query.offset, 10));
const users = await User.findAll({
limit: pageSize,
offset: offset
});# Django ORM
# INCORRECT: String slicing
page_size = request.GET.get('limit')
users = User.objects.all()[:page_size] # String causes error
# CORRECT: Integer slicing
page_size = int(request.GET.get('limit', 10))
users = User.objects.all()[:page_size]
# Or use Django pagination
from django.core.paginator import Paginator
paginator = Paginator(User.objects.all(), 10)
page = paginator.get_page(request.GET.get('page', 1))// TypeORM (Node.js)
// INCORRECT: String limit
const users = await userRepo.find({ take: "10" });
// CORRECT: Integer limit
const limit = Math.max(1, Math.min(100, parseInt(req.query.limit, 10)));
const offset = Math.max(0, parseInt(req.query.offset, 10));
const users = await userRepo.find({
take: limit,
skip: offset
});Write unit tests to verify pagination handles invalid LIMIT values gracefully.
// Jest test example
describe('Pagination', () => {
test('should handle string LIMIT value', async () => {
const result = await queryUsers({ limit: "10" });
expect(result.success).toBe(true);
expect(result.data.length).toBeLessThanOrEqual(10);
});
test('should reject negative LIMIT', async () => {
const result = await queryUsers({ limit: "-5" });
// Should default to safe value, not throw error
expect(result.success).toBe(true);
});
test('should cap maximum LIMIT', async () => {
const result = await queryUsers({ limit: "10000" });
expect(result.data.length).toBeLessThanOrEqual(100); // Max enforced
});
test('should handle non-numeric LIMIT', async () => {
const result = await queryUsers({ limit: "abc" });
expect(result.success).toBe(true);
expect(result.data.length).toBeLessThanOrEqual(10); // Default applied
});
test('should handle NULL or missing LIMIT', async () => {
const result = await queryUsers({ limit: null });
expect(result.success).toBe(true);
});
});# Python unittest example
class PaginationTestCase(TestCase):
def test_string_limit_value(self):
response = self.client.get('/api/users/?limit=10')
self.assertEqual(response.status_code, 200)
def test_negative_limit(self):
response = self.client.get('/api/users/?limit=-5')
self.assertEqual(response.status_code, 200) # Should not error
def test_max_limit_enforced(self):
response = self.client.get('/api/users/?limit=10000')
users = response.json()
self.assertLessEqual(len(users), 100) # Max capThe SQLSTATE 2201W error is part of PostgreSQL's class 22 (Data Exception) errors that indicate a problem with the structure or semantics of the data supplied in a SQL statement. Unlike type casting errors that occur at runtime, 2201W is raised during query planning when PostgreSQL validates the LIMIT clause.
PostgreSQL requires LIMIT to be a non-negative integer literal, a parameter cast to integer (with ::integer or ::int4), or a function that returns an integer. Trying to pass a string, floating-point, or other non-integer type to LIMIT will fail at the parsing stage.
Related errors in the same family:
- 2201X: invalid_row_count_in_result_offset_clause (same issue with OFFSET)
- 22003: numeric_value_out_of_range (if LIMIT value is too large)
- 42804: datatype_mismatch (if wrong type is used in a function with LIMIT)
Performance note: For pagination in large tables, always use OFFSET carefully:
- LIMIT 10 OFFSET 1000000 scans 1,000,010 rows and discards the first 1,000,000
- For better performance with large offsets, consider cursor-based pagination or keyset pagination (WHERE id > last_id)
- Combine LIMIT with ORDER BY to ensure consistent pagination:
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
PostgreSQL also supports shorthand for LIMIT without OFFSET:
SELECT * FROM users FETCH FIRST 10 ROWS ONLY; -- SQL standard syntax
SELECT * FROM users LIMIT 10; -- PostgreSQL shorthand (equivalent)
Both handle integer type checking the same way, so the same fixes apply.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL