Incident brief
Numeric value out of range
A value didn't fit in the numeric type's allowed range. PostgreSQL rejects it instead of wrapping it around or silently rounding it.
In 10 seconds
- What
- Numeric value out of range
- What triggers it
- Create a table with a smallint column.
- The fix
- Use a wider integer type (integer or bigint) if values can legitimately exceed smallint's range.
- Proof
- Reproduced on PostgreSQL 16.14 → Inserting 40000 into a smallint column (documented range -32768 to +32767) was rejected with SQLSTATE 22003. No row was stored.
The fix
What to do right now
The immediate, application-level response to this error.
- Use a wider integer type (integer or bigint) if values can legitimately exceed smallint's range.
- Validate expected value ranges in the application before inserting.
- Remember arithmetic on in-range values can still overflow the result type — see the premium counterexample below.
-- widen the type so a legitimately larger value fits
ALTER TABLE metrics.counters ALTER COLUMN count TYPE integer;
INSERT INTO metrics.counters (id, count) VALUES (5, 40000);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 — §8.1.1 Integer Types
Attempts to store values outside of the allowed range will result in an error.Read the full section on postgresql.org →
The insert that's out of range
40000 is past smallint's documented maximum of 32767, so PostgreSQL rejected the insert immediately.Checking the table afterward
The table is empty. The rejected insert left nothing behind, not even a clamped value.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.
- 1Create a table with a smallint column.
- 2Insert a value outside smallint's documented range (-32768 to +32767).
- 3PostgreSQL rejects it with SQLSTATE 22003.
One client: create a smallint column, insert a value past its documented range.
DROP TABLE IF EXISTS metrics.counters;
CREATE TABLE metrics.counters (
id integer primary key,
count smallint
);INSERT INTO metrics.counters (id, count) VALUES (1, 40000);SELECT * FROM metrics.counters;What PostgreSQL actually returned
DROP TABLE
CREATE TABLEERROR: smallint out of range id | count
----+-------
(0 rows)The documented boundary was tested at both ends: 32767 and -32768 are proven to be accepted exactly, and 32768 — one past the upper boundary — is proven to be rejected.
Insert the exact upper and lower documented boundary values, then insert one value past the upper boundary.
Without this
Above: 40000 (well past the range) is rejected.
With this, tested
Below: the exact boundary values 32767 and -32768 succeed; 32768 — one past the boundary — fails.
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
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed