PostgreSQL conditional where clause

Question

In my Ruby on Rails app I'm using blazer(https://github.com/ankane/blazer) and I have the following sql query:

SELECT *
FROM survey_results sr
LEFT JOIN clients c ON c.id = sr.client_id
WHERE sr.client_id = {client_id}

This query works really well. But I need to add conditional logic to check if client_id variable is present. If yes then I filter by this variable, if not then I not launching this where clause. How can I do it in PostgreSQL?

Answer 1

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

Answer 2

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.