How to remove conditions from WHERE clause if parameters are NULL

Question

I'm passing 2 parameters to a PL/pgSQL function. Here's the query:

SELECT * 
FROM table 
WHERE col1 = param1 
  AND col2 = param2

Both parameters can be NULL, in which case the respective expression should be removed from the WHERE clause.

How can I do that? With IF conditions?

Answer 1

Maybe this is doing the trick:

SELECT * 
FROM table 
WHERE col1 = param1 
  AND (param2 is null or col2 = param2);

This is not removing the AND condition, but should make the unimportant in case of param2 is null. So not clearly answering your question but going around... ;)

Answer 2

The simplest (though probably not the most efficient) way is to handle the null inside the SQL statement itself, e.g.:

SELECT * 
FROM table 
WHERE col1 = param1 
AND (param2 IS NULL OR col2 = param2)

This by-passes the clause for all rows if the parameter is null.

Or with this trick:

SELECT * 
FROM table 
WHERE col1 = param1 
AND col2 = COALESCE(param2, col2)

When param2 is NULL, the condition is equivalent to col2 = col2, which will always be true as long as col2 doesn't itself contain NULL values.

A quick test using hard-coded values rather than a function parameter gave me the same query plan for both approaches - the OR and COALESCE parts are seemingly optimised away before planning the query, so it's as though the second AND were indeed being conditionally removed.