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.

Eugene Lisitsky · Accepted Answer · 2017-10-04 08:59:17Z

14

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 "

edited Oct 4, 2017 at 8:59

answered Oct 4, 2017 at 8:57

Eugene Lisitsky

13k6 gold badges42 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mindaugas Jakubauskas Over a year ago

Should I use it as WHERE COALESCE(location.city, "") = :city in my query?

Eugene Lisitsky Over a year ago

Better solution is to update the table set null fields to empty strings ''. Is it possible?

Mindaugas Jakubauskas Over a year ago

Thank you. I know it would be a better way, but unfortunately it is impossible in this case.

R. Du Over a year ago

but by using a function (coalesce here) on the column, your query clause won't be able to use the index on location.city if it exists ..

Collectives™ on Stack Overflow

PostgreSql remove difference between null and empty string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related