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

Parameters on Insights dashboards are derived from the SQL queries in the blocks of that dashboard. Depending on how you write the WHERE statements in your query, the parameter could be left empty or must be populated in order for data to appear in your Insights block. 

When the parameter has no value, SQL interprets it as null in your code. Null means no value.

If you want your Insights block to populate data even if your parameter field is left empty (aka, is Null), you can use the strategies listed below.

Solution 1: use "or is null" logic in your WHERE statement

The example queries below will create a parameter at the top of your Insights dashboard called "City Parameter". The data in your block will populate if "City Parameter" is populated, and also if it is left empty (Null).

To include null values in your Insights search parameter for single-data fields with a single datapoint parameter (i.e., your parameter only accepts one value, not multiple values), you can use an OR statement like:

SELECT
data1,
data2,
city
FROM bio_data
WHERE (city = {{City Parameter}} OR city = {{City Parameter}} IS NULL)

If you have a multi-select data field and are using a multi-select Insights parameter, there are a couple options to include Null values.

• To filter for any of {{Parameter Values}} within the multi-select data field, or if {{Parameter Values}} is empty, use the ?| Postgres functions. For example:

SELECT
data1,
data2,
city
FROM bio_data
WHERE (city ?| {{City Parameter}} OR CARDINALITY({{City Parameter}}) = 0)

• To filter where all of the values populated in {{Parameter Values}} are within the multi-select data field, or if {{Parameter Values}} is empty, use the ?& Postgres functions. For example:

SELECT
data1,
data2,
city
FROM bio_data
WHERE (city ?& {{City Parameter}} OR CARDINALITY({{City Parameter}}) = 0)

• To filter where all of the values populated in {{Parameter Values}} are within the multi-select data field, or if {{Parameter Values}} is empty, use the ?& Postgres functions.

If you have a single-select data field and are using a multi-select Insights parameter, you can use the ANY and CARDINALITY Postgres functions. For example:

SELECT
data1,
data2,
city
FROM bio_data
WHERE (city ANY{{City Parameter}} OR CARDINALITY({{City Parameter}}) = 0)

Solution 2: 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 3: 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 4: 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').