Postgres: handling null in where clause

Question

Using Birt reporting which allows injecting parameter in a query as follow:

SELECT *
FROM foo 
WHERE bar = ?

The question mark comes from a drop down list. Among these values, 'All' can be selected, meaning there are no WHERE clause.

Is there a way to have conditional value in the WHERE clause such as below or an equivalent ?

WHERE
  CASE WHEN ? IS NULL 
  THEN 1=1
  ELSE bar = '%'
END

Something like where(bar = ? or ? is null)?

jarlh
– jarlh

2022-06-16 18:31:18 +00:00
Commented Jun 16, 2022 at 18:31 — jarlh
– jarlh, Commented Jun 16, 2022 at 18:31
questionning my whole career now ... Thanks anyway !

Hey StackExchange
– Hey StackExchange

2022-06-16 19:37:48 +00:00
Commented Jun 16, 2022 at 19:37 — Hey StackExchange
– Hey StackExchange, Commented Jun 16, 2022 at 19:37

Iłya Bursov · Accepted Answer · 2022-06-16 18:40:33Z

3

you can try something like:

SELECT *
FROM foo 
WHERE COALESCE(bar, '') = COALESCE(?, bar, '')

so, if argument is null - column will be compared with itself, which is almost always returns true, except weird logic around null values, this is why we need coalesce on both sides of equation

edited Jun 16, 2022 at 18:40

answered Jun 16, 2022 at 18:31

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hey StackExchange Over a year ago

Geez I must say. Thanks it works !

Hey StackExchange Over a year ago

Maybe I should open a new question, in which case I will, but can this be adapted if multiple values in the list are selected (kind on IN ('p1', 'p2') ?

Iłya Bursov Over a year ago

@HeyStackExchange yeah, sure like IN (coalesce(a,b,c...), coalesce(d,e,f...))

Collectives™ on Stack Overflow

Postgres: handling null in where clause

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related