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?

Is the function written in `plpgsql` (as opposed to plain SQL, or one of the other pluggable languages)? — IMSoP, Apr 30 '14 at 10:52

score 13 · Answer 1 · answered Apr 30 '14 at 10:55

13

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... ;)

answered Apr 30 '14 at 10:55

frlan

6,950
3
31
72

i've modified the question.Can anyone answer that too. – user3339988 Apr 30 '14 at 13:48
1

apply what you have learned at this one. `WHERE (param1 is null or col1 = param1) AND (param2 is null or col2 = param2)`. Of course this would return everything if both a null. Maybe thinking about valid default values at this point? – frlan Apr 30 '14 at 13:55
Beware. The `IS NULL` leads to a full scan table access. – A Coder Mar 09 '22 at 16:06

IMSoP · Answer 2 · 2014-04-30T11:35:09.573

9

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.

edited Apr 30 '14 at 11:35

answered Apr 30 '14 at 10:55

IMSoP

89,526
13
117
169

+1 but note that coalesce isn't a good idea, because it won't use an index if one is available whereas the first variation will. – Denis de Bernardy Apr 30 '14 at 11:01
@Denis I just did some testing with a hard-coded `NULL` or string in place of `param2`, and got identical query plans for both strategies. Since it's constant for all rows, the first becomes either `TRUE OR ...` or `FALSE OR ...`, which can be optimised away, and the second becomes `COALESCE(NULL, col2)` or `COALESCE('known value', col2)`, which can be replaced by `col2` and `'known value'` before planning the query. – IMSoP Apr 30 '14 at 11:29
4

actually, `col2 = col2` will not always be true. If `col2` could contain null values, this condition could be `unknown`, so this method not only won't use index, but it also could return wrong results – Roman Pekar Apr 30 '14 at 11:29
@RomanPekar Good point about NULL values of `col2`; I'll add a note about that. – IMSoP Apr 30 '14 at 11:30
The first query can be better for use inside PL/pgSQL - the planer will create 2 different plans and will chose one on execution time. – Ihor Romanchenko Apr 30 '14 at 12:33

score 6 · Answer 3 · edited Jun 20 '20 at 09:12

A simple (param1 IS NULL OR col1 = param1) takes care of this, as has been answered already.

To actually remove any or all NULL conditions, you need dynamic SQL. You can build your statement in the client conditionally or you can create a plpgsql (or any other procedural language) function to take care of it. This approach may result in superior query plans when dealing with complex queries.

PL/pgSQL function

The tricky parts are:

to deal with NULL values and empty strings properly in string concatenation
to deal with variable data types while avoiding possible SQL injection

CREATE OR REPLACE FUNCTION f_conditional_where(_param1 int  = NULL
                                             , _param2 text = NULL
                                             , _param3 date = NULL)
  RETURNS SETOF tbl AS
$func$
DECLARE
   _where text :=
      concat_ws(' AND '
        , CASE WHEN _param1 IS NOT NULL THEN 'col1 = $1' END
        , CASE WHEN _param2 IS NOT NULL THEN 'col2 = $2' END
        , CASE WHEN _param3 IS NOT NULL THEN 'col3 = $3' END);   
   _sql text := 'SELECT * FROM tbl';
BEGIN

IF _where <> '' THEN
   _sql := _sql || ' WHERE ' || _where;
END IF;

-- debug output
RAISE NOTICE '
 _sql:   |%|
 _where: |%|', _sql, _where;

-- execute
RETURN QUERY EXECUTE _sql
USING $1, $2, $3;

END
$func$  LANGUAGE plpgsql;

Three examples for function call:

SELECT * FROM f_conditional_where();
SELECT * FROM f_conditional_where(1, 'foo');
SELECT * FROM f_conditional_where(_param3 := '2012-01-01', _param2 := 'foo');

SQL Fiddle.

You need to have a basic understanding of plpgsql for this. You'll find plenty of examples with ample explanation in the plpgsql tag.

How to remove conditions from WHERE clause if parameters are NULL

3 Answers3

PL/pgSQL function

Linked