Optional conditions in postgreSQL query

Question

I need to create a procedure with optional arguments and use them only if they are not null

My current query looks like:

SELECT * FROM sth WHERE
  (arg1 IS NULL OR sth.c1 = arg1) AND
  (arg2 IS NULL OR sth.c2 = arg2) AND
  (arg3 IS NULL OR sth.c3 > arg3) AND
  (arg4 IS NULL OR sth.c4 < arg4)

I'm thinking of a way to make it look better / shorter. My first shot is:

SELECT * FROM sth WHERE
  COALESCE(sth.c1 = arg1, 't') AND
  COALESCE(sth.c2 = arg2, 't') AND
  COALESCE(sth.c3 > arg3, 't') AND
  COALESCE(sth.c4 < arg4, 't');

but I'm not sure if this looks any better. Do you know any useful tricks for this?

Answer 1

Keep it the way it is. Using coalesce will prevent the query planner from doing its job properly, and you'll end up with sucky query plans.

Best I'm aware, the following expressions will use a btree index:

• col = 'val'
• col is null

The following expressions will not use a btree index:

• col is [not] 'val'
• (col = 'val') is [not] <true | false | null>
• col is [not] distinct from 'val'
• coalesce(col, 'val') = 'val'
• coalesce(col = 'val', <true | false | null>)