Parse data into Vec and insert vector into postgresql

Question

I am receiving a JSON array which is inserted into a Vec<T>. I am using serde for the parsing. I want to insert the vector to a database table. The JSON array is parsed fine. The field publication_time is with time zone so I parse it using my_date_format using the example provided by serde. When I add (derive) Insertable to the struct cargo build fails with

error[E0277]: the trait bound `DateTime<Local>: diesel::Expression` is not satisfied
 --> src/models.rs:5:23
  |
5 | #[derive(Deserialize, Insertable)]
  |                       ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `DateTime<Local>`
  |
  = note: required because of the requirements on the impl of `AsExpression<diesel::sql_types::Nullable<diesel::sql_types::Timestamp>>` for `DateTime<Local>`
  = note: this error originates in a derive macro (in Nightly builds, run with -Z macro-backtrace for more info)

models.rs:

use serde::{Deserialize};
use chrono::{DateTime, Local};
use crate::schema::readings;

#[derive(Deserialize, Insertable)]
struct Reading {
    #[serde(with = "my_date_format")]
    publication_time: DateTime<Local>,
    id: i32,
    index: i32,
    field_description: String,
    measurement: f32,
}

schema.rs:

table! {
    readings {
        measurement_time_default -> Nullable<Timestamp>,
        id -> Nullable<Integer>,
        index -> Nullable<Integer>,
        field_description -> Nullable<Text>,
        measurement -> Nullable<Float>,
    }
}

Cargo.toml:

serde = "1"
serde_json = "1"
serde-datetime = "0.1.0"
diesel = { version = "1.4.4", features = ["postgres", "chrono"] }
chrono = "0.4.19"

I saw this similar question regarding BigDecimal, but my issue is with DateTime<Local>. I have used chrono::NaiveDateTime in another project where I also insert data to a table. In this answer there is linked to the particular versions diesel is using. When I change the type to NaiveDateTime serde fails to compile with this error:

error[E0308]: mismatched types
 --> src/models.rs:5:10
  |
5 | #[derive(Deserialize, Insertable)]
  |          ^^^^^^^^^^^ expected struct `NaiveDateTime`, found struct `DateTime`
  |
  = note: expected struct `NaiveDateTime`
             found struct `DateTime<Local>`
  = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

This question is missing crucial details to provide a complete answer. Please add the corresponding schema definition and your implementation of `my_date_format`. Otherwise it's only possible to say that `DateTime` is not compatible with any diesel sql type, therefore you cannot just use that type as part of your `Insertable` derive. — weiznich, Mar 29 '21 at 09:02
Thank you @weiznich. You solved it. I've edited the question and added schema.rs. Then I performed a new search and saw https://docs.diesel.rs/diesel/pg/types/sql_types/struct.Timestamptz.html where it uses Timestamptz. I've added the original Timestamp for clarity. — kometen, Mar 29 '21 at 13:49

kometen · Accepted Answer · 2021-03-29T14:00:09.280

The comment by weiznich to include schema.rs pointed me in the right direction. I changed Timestamp to Timestamptz.

schema.rs:

table! {
    readings {
        measurement_time_default -> Nullable<Timestamptz>,
        id -> Nullable<Integer>,
        index -> Nullable<Integer>,
        field_description -> Nullable<Text>,
        measurement -> Nullable<Float>,
    }
}

Timestamptz uses DateTime as described here. Timestamp uses NaiveDateTime. This solves my issue.

Parse data into Vec and insert vector into postgresql

1 Answers1