PostgreSQL - conditionally exclude statement from WHERE clause

Question

I'm using PL/pgSQL (PostgreSQL version 10) and wonder if there's a way to exclude statement from WHERE clause.

My code:

WHERE 
(
    e.priority >= priority_from AND
    e.priority <= priority_to
) AND
v.id = vocabulary_id_ AND
e.text LIKE '%' || search_phrase || '%'
;

If the parameter vocabulary_id_ has value of 0 or null - the query returns 0 records because there's no such vocabulary with Id that equals 0 or null.

If I go with IF ELSE statement - I'll end up having 2 almost identical queries - one with v.id = vocabulary_id_ AND part, and the other without it.

Is there an easy way to exclude this part using PL/pgSQL syntax?

What's your expect result if vocabulary_id_ is null or vocabulary_id_=0? — D-Shih
– D-Shih, Commented Jul 8, 2018 at 20:13
@D-Shih I'm passing the value from .NET Core project - currently 0 is being passed. — Alex Herman
– Alex Herman, Commented Jul 8, 2018 at 20:14
You're trying to push a business process requirement directly into the database... That's a big issue. You should review what you're trying to do because, to me, it's like you're trying to say "hey, if I don't have the email address, do this but if I do, do the same thing but with that : all with the same query". — olleo
– olleo, Commented Jul 8, 2018 at 20:35

Ctznkane525 · Accepted Answer · 2018-07-08 20:27:50Z

2

You can use this

WHERE 
(
e.priority >= priority_from AND
e.priority <= priority_to
) And    (V.id = vocabulary_id_ or vocabulary_id_ is null or vocabulary_id_ = 0) and
e.text LIKE '%' || search_phrase || '%'
 ;

Just to add, at least with SQL Server, while this is convenient, at least with SQL Server, these types of queries sometimes lead to suboptimal performance. I don't know if that is the case with postgres.

edited Jul 8, 2018 at 20:27

answered Jul 8, 2018 at 20:14

Ctznkane525

7,5453 gold badges19 silver badges41 bronze badges

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

2 Comments

Alex Herman Over a year ago

thank you very much for you answer! and good point - I haven't thought of that :)

olleo Over a year ago

This does not really work because you want to exclude the condition on vocabulary_id_ for 0 or NULL. This will give you wrong rows.

Collectives™ on Stack Overflow

PostgreSQL - conditionally exclude statement from WHERE clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related