Incident brief
LIMIT must not be negative
A query passed a negative value to LIMIT. PostgreSQL requires LIMIT to be non-negative; use LIMIT ALL or NULL to mean “no limit”.
In 10 seconds
- What
- LIMIT must not be negative
- What triggers it
- Run a query whose LIMIT clause evaluates to a negative number.
- The fix
- Use LIMIT ALL, or a NULL LIMIT, when you mean “return everything”.
- Proof
- Reproduced on PostgreSQL 16.14 → A query with LIMIT -1 was rejected with SQLSTATE 2201W. Replacing it with a NULL limit (via NULLIF) returned all five rows.
The fix
What to do right now
The immediate, application-level response to this error.
- Use LIMIT ALL, or a NULL LIMIT, when you mean “return everything”.
- Clamp a computed limit with GREATEST(n, 0) so pagination math can never send a negative.
- Validate page-size input before it reaches the query; reject or floor negatives.
-- LIMIT ALL / NULL means "no limit"; never pass a negative
SELECT g FROM generate_series(1, 5) AS g ORDER BY g LIMIT NULLIF(-1, -1);Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §7.6 LIMIT and OFFSET
If a limit count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). LIMIT ALL is the same as omitting the LIMIT clause, as is LIMIT with a NULL argument.Read the full section on postgresql.org →
The negative LIMIT
LIMIT counts rows to return, so a negative value is meaningless. PostgreSQL raises SQLSTATE 2201W rather than guessing what -1 should mean.The session is unaffected by the error
The rejected query never ran; the source still has all five rows and the session is unaffected.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Run a query whose LIMIT clause evaluates to a negative number.
- 2PostgreSQL raises SQLSTATE 2201W and returns no rows.
- 3Outside a transaction, the session is unaffected — the next statement runs normally.
One client, no schema: generate_series feeds five rows to a LIMIT clause that evaluates to -1.
-- LIMIT parsing is self-contained; generate_series supplies the rows.
SELECT 'no schema needed' AS setup_note;SELECT g FROM generate_series(1, 5) AS g ORDER BY g LIMIT -1;SELECT count(*) AS rows_available FROM generate_series(1, 5) AS g;What PostgreSQL actually returned
setup_note
------------------
no schema needed
(1 row)ERROR: LIMIT must not be negative rows_available
----------------
5
(1 row)Replacing the negative LIMIT with a NULL limit (LIMIT ALL semantics) was tested and returned every row, matching the manual's statement that a NULL LIMIT means no limit.
Run the same query, but turn the -1 into NULL so it behaves like LIMIT ALL.
Without this
Above: LIMIT -1 raises SQLSTATE 2201W.
With this, tested
Below: a NULL LIMIT returns all rows.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Related errors
Glossary
Verification
- Last verified
- 2026-07-19 (isolated lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed