How to Use Data Filters: Simple to Advanced Filtering Options in PostgreSQL

You may not always need to retrieve all the rows from a table. While you can use the WHERE clause to filter rows, PostgreSQL provides many more operators than just AND and OR.

The WHERE clause can be used with the SELECT statement to filter data based on one or more conditions. PostgreSQL evaluates the condition for each row and returns only the rows for which the condition evaluates TRUE.

1. What is a WHERE Clause in PostgreSQL?

The basic syntax of a SELECT query with a WHERE clause is like this:

SELECT column1, column2
FROM table_name
WHERE condition;

Now, let's consider this one example to do all the operators in a SELECT statement. Suppose we have this employee table built in our database:

2. Using the = Operator (Check Equality):

This is one of the most commonly used operators in a SELECT statement. As you might know, the = operator is used when you want to get rows where a column has a specific value.

Example : 
SELECT *
FROM employees
WHERE department = 'HR';

This returns employees whose department is exactly HR. We will not be using the quotes used with HR in case of numbers; in that case, it would be like: WHERE salary = 50000;

3. Using <> for Not Equal:

The <> operator can be used to find records where one value is not equal to another value. For example,

SELECT *
FROM employees
WHERE department <> 'IT';

This returns all employees who are not in the IT department. PostgreSQL also has the != operator that does the same operation.

4. Using > to Find Greater Values and < for smaller values

The > operator select rows where a value is greater than the specified value. And the < operator select records where a value is less than a specified value.

Example of < can be : 
SELECT *
FROM employees
WHERE age < 30;
This returns employees younger than 30.

Examples of > can be:

SELECT *
FROM employees
WHERE salary > 55000;

5. Using >= and <=

Postgres also provides operators that compare the specified value, for example:

SELECT *
FROM employees
WHERE salary >= 55000;

This return employees earning exactly 55000 as well as those earning more.

6. Using IN to Match Multiple Values:

Suppose you want employees from several departments, and instead of writing the query like this with many OR conditions:

SELECT *
FROM employees
WHERE department = 'IT'
   OR department = 'HR'
   OR department = 'Sales';

You can write:

SELECT *
FROM employees
WHERE department IN ('IT', 'HR', 'Sales');

The IN operator checks whether a value matches any value in a specified list.

You can also use NOT IN to get results that exclude the added values, for example:

SELECT *
FROM employees
WHERE department NOT IN ('IT', 'HR');

This excludes employees from IT and HR.

You can use IN with a subquery like:

SELECT *
FROM employees
WHERE department IN (
    SELECT department
    FROM departments
    WHERE active = true
);

In this case, PostgreSQL uses the results of the subquery as the values for the IN condition.

7. Using BETWEEN for Ranges

The BETWEEN operator is useful when you want to filter values within a range. For example, to find employees whose salary is between 50000 and 70000, you can use

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

BETWEEN is inclusive, meaning both boundary values are included. BETWEEN can also be used with date values as well.

8. Using LIKE for Pattern Matching

The LIKE operator is used if you don't know the exact value or want to search for a particular text pattern. For example:

SELECT *
FROM employees
WHERE name LIKE 'A%';

This finds the names that start with A. postgres uses % and _ as wildcard characters with LIKE.

% Wildcard

% represents zero or more characters. For example

WHERE name LIKE 'A%'

Matches:

Alice
Andrew
Alex

You can also search for names ending with some particular character:

WHERE name LIKE '%a';

Or names containing a particular sequence:

WHERE name LIKE '%li%';

_ Wildcard

The underscore _ represents exactly one character. For example:

WHERE name LIKE 'A_i%'

Can match values such as:

Alice
Alicia

Depending on the characters in the value, PostgreSQL also provides ILIKE to perform case-insensitive pattern matching. Suppose you are not sure about the case you have stored in the DB; you can use ILIKE so that:

SELECT *
FROM employees
WHERE name ILIKE 'a%';

This can match names such as:

Alice
Andrew
alex

9. Using ANY in Queries

ANY can be useful when you compare a value with multiple values returned by a subquery:

SELECT *
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'HR'
);

This returns employees whose salary is greater than at least one salary returned by the subquery.

10. using ALL with query:

So, that you can compare against all the values returned by the subquery, for example:

SELECT *
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'HR'
);

Here, the salary must be greater than every salary returned by the subquery.

11. Use of NOT in Queries:

NOT will reverse a condition:

SELECT *
FROM employees
WHERE NOT department = 'IT';

It can also be combined with other conditions like:

SELECT *
FROM employees
WHERE NOT (salary > 50000 AND age < 30);

12. Use of EXISTS:

EXISTS can check whether a subquery returns at least one row:

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.name = e.department
);

This is really useful when you are filtering rows based on the existence of related records.

13. Use of IS DISTINCT FROM

This is a PostgreSQL feature a bit different because it handles NULLs differently from normal equality checks:

SELECT *
FROM employees
WHERE department IS DISTINCT FROM 'IT';

Unlike <>, IS DISTINCT FROM gives a definite true/false result even when one of the values is NULL.

PostgreSQL has many flexible options to filter data other than using the normal = and > operators; all these are helpful when you have to get the data in a different manner than what you normally do with all basic operators. Getting detailed knowledge of this will help you write better queries in your business scenarios and reporting.