How to get average of a column using rust diesel?-CodePudding

I am trying to get the average value of a column using rust diesel but am stuck with the type error.

Error:

the trait bound `f64: FromSql<Numeric, _>` is not satisfied
the following other types implement trait `FromSql<A, DB>`:
  <f32 as FromSql<diesel::sql_types::Float, DB>>
  <f64 as FromSql<Double, DB>>
  <i16 as FromSql<SmallInt, DB>>
  <i32 as FromSql<Integer, DB>>
  <i64 as FromSql<BigInt, DB>>
  <u32 as FromSql<Oid, Pg>>
required because of the requirements on the impl of `diesel::Queryable<Numeric, _>` for `f64`

Code:

   let new_avg: Option<f64> = fruits
                .select(avg(weight))
                .filter(fruit_name.eq(&fruit_name))
                .get_result::<Option<f64>>(&self.postgres.get().unwrap())
                .unwrap();

CodePudding user response：

Use bigdecimal type

After hours of debugging, I found out diesel returns a type of Bigdecimal and you have to enable "numeric" feature for the diesel crate, I wish this could be documented.

New code:

let new_avg: Option<BigDecimal> = fruits
                .select(avg(weight))
                .filter(fruit_name.eq(&fruit_name))
                .get_result(&self.postgres.get().unwrap())
                .unwrap();

CodePudding user response：

The problem seems to be that you are trying to cast type Numeric from postgres to f64 in Rust, which does not have implementation.

I tried to reproduce your case so I created table like so:

CREATE TABLE fruits (
    id SERIAL PRIMARY KEY,
    value NUMERIC NOT NULL
)

for which diesel generated for me this in schemas:

table! {
    fruits (id) {
        id -> Int4,
        value -> Numeric,
    }
}

and in models I created Fruit:

#[derive(Queryable, Debug)]
pub struct Fruit {
    pub id: i32,
    pub value: f64
}

Right now when I try to run this:

let results = fruits.load::<Fruit>(&pg_connection).expect("");

I'm getting the same error as you which we can solve in few ways.

If you want to keep type f64 in Rust then you can change in table creation that value should have type DOUBLE PRECISION which after running diesel migration will generate Float8 type in schema which has it's implementation mentioned in error:

= help: the following implementations were found:
    <f64 as FromSql<Double, DB>>

If you want to keep type Numeric in postgres table you can try using bigecimal::BigDecimal or diesel::pg::data_types::PgNumeric as type of value in Fruit struct since there is also implementation for casting Numeric to PgNumeric.

If you want to keep both you probably have to implement it on you own