Dynamic where condition PostgreSQL

Question

I am building a CRUD application that allows the user to input some search criteria and get the documents corresponding to those criteria. Unfortunately i have some difficulties in creating a query in postgres that uses different conditions in the where part, based on the input sent by the user.

For example if the user set as search criteria only the document number the query would be defined like this:

select * from document where document_num = "value1"

On the other hand if the user gave two criteria the query would be set up like this:

select * from document where document_num = "value1" and reg_date = "value2"

How can i set up a query that is valid for all the cases? Looking in other threads i saw as a possible solution using coalesce in the where part:

document_num = coalesce("value1", document_num)

The problem with this approach is that when no value is provided postgres converts the condition to document_num IS NOT NULL which is not what i need (my goal is to set the condition to always true).

Thanks in advance

Is there any default values ? Are you using query inside a stored procedure or function? — Jay Godhani
– Jay Godhani, Commented Apr 24, 2022 at 10:44

Jay Godhani · Accepted Answer · 2022-04-24 11:59:46Z

4

So the solution by @D-shih will work if you have a default value and you can also use COALESCE as below.

SELECT *
FROM document
WHERE document_num = COALESCE("value1", default_value)
AND reg_date = COALESCE("value2", default_value);

If you don't have default values then you can create your query using CASE WHEN(here I am supposing you have some variables from which you will determine which conditions to apply like when to apply document_num or when to apply reg_date or when to apply both). Giving a little example below.

SELECT *
FROM document
WHERE
(
    CASE
        WHEN "value1" IS NOT NULL THEN document_num = "value1"
        ELSE TRUE
    END
) 
AND (
    CASE
        WHEN "value2" IS NOT NULL THEN reg_date = "value2"
        ELSE TRUE
    END
)

You can read more how to use CASE WHEN here.

edited Apr 24, 2022 at 11:59

answered Apr 24, 2022 at 11:28

Jay Godhani

3962 silver badges12 bronze badges

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

1 Comment

pydev95 Over a year ago

Thank you very much. I too thought about using the case statements but i was not sure how to handle the "AND" part when one or more conditions are missing. I tried your solution and it works perfectly!

D-Shih · Accepted Answer · 2022-04-24 10:18:28Z

1

If I understand correctly, you can try to pass the user input value by parameter.

parameter default value might design that the user can't pass if the user didn't want to use the parameter it will use the default value.

we can use OR to judge whether use parameter otherwise ignores that.

SELECT * 
FROM document 
WHERE (document_num = :value1 OR :value1 = [default value]) 
AND (reg_date = :value2 OR :value2 = [default value])

answered Apr 24, 2022 at 10:18

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Collectives™ on Stack Overflow

Dynamic where condition PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related