How to fix 2201X: invalid_row_count_in_result_offset_clause in PostgreSQL

-- This will fail: negative OFFSET
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET -10;

-- This is correct: non-negative OFFSET
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;  -- First page
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- Second page

// Bad: Can produce negative offset
const offset = (page - 1) * pageSize;  // If page = 0, offset = -pageSize

// Good: Ensure non-negative offset
const offset = Math.max(0, (page - 1) * pageSize);

-- Check total rows before calculating OFFSET
SELECT COUNT(*) as total_rows FROM users;

-- Use conditional logic for large offsets
WITH total AS (
  SELECT COUNT(*) as count FROM users
)
SELECT *
FROM users
CROSS JOIN total
WHERE
  CASE
    WHEN 1000 > total.count THEN FALSE  -- Offset exceeds rows
    ELSE TRUE
  END
ORDER BY id
LIMIT 10
OFFSET 1000;

// Before executing query
const totalRows = await getTotalCount();
const maxOffset = Math.max(0, totalRows - pageSize);
const safeOffset = Math.min(offset, maxOffset);

-- These will fail: non-integer OFFSET
SELECT * FROM users OFFSET 5.5;
SELECT * FROM users OFFSET '10';
SELECT * FROM users OFFSET some_float_column;

-- These are correct: integer OFFSET
SELECT * FROM users OFFSET 5;
SELECT * FROM users OFFSET CAST(5.5 AS INTEGER);  -- Explicit cast
SELECT * FROM users OFFSET FLOOR(some_float_column);  -- Convert to integer

-- Using parameterized queries with proper types
-- In application code:
const offset = parseInt(req.query.offset, 10) || 0;
const query = 'SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2';
const values = [pageSize, offset];

function safeOffset(offset) {
  const num = Number(offset);
  if (isNaN(num) || !Number.isInteger(num) || num < 0) {
    return 0;  // Default to first page
  }
  return num;
}

-- Using COALESCE to handle NULL OFFSET
SELECT * FROM users
ORDER BY id
LIMIT 10
OFFSET COALESCE(NULL, 0);  -- Defaults to 0

-- Using CASE to validate OFFSET
SELECT * FROM users
ORDER BY id
LIMIT 10
OFFSET CASE
  WHEN $1 < 0 THEN 0
  ELSE $1
END;

-- For dynamic OFFSET from columns, add validation
SELECT * FROM users
ORDER BY id
LIMIT 10
OFFSET GREATEST(0, COALESCE(offset_column, 0));

// Express.js example
app.get('/users', async (req, res) => {
  const page = parseInt(req.query.page, 10) || 1;
  const pageSize = parseInt(req.query.pageSize, 10) || 10;
  const offset = Math.max(0, (page - 1) * pageSize);

  // Execute query with validated offset
  const users = await db.query(
    'SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2',
    [pageSize, offset]
  );

  res.json(users);
});

-- Instead of OFFSET, use WHERE clause with last seen ID
SELECT * FROM users
WHERE id > last_seen_id  -- More efficient than OFFSET
ORDER BY id
LIMIT 10;

-- For multi-column ordering
SELECT * FROM users
WHERE (created_at, id) > (last_created_at, last_id)
ORDER BY created_at, id
LIMIT 10;

WITH numbered AS (
  SELECT *,
    ROW_NUMBER() OVER (ORDER BY id) as rn
  FROM users
)
SELECT * FROM numbered
WHERE rn BETWEEN 11 AND 20;  -- Equivalent to OFFSET 10 LIMIT 10

-- First query
SELECT * FROM users ORDER BY id LIMIT 10;
-- Returns last id: 15

-- Next query using cursor
SELECT * FROM users
WHERE id > 15  -- Use last ID as cursor
ORDER BY id
LIMIT 10;

-- Alternative to LIMIT/OFFSET syntax
SELECT * FROM users
ORDER BY id
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;