[diesel] how to define nullable field with timestamp type in the table?

help
1

hey, I want to define a table that contains nullable field, this is my up.sql

create table TodoItems (
    id serial primary key not null,
    name varchar(50) not null,
    completed boolean not null,
    deleted boolean not null,
    created_at timestamp default null,
    completed_at timestamp default null,
    deleted_at timestamp default null
);

and this is src/schema.rs

diesel::table! {
    TodoItems(id) {
        id -> Integer,
        name -> VarChar,
        completed -> Bool,
        deleted -> Bool,
        created_at -> Nullable<Timestamp>,
        completed_at -> Nullable<Timestamp>,
        deleted_at -> Nullable<Timestamp>,
    }
}

and this is src/models.rs

use diesel::prelude::*;
use diesel::sql_types::Timestamp;

#[derive(Queryable, Selectable)]
#[diesel(table_name = crate::schema::TodoItems)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct TodoItem {
    pub id: i32,
    pub name: String,
    pub completed: bool,
    pub deleted: bool,
    pub created_at: Option<Timestamp>,
    pub completed_at: Option<Timestamp>,
    pub deleted_at: Option<Timestamp>
}

but the compiler tells me that

error[E0277]: the trait bound `diesel::sql_types::Timestamp: FromSql<diesel::sql_types::Timestamp, Pg>` is not satisfied
  --> src/models.rs:14:21
   |
14 |     pub deleted_at: Option<Timestamp>
   |                     ^^^^^^ the trait `FromSql<diesel::sql_types::Timestamp, Pg>` is not implemented for `diesel::sql_types::Timestamp`
   |
   = help: the following other types implement trait `FromSql<A, DB>`:
             <(T0, T1) as FromSql<Record<(ST0, ST1)>, Pg>>
             <(T0, T1, T2) as FromSql<Record<(ST0, ST1, ST2)>, Pg>>
             <(T0, T1, T2, T3) as FromSql<Record<(ST0, ST1, ST2, ST3)>, Pg>>
             <(T0, T1, T2, T3, T4) as FromSql<Record<(ST0, ST1, ST2, ST3, ST4)>, Pg>>
             <(T0, T1, T2, T3, T4, T5) as FromSql<Record<(ST0, ST1, ST2, ST3, ST4, ST5)>, Pg>>
             <(T0, T1, T2, T3, T4, T5, T6) as FromSql<Record<(ST0, ST1, ST2, ST3, ST4, ST5, ST6)>, Pg>>
             <(T0, T1, T2, T3, T4, T5, T6, T7) as FromSql<Record<(ST0, ST1, ST2, ST3, ST4, ST5, ST6, ST7)>, Pg>>
             <(T0, T1, T2, T3, T4, T5, T6, T7, T8) as FromSql<Record<(ST0, ST1, ST2, ST3, ST4, ST5, ST6, ST7, ST8)>, Pg>>
           and 50 others
   = note: required for `Option<diesel::sql_types::Timestamp>` to implement `FromSql<diesel::sql_types::Nullable<diesel::sql_types::Timestamp>, Pg>`
   = note: required for `Option<diesel::sql_types::Timestamp>` to implement `diesel::Queryable<diesel::sql_types::Nullable<diesel::sql_types::Timestamp>, Pg>`
   = note: required for `Option<diesel::sql_types::Timestamp>` to implement `FromSqlRow<diesel::sql_types::Nullable<diesel::sql_types::Timestamp>, Pg>`
   = help: see issue #48214

For more information about this error, try `rustc --explain E0277`.
error: could not compile `rust-todo` (lib) due to previous error

what is the wrong with my code ? how can I fix it ?

2

You shouldn't be using this type. You need a Rust type that corresponds to a Postgres Timestamp field, which should probably be chrono::NaiveDateTime according to this guide.

You will need to add the chrono crate to Cargo.toml and activate the chrono feature for diesel.

3 Likes
3

thank you,
i have fix it by this code

# in the Cargo.toml
[dependencies]
diesel = { version = "2.1.0", features = ["postgres", "chrono"] }

in the schema.rs

// @generated automatically by Diesel CLI.

diesel::table! {
    TodoItems(id) {
        id -> Integer,
        name -> VarChar,
        completed -> Bool,
        deleted -> Bool,
        created_at -> Nullable<Timestamp>,
        completed_at -> Nullable<Timestamp>,
        deleted_at -> Nullable<Timestamp>,
    }
}

in the models.rs

use diesel::prelude::*;
use chrono::{Local, NaiveDateTime, TimeZone};

#[derive(Queryable, Selectable, Debug, Clone)]
#[diesel(table_name = crate::schema::TodoItems)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct TodoItem {
    pub id: i32,
    pub name: String,
    pub completed: bool,
    pub deleted: bool,
    pub created_at: Option<NaiveDateTime>,
    pub completed_at: Option<NaiveDateTime>,
    pub deleted_at: Option<NaiveDateTime>
}
5

For reference: The official documentation about this topic is here. Instead of providing an incomplete list, it explains the underlying principle, which might be more helpful.

1 Like