Performing UPDATE statement with Postgres

I am attempting to perform an "UPDATE" transaction against a Postgres database, however, I cannot find any documentation regarding performing the update. I have tried several different commands from the crate, some even complete. But the table is never updated. Any thoughts or ideas? I essentially just wat to add a new value to an existing item within a table in Postgres.

My current code (has changed a lot), runs without error, but the changed value is not reflected when querying the data afterwards.

pub struct Variables {
    name: String,
    value: String
}

pub fn configuring_variables(words: Vec<&str>) {
    let mut client = Client::connect("postgresql://rust:rust@localhost", NoTls);
    let new_value = Variables {
        name: words[1].to_string(),
        value: words[2].to_string()
    };
    client.expect("Query failed, ensure variables are spelled correctly.").query("UPDATE variables SET {new_value.name} = {new_value.value} WHERE name = {new_value.name};", &[]);
    println!("Added {} as the value for {}.", &new_value.value, &new_value.name);
}

pub fn displaying_variables() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://rust:rust@localhost", NoTls)?;
    for row in client.query("SELECT name,value FROM variables", &[]).unwrap() {
        let new_var = Variables {
            name: row.get(0),
            value: row.get(1),
        };
            println!(" {:?} = {:?} ", new_var.name, new_var.value);
    }
    Ok(())
}

Firstly, normally we put the expect where we define the variable, not where we use it.
Secondly, your query is malformed and as such doesn't make any sense. And you are not being able to detect it since you are not handling the returned error from query (with manual match, unwrap, expect, etc). Also, as far as I can tell, the query function is for making queries, such as SELECT statements. For updates, you want to use execute.
Thirdly, I think you meant to use the following update:

client.execute("UPDATE variables SET name = $2 WHERE name = $1;", &words[..2]).unwrap();

I am assuming you are familiar with regular SQL syntax. Here the $1 and $2 act as placeholders, which are filled in with corresponding values you pass in the slice (which is the second argument). Although slices in Rust are zero-indexed, placeholders seem to be one-indexed.
If you have further questions, please ask here.

Thank you for your response. New to Rust, so I look into the error messages that Rust provides after a failed compile. I placed the "expect" there because it was highlighted by Rust, asking me to consider placing it there.

note: the method `execute` exists on the type `Client`
  --> /home/smoke/.cargo/registry/src/github.com-1ecc6299db9ec823/postgres-0.19.4/src/client.rs:83:5
   |
83 | /     pub fn execute<T>(&mut self, query: &T, params: &[&(dyn ToSql + Sync)]) -> Result<u64, Error>
84 | |     where
85 | |         T: ?Sized + ToStatement,
   | |________________________________^
help: consider using `Result::expect` to unwrap the `Client` value, panicking if the value is a `Result::Err`
   |
15 |     client.expect("REASON").execute("UPDATE variables SET value = $2 WHERE name = $1;", &words[..2]).unwrap();
   |           +++++++++++++++++

But other than that the changes you suggested are throwing an error at method not found in 'Result<Client, postgres::Error>' Any thoughts? Been browsing the Postgres Docs, I don't think I have a typo anywhere, and my code looks pretty much the same as theirs?

When I place the .expect() statement in. It also returns:

error[E0308]: mismatched types
  --> src/database_operations.rs:15:104
   |
15 |     update_query.expect("query failure..").execute("UPDATE variables SET value = $2 WHERE name = $1;", &words[..2]).unwrap();
   |                                            ------- arguments to this function are incorrect            ^^^^^^^^^^^ expected trait object `dyn ToSql`, found `str`
   |
   = note: expected reference `&[&dyn ToSql + Sync]`
              found reference `&[&str]`

Take a small break and read about the Result type and its expect and unwrap methods, then go back to your code. That should help you understand better what is happening.

3 Likes

Hey, I did what you suggested. Came back, and got it working perfectly! Plus my headache is gone! Cheers!

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.