CODEWITHSUNDEEP
postgresqlnull
mellerbeck
mellerbeck

Reputation: 317

Ignoring Null input value when using a parameter with postgres

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?

Upvotes: 0

Views: 519

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247215

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.

Upvotes: 1

Related Questions