Postgres getting id of inserted row

I'm not sure if it's appropriate to post a question about using Postgres with Rust here, so let me know if it's not. But the problem seems to be about datatype conversions, so hopefully it's okay. See the last comment near the end of the code. Everything here works except updating a row with a given id.

use postgres::{Client, Error, NoTls, Row};

fn insert_dog(client: &mut Client, name: &str, breed: &str) -> Result<Vec<Row>, Error> {
    client.query(
        "insert into dogs (name, breed) VALUES ($1, $2) returning id",
        &[&name, &breed],
    )
}

fn insert_dogs(client: &mut Client) -> Result<u64, Error> {
    insert_dog(client, "Maisey", "Treeing Walker Coonhound")?;
    insert_dog(client, "Ramsay", "Native American Indian Dog")?;
    insert_dog(client, "Comet", "Whippet")?;
    Ok(0)
}

fn delete_dogs(client: &mut Client) -> Result<u64, Error> {
    client.execute("delete from dogs", &[])
}

fn update_dog(client: &mut Client, id: u64, name: &str) -> Result<u64, Error> {
    let new_id = id as u32;
    dbg!(new_id);
    client.execute("update dogs set name = $2 where id = $1", &[&new_id, &name])
}

fn report_dogs(client: &mut Client) {
    if let Ok(rows) = client.query("select id, name, breed from dogs", &[]) {
        for row in rows {
            let id: i32 = row.get(0);
            let name: &str = row.get(1);
            let breed: &str = row.get(2);
            println!("id={} name={} breed={}", id, name, breed);
        }
    }
}

fn main() {
    let username = "mark";
    let password = "";
    let database = "animals";
    let conn_str = format!(
        "postgresql://{}:{}@localhost/{}",
        username, password, database
    );
    let mut client = Client::connect(&conn_str, NoTls).unwrap();

    delete_dogs(&mut client).unwrap();
    insert_dogs(&mut client).unwrap();

    let rows = insert_dog(&mut client, "Oscar", "German Shorthaired Pointer").unwrap();
    let row = rows.first().unwrap();
    if let Some::<i32>(id) = row.get(0) {
        dbg!(id); // outputs an integer value as expected

        // Next line panics with "cause: Some(WrongType { postgres: Int4, rust: "u32" }) }".
        // It doesn't compile if I try to pass id as a u32.
        update_dog(&mut client, id as u64, "Oscar Wilde").unwrap();
    }

    report_dogs(&mut client)
}

What is your SQL definition for the table? Please share your CREATE TABLE command.

Prima facie it seems your id is not a PostgreSQL BIGINT type that would map to i64 but it is an INT type which will map to i32.

Check the mapping of types used by the rust-postgres crate between PostgreSQL and rust types here:
https://github.com/sfackler/rust-postgres/blob/master/postgres-types/src/lib.rs#L368

Thanks, using i32 instead of u64 fixed it!
I got confused because I saw that the return type of client.execute was Result<u64, Error>, but I see now that is just the type of count of affected rows.

In answer to your question, here is how I created the table:

create table dogs (
  id serial,
  breed text,
  name text
)

Here us what I see when I enter \d in psql:

                            Table "public.dogs"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('dogs_id_seq'::regclass)
 breed  | text    |           |          |
 name   | text    |           |          |

@osrust Am I using the best approach to get the id of the inserted row?

Are you asking regarding the Rust interface, or the SQL call?

With regards to the SQL, I do believe you're using the current state of the art way of getting the last inserted row with postgresql. CURRVAL() and LASTVAL() requires an extra round-trip, while using RETURNING will get you what you want in just one call.

1 Like

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.