Using java Boolean class as boolean type in SQL native query

Question

I have a use-case of getting the records based on the active flag in Postgres db.

In the rest api,

1. If active param is passed as active=true, get the active records
2. If active param is passed as active=false get the inactive records
3. If active param is not passed get both the records

In api the 'active' flag as Boolean class. Using native query for querying, but unable to form the query

select * from records r where r.country='India' and (IF(active IS NULL ) BEGIN '' END ELSE BEGIN r.active = :active END)

But query is failing..

Answer 1

SQL does not support "IF" constructs, moreover you are attempting to create a transaction (begin...) within the select statement. It does not work that way. I suggest you spend a little with the Postgres documentation manual beginning with SQL syntax the or a beginning SQL tutorial/book. As for this in particular it is a pretty basic query:

   select r.* 
     from records r
    where is_active_parameter is null
       or r.is_active = is_active_parameter;

Passing the parameter depends upon your interface platform (psql, dbeaver, pgAdmin, ...). I created a demonstration fiddle. It defines a Postgres SQL function (to facilitate passing a parameter, but the query runs standalone equally as well). You didn't provide table definition for records so I just made my own.