Rust PostgreSQL LIKE Patterns

Posted by Brahm Lower on Mon 27 August 2018

Problemo!

I've been working on gifzone and had a problem wiht a postgres query. It was supposed to get items whose title contained a user provided keyword. The code essentially looked like this:

pub fn fetch_filter(conn: &Connection, query: &SearchQuery) -> Result<Vec<Gif>, DatabaseError> {
    let rows = conn.query("SELECT * FROM gifs WHERE title LIKE '%$1%'", &[ &query.value ])?;
    let vec = rows.iter().map(Gif::from).collect();
    Ok(vec)
}

This would panic while running though:

thread '<unnamed>' panicked at 'expected 0 parameters but got 1', /Users/brahm/.cargo/registry/src/github.com-1ecc6299db9ec823/postgres-0.15.2/src/lib.rs:602:9
note: Run with `RUST_BACKTRACE=1` for a backtrace.

This was a little unexpected since the SQL obviously contains one parameter, so the quoting must be obscuring the parameter reference.

SOLVED

I didn't see any formal documentation addressing this in the documentation for the library, but I eventually found a github issue that provides an adequate workaround using string concatination operators within the SQL statement. Final code looks as follows:

pub fn fetch_filter(conn: &Connection, query: &SearchQuery) -> Result<Vec<Gif>, DatabaseError> {
    let rows = conn.query("SELECT * FROM gifs WHERE title LIKE '%' || $1 || '%'", &[ &query.value ])?;
    let vec = rows.iter().map(Gif::from).collect();
    Ok(vec)
}

tags: rust, postgresql