PostgreSQL去除null和空字符串之间的差异

Question

我有一个类似这样的查询：

$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();

我遇到了一个问题，因为在数据库中，空的city列有时是null，有时是空字符串（我知道这是不正确的，但无法更改）。当我的$cityVar变量为空时，我需要获取所有具有所需country的条目，但现在我只能获取那些city列为空字符串的条目（我无法获取那些city为null的条目）。

我唯一想到的解决办法是修改查询本身，例如：

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

但我想知道是否有更正当的方法来解决这个问题。注意：我需要搜索类似于city的更多列。

- Mindaugas Jakubauskas

Answer 1

使用COALESCE(city, '')函数 - 它会将NULL字符串强制转换为空字符串""。如果你有很多城市和很多对它们的查询，你可以在合并字段上创建一个索引来促进搜索。

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

- Eugene Lisitsky

---

我在查询中应该使用 WHERE COALESCE(location.city, "") = :city 吗？ - Mindaugas Jakubauskas

更好的解决方案是将表中的“null”字段更新为空字符串“''”。这可行吗？ - Eugene Lisitsky

谢谢。我知道有更好的方法，但不幸的是在这种情况下不可能。 - Mindaugas Jakubauskas

但是，如果在列上使用一个函数（这里是coalesce），则您的查询子句将无法使用位置.city上的索引（如果存在）。 - R. Du