0

I am working with tokio postgres for a Rust project where I have a table Home having two columns 'number' and 'address'. My queries are being sent from the rust source code using the sql client as shown below,

let rows = client.query(QUERY, &[&"number", "address"]).await?;

where

QUERY: &str =
"
SELECT * FROM Home 
WHERE number <= $1 
AND address = $2;
";

In the above case, both inputs are valid strings of non-zero length. Given, this information, I am trying to query the rows of the table following certain rules. I have provided them below.

  1. If the query input 'address' is null, then the AND part of the string will not be there. In the problem only the 'address' parameter can be null only. The 'number' field is always consistently non-empty. Some ideas that I came across look like this but the ideas are not that concrete and in the current condition it does not work. One example,

    QUERY: &str = " SELECT * FROM Home WHERE number <= $1
    IF $2 IS NOT NULL THEN address = $2; ";

  2. I will have to modify the rhs side SQL queries. I can still create a dynamic string so that at run time the queries will look different according to the case encountered, but the requirement is to handle it directly with the help of a SQL rather than rust.

Is there a way to achieve this?

bluestacks454
  • 151
  • 7

1 Answers1

2

You can use coalesce function in your query:

SELECT * FROM Home 
WHERE number <= $1 
AND address = COALESCE($2, address);

If input parameter $2 will be null, then the field address will will be compared against itself, which will always return true (if there are no nulls in the address field).

qaziqarta
  • 1,782
  • 1
  • 4
  • 11
  • if the address parameter is null, I will have to unwrap the variable before its inserted into the query method which will throw an error in case. Is there a way to handle this from Rust without me having to implement unwrap().I saw postgres client does not take None – bluestacks454 Aug 23 '22 at 02:55
  • @bluestacks454, you mean this question?: https://stackoverflow.com/q/73452619/19032206 – qaziqarta Aug 23 '22 at 12:26
  • yes. This question. – bluestacks454 Aug 24 '22 at 02:56