Ignoring Null input value when using a parameter with postgres

Question

Given this simple table fiddle

 CREATE TABLE tbl (
  tbl_id             serial PRIMARY KEY,
  column_a       text NOT NULL,
  isactive bool NOT NULL
);

INSERT INTO tbl VALUES
  (1, 'a', true)
, (2, 'b', true)
, (3, 'c', false);

I'm seeing a user using this case statement to suppress a parameter

SELECT
tbl_id,
column_a,
isactive
FROM tbl
WHERE CASE WHEN $1 IS NOT NULL THEN isactive = $1 ELSE 1=1 end AND
CASE WHEN $2 IS NOT NULL THEN column_a = $2 ELSE 1=1 end

I'm going to suggest that they use this syntax

SELECT
tbl_id,
column_a,
isactive
FROM tbl
WHERE ($1 IS NULL OR isactive = $1) and 
($2 IS NULL OR column_a = $2)

I think these are equivalent (and probably easier to read). Would you do anything different?

Laurenz Albe · Accepted Answer · 2021-11-03 07:18:19Z

1

Your suggestion is certainly better and no less efficient than the CASE statement.

But OR is usually a problem in WHERE conditions, because it makes it difficult for PostgreSQL to use indexes to speed up the query.

Better solutions are:

add the WHERE condition to the query only when the parameter IS NOT NULL

write the query as a UNION:

  SELECT tbl_id, column_a, isactive
  FROM tbl
  WHERE isactive = $1 AND column_a = $2
UNION ALL
  SELECT tbl_id, column_a, isactive
  FROM tbl
  WHERE isactive = $1 AND $2 IS NULL
UNION ALL
  SELECT tbl_id, column_a, isactive
  FROM tbl
  WHERE $1 IS NULL AND column_a = $2
UNION ALL
  SELECT tbl_id, column_a, isactive
  FROM tbl
  WHERE $1 IS NULL AND $2 IS NULL;

That looks longer and more complicated, but it can use indexes on isactive and column_a.

answered Nov 3, 2021 at 7:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

mellerbeck Over a year ago

Thank you! I appreciate it

Collectives™ on Stack Overflow

Ignoring Null input value when using a parameter with postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related