Understanding own type -> JSONB

[apiraino]

Hi,

after figuring out how to query a JSONB field, now I can't guess the corretc casting for an INSERT/UPDATE.

Here's more or less what I am trying:

testdb=> \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 fld    | jsonb   |           |          | 

#[derive(Debug, Deserialize, Serialize, ToSql, FromSql, Clone)]
pub struct MyType {
    pub id: i32
}

pub async fn update(client: &DbClient, data: MyType) -> Result<u64, DbError> {
    let st = client
        .prepare_typed(
            "insert into test (fld) values ($1)",
            &[tokio_postgres::types::Type::JSONB],
        )
        .await?;

    client
        .execute(
            &st,
            &[
                &data
            ],
        )
        .await
}

I've tried various combo with use tokio_postgres::types::Json, none of them worked. When I can make it compile, then it fails at runtime because the "elementary" type is not converted to Json, example error:

[2020-11-24 19:40:49.174798 +01:00] ERROR .... Error { kind: ToSql(0), cause: Some(WrongType { postgres: Json, rust: "core::option::Option<alloc::string::String>" }) }
[2020-11-24 19:40:49.174897 +01:00] ERROR ... cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `json`

Second question: can I use tokio_postgres::types::Type::JSONB or do you suggest using tokio_postgres::types::Type::JSON because of the extra byte header that may make things more difficult?

thanks

[sfackler]

The derive feature has no interaction with JSONB of any kind: https://docs.rs/postgres-types/0.1.3/postgres_types/#derive. Importing a type will not change that fact.

[apiraino]

Thanks @sfackler for the quick feedback (always appreciated). I'm a bit confused though and don't understand how to make an actionable out of your answer.

If I understand you are suggesting that I cannot cast to a JSONB and that I should use a JSON, ex:

            "insert into test (fld) values ($1)",
            &[tokio_postgres::types::Type::JSON],

but this requires &data (in my example) to be cast to a tokio_postgres::types::Json<T> and that's the first part of my question. I could not find a way to do that.

Am I far from the truth?

[apiraino]

oook I think I got it. Found the one and single example around to understand how that is supposed to work:

use tokio_postgres::types::Json;

pub async fn update(client: &DbClient, data: MyType) -> Result<u64, _> {
    let st = client
        .prepare_typed(
            "insert into test (fld) values ($1)",
            &[tokio_postgres::types::Type::JSON],
        )
        .await?;

    client.execute(&st, &[&Json(data)]).await
}

Both tokio_postgres::types::Type::JSON and tokio_postgres::types::Type::JSONB in the prepare_typed() work.

make sense?

[sfackler]

Yes, that is how the Json type is intended to be used.