How do I make my parameter work when it is a null value?

If you want a parameter that can filter by a certain value, but when you have no value in your parameter, SQL returns no results. When the parameter has no value, SQL interprets it as null in your code. Null means no value. You can fix this problem by adding a code to fix the null case. There are 3 ways to resolve this problem: you can either coalesce the null value or add a logic to execute another operation.

Solution 1: use COALESCE

Coalesce in Postgres is a function that takes a list of arguments and returns the first non-null value. That means you can put your parameter at the start of that list. At the end, you can put a default value.

SELECT
data1,
data2,
city
FROM bio_data
WHERE city = COALESCE(#{{City Parameter}}, 'SFO')

In the code above, SQL will look at the WHERE line and first ask “Is City Parameter null?” If it is not null, then it will do “city = City Parameter”. However, if it is null, it will use the default and do city = ‘SFO’.

Solution 2: use additional logic

Coalesce is great because it allows you to define a default value, but what if instead, you wanted to simply remove the filter if the parameter was not specified? For example, this means you would show data from all cities.

SELECT
data1,
data2,
city
FROM bio_data
WHERE #{{City Parameter}} is null or city = #{{City Parameter}}

With the logic above, if the City Parameter has no value, it will set the left side of the or to true, which means the right side can be “ignored“ logically. If the City Parameter has a value, the left side is not ignored, which means the right side of the or applies.

Solution 3: use an advanced logic

What if instead of showing all cities by default, you wanted to simply show a subset. This is similar to Solution 1, but combined with Solution 2 to make it so the default is a list of values.

SELECT
data1,
data2,
city
FROM bio_data
WHERE (#{{City Parameter}} is null and city in ('SFO', 'LAX', 'YYZ', 'YQM', 'CDG', 'LAS')) or city = #{{City Parameter}}

With the logic above, the left side of the or is a more complex statement with 2 checks. First, is null checks that the parameter has no value. Second, in checks that the “city” column has values that belong in the list ('SFO', 'LAX', 'YYZ', 'YQM', 'CDG', 'LAS').