You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
OverflowError raised when JSONB column receives Python dict with int values > 2^31-1 #163
psqlpy raises OverflowError: out of range integral type conversion attempted when a Python dict containing integer values larger than int32 (2,147,483,647) is passed to a jsonb column, or wrapped via psqlpy.extra_types.JSONB. PostgreSQL jsonb itself imposes no such limit, and psqlpy already supports int64 for bigint columns via BigInt(...).
Reproduction
importasynciofrompsqlpyimportConnectionPoolfrompsqlpy.extra_typesimportJSONBDSN="postgresql://user:pw@host:5432/db"asyncdefmain():
pool=ConnectionPool(dsn=DSN, max_db_pool_size=2)
asyncwithpool.acquire() asconn:
awaitconn.execute("CREATE TEMP TABLE t (data jsonb)")
# OK — int32 maxawaitconn.execute("INSERT INTO t VALUES ($1)", [{"id": 2147483647}])
# FAIL — int32 max + 1awaitconn.execute("INSERT INTO t VALUES ($1)", [{"id": 2147483648}])
# OverflowError: out of range integral type conversion attempted# Also fails inside JSONB() wrapper before execute even runs:JSONB({"id": 2147483648})
# OverflowError: out of range integral type conversion attemptedasyncio.run(main())
Tested with all of the following — same OverflowError every time:
PostgreSQL jsonb stores numbers as arbitrary-precision text; any int64 (or larger) value should round-trip without error. psqlpy already proves it can marshal int64 correctly when targeting a bigint column with BigInt(...), so the same range should be supported inside nested jsonb payloads.
Actual
OverflowError is raised at the Python→Rust boundary, suggesting the dict → jsonb marshalling path defaults to i32 instead of i64 (or arbitrary precision).
Real-world impact
Hit this in production while inserting LLM / tool-call results into a jsonb audit-log table. The tool returned a vendor-issued order_id = 36704707556755 (well within int64, normal for snowflake / timestamp-based IDs), and the insert silently failed via a try/except that only logged str(e) — which turned into an undetected data-loss bug for hours until upstream consumers timed out.
Workaround
Pass the JSON as text and cast in SQL:
awaitconn.execute(
"INSERT INTO t (data) VALUES ($1::text::jsonb)",
[json.dumps(payload)], # any int size now safe
)
This bypasses psqlpy's dict marshalling and lets PostgreSQL parse the jsonb itself.
Suggested fix
Treat integers inside dict → jsonb (and inside JSONB(...)) as i64 by default, or fall back to arbitrary-precision (numeric) when the value exceeds i64. Python int is unbounded, so any silent narrowing is surprising.
Activity