Custom ColumnTypes - PostgreSQL Interval Value

[Rudi3]

Is it currently possible to use other types such as (Pg) Interval?

I found the Custom Variant in the Docs, but is this meant to map back to existing sea_orm::Values or can it be used to access other types than the predefined ones?

Is there currently a way to use other types than the listed ones or would they have to be implemented in sea_orm first?

Edit: What I was able to work out so far:

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "mytable")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    #[sea_orm(column_type = "Custom(\"interval\".to_owned())")]
    pub duration: String
}

myobject::ActiveModel {
    duration: Set(String::from("00:10:00")),
    ..Default::default()
}
.save(db)
.await.expect("oops");

This doesn't seem to make it work:

oops: Query("error returned from database: Column »duration« has type interval, but the expression has type text")

Am I missing something? Do I need a custom type instead of String?

[billy1624]

Hi @Rudi3, regarding the use of custom types you can check the following discussion & PR

• #139
• #400

Btw... how would you defined the interval attribute in the Model?

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "mytable")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    #[sea_orm(column_type = "Custom(\"interval\".to_owned())")]
    pub duration: chrono::Duration, # or std::time::Duration?
}

[Rudi3]

Hi @billy1624, sorry - it seems that I missed those issues. I'd probably use chrono as the other Date/Time types already rely on chrono (as far as I've seen) and it should be higher level (and can be converted back to std::time::Duration if needed).

But if I try this, DeriveEntityModel fails. I'll try if implementing the traits in https://github.com/SeaQL/sea-orm/pull/400/files and using a wrapper like pub struct Interval<T>(chrono::Duration, std::marker::PhantomData<T>);

will make my example work.

the trait bound `std::time::Duration: sea_orm::TryGetable` is not satisfied

the trait `sea_orm::TryGetable` is not implemented for `std::time::Duration`rustc(E0277)

the trait bound `sea_orm::Value: std::convert::From<std::time::Duration>` is not satisfied

the trait `std::convert::From<std::time::Duration>` is not implemented for `sea_orm::Value`

help: the following implementations were found:
        <sea_orm::Value as std::convert::From<&'a [u8]>>
        <sea_orm::Value as std::convert::From<&'a str>>
        <sea_orm::Value as std::convert::From<bool>>
        <sea_orm::Value as std::convert::From<chrono::DateTime<Tz>>>
      and 19 others
note: required because of the requirements on the impl of `std::convert::Into<sea_orm::Value>` for `std::time::Duration`rustc(E0277)

[...]

[billy1624]

A PR would be required in SeaORM before you can insert / get Pg interval value into / from PostgreSQL

[billy1624]

Btw... these PRs in sea-query are related

• SeaQL/sea-query#169
• SeaQL/sea-query#195

[Rudi3]

I see. I prepared this now, but I'm still missing something: https://github.com/SeaQL/sea-orm/commit/e5ee4765469fe8d4920e7c58befcafa6802dddab

I didn't find any related code dealing with sqlx::Decode for other chrono Types such as DateTime in sea_orm - if you could tell me how you handled this, I might be able to make a PR (I don't necessarily know what I'm doing).

error[E0277]: the trait bound `chrono::Duration: sqlx::Decode<'_, Postgres>` is not satisfied
    |
258 |                         row.try_get::<Option<$type>, _>(_column.as_str())
    |                             ^^^^^^^ the trait `sqlx::Decode<'_, Postgres>` is not implemented for `chrono::Duration`
...
308 | try_getable_postgres!(chrono::Duration);
    | --------------------------------------- in this macro invocation
    |
    = note: required because of the requirements on the impl of `sqlx::Decode<'_, Postgres>` for `Option<chrono::Duration>`
    = note: this error originates in the macro `try_getable_postgres` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0277]: the trait bound `chrono::Duration: ValueType` is not satisfied
    |
268 |                     QueryResultRow::Mock(row) => row.try_get(_column.as_str()).map_err(|e| {
    |                                                      ^^^^^^^ the trait `ValueType` is not implemented for `chrono::Duration`
...
308 | try_getable_postgres!(chrono::Duration);
    | --------------------------------------- in this macro invocation
    |
    = note: this error originates in the macro `try_getable_postgres` (in Nightly builds, run with -Z macro-backtrace for more info)

[Rudi3]

Is it safe to assume that there is still some code missing in sea_query?

When adding

#[cfg(feature = "sqlx-postgres")]
#[cfg_attr(docsrs, doc(cfg(feature = "sqlx-postgres")))]
impl_into_active_value!(crate::prelude::PgInterval, Set);

in active_model.rs, I see that:

the trait `From<sqlx::postgres::types::PgInterval>` is not implemented for `sea_query::Value`

(I am assuming that PgInterval is the only option available for now)

https://github.com/SeaQL/sea-orm/compare/master...Rudi3:pginterval?expand=1

[billy1624]

Is it safe to assume that there is still some code missing in sea_query?

Yes, currently we have no variant defined in sea_query::Value to store Interval

• https://github.com/SeaQL/sea-query/blob/5d6ef72a4db7e8e494a297a6bf570d9a91f41262/src/value.rs#L23-L79