Question 254312 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to write parametrized SQL Native query where parameters could be Null?

I have a customer table having following colums – customerId-primary key, customer_name, food_preference, country, wallet_balance.

The requirement is to get all customer data with –

food_preference is vegetarian
country is"USA"
wallet_balance greater than 5000

So in the CustomerRepo in my Spring boot project the method I have written is

@Query(select * from customer where food_preference = ?1 and country = ?2 and walletBalance              
?3, nativeQuery = true)
List<CustomerEntity> getCustomersOnCriteria(Stirng foodPreference, String country, long 
walletBalance);

Now here the value of country could be null, In that case I would like to have all customers with all countries present in the db with condition wallet balance more than 5000, foodPreference equals Vegetarian. What modification I’ll have to do it in the Query? I am using Postgres db.

Tags:

2

Answers


  1. 0

    Something like this?

    (@country is null and wallet balance > 5000) OR country = @country
    Login or Signup to reply.
  2. 0

    The simple solution is to add the null possibility in an OR condition:

    select * from customer where food_preference = ?1 and 
(country = ?2 or ?2 is null) and
walletBalance = ?3

    Though this can lead to a suboptimal execution plan, specially in the case where the query with the country should lead to an index access while the query without it to a seq scan.

    In this case is more adviseable to define a second native query for the case without the country input and programaticaly check which query should be used

    select * from customer where food_preference = ?1 and 
walletBalance = ?2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search