Postgres 15+Rust 1.70+Diesel 2.0：INSERT后get_results未返回插入行

在Postgres 15、Rust 1.70.0和Diesel 2.0环境下，可成功向assets表插入数据，但执行INSERT语句后调用get_results<Asset>返回空数组；执行SELECT语句时get_results能正常返回对应行数组。Asset结构体已派生#[derive(QueryableByName)]，不清楚问题所在，请求指导。

测试代码 #

#[test]
fn test_get_result(){
     use crate::service_tests::prelude::connection_without_transaction;
     use diesel::sql_query;
     use diesel::RunQueryDsl;

     use crate::models::asset::Asset;

     let mut connection = connection_without_transaction();

     let query = "Select * from assets";
     let result1 = diesel::sql_query(query)
                            .get_results::<Asset>(&mut connection)
                            .unwrap();
    dbg!(result1.len());

    let query = "insert into assets (
        parent_assetcode,category_code,label_code,
        erp_code,name,description,is_depreciable,
        physical_id,machine_tag_no,in_store,
        quantity,is_multiple_entries,group_qty,
        acquired_value,acquired_date,date_put_to_use,
        physical_verification,asset_type,asset_user,
        cost_center_code,department_code,branch_code,
        location_code,physical_location_code,requires_maintenance,
        created_user_id,remarks
        )
        values
        (
            'BL00000SP','BLD','L-BLD-2','BL00000SP',
            'name of asset','descr',false,'aaaa',
            'mactag',true,1200,false,10,13250.0,
            '2023-05-01','2023-05-02',true,'Owned',
            1,'162','NA','MA003','276 VMART PATNA KURJI',
            'NA',false,1,'remarksss'
        )";
     let result2 = diesel::sql_query(query)
                        .get_results::<Asset>(&mut connection)
                        .unwrap();
     dbg!(result2.len());

}

测试输出 #

running 1 test
[src/service_tests/csv_import_test.rs:53] result1.len() = 44
[src/service_tests/csv_import_test.rs:78] result2.len() = 0
test service_tests::csv_import_test::test_get_result ... ok

解决方案 #

这是因为普通INSERT语句默认不会返回插入的行数据，Diesel的get_results需要查询有返回结果才能解析成Asset对象。

解决方法是修改INSERT语句，使用PostgreSQL的INSERT ... RETURNING语法，让插入操作返回刚插入的记录：

修改后的INSERT语句如下：

insert into assets (
    parent_assetcode,category_code,label_code,
    erp_code,name,description,is_depreciable,
    physical_id,machine_tag_no,in_store,
    quantity,is_multiple_entries,group_qty,
    acquired_value,acquired_date,date_put_to_use,
    physical_verification,asset_type,asset_user,
    cost_center_code,department_code,branch_code,
    location_code,physical_location_code,requires_maintenance,
    created_user_id,remarks
)
values
(
    'BL00000SP','BLD','L-BLD-2','BL00000SP',
    'name of asset','descr',false,'aaaa',
    'mactag',true,1200,false,10,13250.0,
    '2023-05-01','2023-05-02',true,'Owned',
    1,'162','NA','MA003','276 VMART PATNA KURJI',
    'NA',false,1,'remarksss'
) RETURNING *;

修改后，get_results<Asset>就能获取到刚插入的记录，result2.len()会变为1。

如果不需要返回所有字段，也可以指定具体字段（比如RETURNING id, name），只要返回字段能和Asset结构体的字段对应即可。

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