sqlx: Rust type i64 is not compatible with SQL type NUMERIC

A SUM() over a BIGINT column comes back as NUMERIC in PostgreSQL, and sqlx refuses to decode that into an i64 field. Casting the sum to BIGINT in the query fixes it.

Errors or symptoms
error occurred while decoding column "total_visits": mismatched types; Rust type `i64` (as SQL type `INT8`) is not compatible with SQL type `NUMERIC`summary figures read 0 while the table underneath has real rows
Affects
sqlx 0.8.6 · PostgreSQL
Checked
with sqlx 0.8.6, PostgreSQL 17.6
Depth
one layer down
Tags
rust · sqlx · postgresql · database

What broke

The admin visitors page on my site has a row of summary cards above the table: unique IPs, total visits and a few counts by category. One day every card read 0, while the table underneath had real rows in it. The handler catches a failed summary query and falls back to all zeros, so the page gave no hint of why.

The error, from sqlx's source

I had no log line to copy, because the handler only writes the error to the tracing log. So I traced the wording through sqlx-core 0.8.6, the version this site pins in Cargo.lock.

Row::try_get checks the column's PostgreSQL type against what the target Rust type expects before it tries to decode anything. For i64 that expected type is INT8, and a NUMERIC column fails the check:

if !ty.is_null() && !T::compatible(&ty) {
    return Err(Error::ColumnDecode {
        index: format!("{index:?}"),
        source: mismatched_types::<Self::Database, T>(&ty),
    });
}

mismatched_types builds the message, and ColumnDecode wraps it with the column name. Put together for the total_visits column, the full line reads:

error occurred while decoding column "total_visits": mismatched types; Rust type `i64` (as SQL type `INT8`) is not compatible with SQL type `NUMERIC`

One bad column fails the whole row, so none of the other counts reached the page either, even though they were fine.

Why PostgreSQL hands back a different type

The visit_count column is BIGINT. In PostgreSQL, sum(smallint) and sum(integer) return bigint, but sum(bigint) returns numeric. A total over a bigint column can grow past what a bigint holds, so PostgreSQL gives it a wider type. I checked it on PostgreSQL 17.6:

SELECT pg_typeof(sum(x)) FROM (VALUES (1::bigint)) t(x);  -- numeric
SELECT pg_typeof(sum(x)) FROM (VALUES (1::int)) t(x);     -- bigint

sqlx maps i64 to INT8 and has no path from NUMERIC to i64, so the sum needs a cast before an i64 field can read it.

The fix

Cast the sum back to BIGINT inside the query, in src/db/visitor_queries.rs:

COALESCE(SUM(visit_count), 0)::BIGINT AS total_visits,

The cast runs in PostgreSQL before the row reaches sqlx, so the column sqlx sees is BIGINT and the check passes. If a total ever did pass the bigint range, the cast would stop with ERROR: bigint out of range rather than return a wrong number. A visit counter on a personal site is nowhere near that.

Other aggregates

AVG() returns numeric for every integer input, integer included. I checked that on 17.6 too. When a query has an aggregate and the Rust field is an integer, I think it pays to look up what PostgreSQL says the aggregate returns. It isn't always the column's own type.

Back to all guides