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?

CodePudding user response：

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.