CODEWITHSUNDEEP
sqlpostgresql
Adassko
Adassko

Reputation: 5343

Optional conditions in postgreSQL query

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?

Upvotes: 4

Views: 3294

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

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

Upvotes: 5

Adassko
Adassko

Reputation: 5343

Ok, I think that this query is the best idea for this purpose

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

it doesn't use any functions so the query planner should work fine just as before

it just uses simple expressions where:

1.
true = true // true
true = false // false
true = null // null

2.
false is false // true
true is false // false
null is false // false

3.
not true // false
not false // true

so it will return true if expression is true OR null

Upvotes: 1

Related Questions