如何实现Postgres int4类型与Rust u32类型的转换？

SQL表结构 #

CREATE TABLE IF NOT EXISTS passwords 
(
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    username TEXT NOT NULL,
    password TEXT NOT NULL
);

Rust代码片段 #

struct Password {
   id: u32,
   title: String,
   username: String,
   password: String,
}
let items = self.client.query("SELECT * FROM passwords", &[]).unwrap().iter().map(|row| {
            let password = Password::new_with_id(
                row.get("id"),
                row.get("title"),
                row.get("username"),
                row.get("password"),
            );
            password
        }).collect();

报错信息 #

thread 'main' panicked at C:\Users\k\.cargo\registry\src\index.crates.io-6f17d22bba15001f\tokio-postgres-0.7.10\src\row.rs:151:25:
error retrieving column id: error deserializing column 0: cannot convert between the Rust type `u32` and the Postgres type `int4`
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
error: process didn't exit successfully: `target\debug\PaTUI.exe` (exit code: 101)

问题：如何实现Postgres类型int4与Rust原生类型u32之间的转换？

方法1：获取数据时显式转换类型 #

Postgres的int4对应Rust的i32，由于SERIAL是自增正数，直接将获取到的i32转为u32即可：

let password = Password::new_with_id(
    row.get::<_, i32>("id") as u32,
    row.get("title"),
    row.get("username"),
    row.get("password"),
);

方法2：修改结构体id类型为i32 #

Postgres的SERIAL本质就是int4（即Rust的i32），直接调整结构体的id类型，无需额外转换：

struct Password {
   id: i32,
   title: String,
   username: String,
   password: String,
}

方法3：自定义类型映射（适合必须用u32的场景） #

通过postgres-types crate实现u32与Postgres int4的双向映射：

1. 先在Cargo.toml添加依赖：

postgres-types = "0.2"

2. 实现FromSql和ToSql trait：

use postgres_types::{FromSql, ToSql, Type};
use std::error::Error;

impl FromSql<'_> for u32 {
    fn from_sql(ty: &Type, raw: &[u8]) -> Result<Self, Box<dyn Error + Sync + Send>> {
        let value = i32::from_sql(ty, raw)?;
        Ok(value as u32)
    }

    fn accepts(ty: &Type) -> bool {
        *ty == Type::INT4
    }
}

impl ToSql for u32 {
    fn to_sql(&self, ty: &Type, out: &mut Vec<u8>) -> Result<postgres_types::IsNull, Box<dyn Error + Sync + Send>> {
        (*self as i32).to_sql(ty, out)
    }

    fn accepts(ty: &Type) -> bool {
        *ty == Type::INT4
    }

    fn to_sql_checked(&self, ty: &Type, out: &mut Vec<u8>) -> Result<postgres_types::IsNull, Box<dyn Error + Sync + Send>> {
        (*self as i32).to_sql_checked(ty, out)
    }
}

完成后即可直接用row.get::<_, u32>("id")读取id字段。

内容的提问来源于stack exchange，提问作者Khacke