rust 使用 postgres crate 这个客户端，用select * 查询出来的结果用serde_json 序列化，怎么处理postgres 中的 TIMESTAMP 类型？

Question

使用 postgres rust 驱动，文档 https://docs.rs/postgres/latest/postgres/，

使用 select * 语句查询数据，在用 serde_json 序列化基本类型的时候正常，但是特殊类型就panic 了，如 时间戳类型 TIMESTAMP，代码如下

l
  let query = format!("SELECT * FROM \"{}\".\"{}\";", &schema, &table);
  let records = client.query(&query, &[]).expect("没有获取到结果");
  let mut result: Vec<Map<String, Value>> = vec![];

  for row in records.iter() {
    let mut record = serde_json::Map::new();
    for (i, column) in row.columns().iter().enumerate() {
      let value: Value = match column.type_() {
        &postgres::types::Type::INT4 => row.get::<_, i32>(i).into(),
        &postgres::types::Type::TEXT => row.get::<_, String>(i).into(),
        &postgres::types::Type::BOOL => row.get::<_, bool>(i).into(),
        /// 这里报错
        &postgres::types::Type::TIMESTAMP => row.get::<_, String>(i).into(),
        // &postgres::types::Type::TIMESTAMP => {
        //   row.get::<_, NaiveDateTime>(i).into()
        // }
        // Add more type conversions as needed
        _ => Value::Null,
      };
      record.insert(column.name().to_string(), value);
    }
    result.push(record);
  }

  result

怎么处理 postgres 中的特殊类型如 TIMESTAMP 类型的序列化？

Answer 1

extern crate chrono;
extern crate postgres;
extern crate serde_json;
extern crate serde;
extern crate serde_derive;

use chrono::NaiveDateTime;
use postgres::{Client, NoTls};
use serde_json::Value;
use std::collections::HashMap as Map;

fn main() {
    let conn_str = "host=localhost user=postgres password=yourpassword dbname=yourdbname";
    let mut client = Client::connect(conn_str, NoTls).expect("Failed to connect to DB");

    let schema = "your_schema";
    let table = "your_table";

    let records = query_records(&mut client, &schema, &table);
    println!("Records: {:?}", records);
}

fn query_records(client: &mut Client, schema: &str, table: &str) -> Vec<Map<String, Value>> {
    let query = format!("SELECT * FROM \"{}\".\"{}\";", schema, table);
    let records = client.query(&query, &[]).expect("Failed to fetch records");
    let mut result: Vec<Map<String, Value>> = vec![];

    for row in records.iter() {
        let mut record = serde_json::Map::new();
        for (i, column) in row.columns().iter().enumerate() {
            let value: Value = match column.type_() {
                &postgres::types::Type::INT4 => row.get::<_, i32>(i).into(),
                &postgres::types::Type::TEXT => row.get::<_, String>(i).into(),
                &postgres::types::Type::BOOL => row.get::<_, bool>(i).into(),
                &postgres::types::Type::TIMESTAMP => {
                    let dt = row.get::<_, NaiveDateTime>(i);
                    let dt_string = serde_json::to_string(&dt.format("%Y-%m-%d %H:%M:%S").to_string()).unwrap();
                    serde_json::from_str(&dt_string).unwrap()
                }
                _ => Value::Null,
            };
            record.insert(column.name().to_string(), value);
        }
        result.push(record);
    }

    result
}

Cargo.toml文件：

[dependencies]
postgres = "0.20.0"
serde_json = "1.0.72"
chrono = "0.4.19"
serde = "1.0.130"
serde_derive = "1.0.130"