Optional conditions in postgreSQL query

postgresql, sql

Solution

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

Problem

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?

Original source