PostgreSQL conditional where clause

Check if its null OR your condition like this:

WHERE {client_id} IS NULL OR sr.client_id = {client_id}

The WHERE clause evaluate as follow: If the variable is empty, then the WHERE clause evaluate to true, and therefore - no filter. If not, it continue to the next condition of the OR

If anyone faced with the psql operator does not exist: bigint = bytea issue, here is my workaround:

WHERE ({client_id} < 0 AND sr.client_id > {client_id}) OR sr.client_id = {client_id}

Please consider that, client_id generally cannot be negative so you can use that information for eleminating the operation cast issue.

My solution:

I use spring data jpa, native query.

Here is my repository interface signature.

@Query(... where (case when 0 in :itemIds then true else i.id in :itemIds end) ...)List<Item> getItems(@Param("itemIds) List<Long> itemIds)

Prior calling this method, I check if itemIds is null. If yes, I set value to 0L:

if(itemIds == null) {   itemIds = new ArrayList<Long>();   itemIds.add(0L);}itemRepo.getItems(itemIds);

My IDs starts from 1 so there is no case when ID = 0.