PostgreSql remove difference between null and empty string

Question

I have a query like this:

$stmt = $db->prepare("SELECT location.id FROM location WHERE location.city = :city AND location.country = :country");
$stmt->execute(array(':city' => $cityVar, ':country' => $countryVar));
$locationID = $stmt->fetchColumn();

I have faced a problem, because in database empty city column is sometimes null, sometimes empty string (I know it's wrong, but there is no way to change that). When my $cityVar variable is empty, I need to get all entries with needed country, but now I get only those where city column is empty string (I don't get those where city is null).

The only solution I come up with is to modify the query itself, for example:

$sql = "SELECT location.id FROM location WHERE location.country = :country ";
if ($cityVar) { 
   $sql .= "AND location.city = :city ";
}

but I was wondering if there is more legit ways to solve this problem. NOTE: there are more columns like city I need to search by.

Answer 1

Use COALESCE(city, '') - it will coerce NULL string to empty string "". If you have lots of cities and lots of queries to them, you can create an index on coalesced field to foster search.

"SELECT location.id FROM location WHERE COALESCE(location.city, '') = :city "