LIMIT must not be negative

This error occurs when you provide a negative value for LIMIT or OFFSET in a query.

The LIMIT must not be negative error means you've passed an invalid value to LIMIT or OFFSET.

Understanding the Error

ERROR: LIMIT must not be negative
ERROR: OFFSET must not be negative

LIMIT and OFFSET require non-negative integer values.

Common Causes

1. Negative LIMIT Value

SQL

SELECT * FROM users LIMIT -5;  -- Error!

2. Negative OFFSET Value

SQL

SELECT * FROM users LIMIT 10 OFFSET -1;  -- Error!

3. Application Bug

JAVASCRIPT

// page might be 0, resulting in negative offset
const offset = (page - 1) * pageSize;  // -10 if page is 0!

4. Bad User Input

User-provided pagination parameters not validated.

How to Fix It

Solution 1: Use Valid Values

SQL

SELECT * FROM users LIMIT 10;        -- Valid
SELECT * FROM users LIMIT 10 OFFSET 0;  -- Valid

Solution 2: Validate in Application

JAVASCRIPT

function getUsers(page = 1, pageSize = 10) {
  // Ensure positive values
  const safePage = Math.max(1, parseInt(page) || 1);
  const safePageSize = Math.min(100, Math.max(1, parseInt(pageSize) || 10));
  const offset = (safePage - 1) * safePageSize;

  return db.query(
    'SELECT * FROM users LIMIT $1 OFFSET $2',
    [safePageSize, offset]
  );
}

Solution 3: Use GREATEST Function

SQL

SELECT * FROM users
LIMIT GREATEST($1, 0)
OFFSET GREATEST($2, 0);

Solution 4: Use CASE Expression

SQL

SELECT * FROM users
LIMIT CASE WHEN $1 < 0 THEN 0 ELSE $1 END
OFFSET CASE WHEN $2 < 0 THEN 0 ELSE $2 END;

Solution 5: Default Values in Query

SQL

-- Use COALESCE and GREATEST for safe defaults
SELECT * FROM users
LIMIT GREATEST(COALESCE($1, 10), 1)
OFFSET GREATEST(COALESCE($2, 0), 0);

Pagination Best Practices

JAVASCRIPT

// Complete pagination example
async function paginatedQuery(tableName, page, pageSize) {
  // Validate inputs
  const validPage = Math.max(1, parseInt(page) || 1);
  const validSize = Math.min(100, Math.max(1, parseInt(pageSize) || 20));

  const offset = (validPage - 1) * validSize;

  // Get total count
  const countResult = await db.query(
    `SELECT COUNT(*) FROM ${tableName}`
  );
  const total = parseInt(countResult.rows[0].count);

  // Get page data
  const dataResult = await db.query(
    `SELECT * FROM ${tableName} LIMIT $1 OFFSET $2`,
    [validSize, offset]
  );

  return {
    data: dataResult.rows,
    pagination: {
      page: validPage,
      pageSize: validSize,
      total,
      totalPages: Math.ceil(total / validSize),
    },
  };
}

Best Practices

Always validate user-provided pagination parameters
Set maximum limits to prevent huge queries
Use keyset pagination for large datasets (more efficient than OFFSET)
Default to reasonable values for missing parameters
Handle edge cases (page 0, negative values)