Understanding PostgreSQL Filter and WHERE Clause

The terms FILTER and WHERE are used in PostgreSQL to further refine data selection, but they have different purposes and can be used in different contexts. Let’s explore both keywords in detail, understanding their unique use cases with code examples.

1. Setting up a database on Docker

Creating a database is time-consuming but with Docker, it becomes easy. To understand how to install Docker on Windows OS, you can view the video provided at this link. After completing the task, launch the terminal and execute the command below to configure and start postgresql.

-- Remember to change the password –
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=your_password --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Don’t forget to input your preferred password. Assuming everything goes smoothly, the postgresql database server will be operational on port number 5432, allowing you to connect using the Dbeaver GUI tool.

2. The WHERE Keyword

The WHERE keyword filters table rows based on specified conditions. It is a crucial clause in SQL that is used in SELECT, UPDATE, DELETE, and other SQL statements to specify the rows affected by the query. The format for this clause is the following:

SELECT column1, column2, ... FROM table_name WHERE condition;

2.1 Example

Consider a table named employees with the following structure:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100), 
  age INT, 
  department VARCHAR(50)
);

To find out the employees whose age is greater than 30, we can use the following WHERE clause:

SELECT 
  * 
FROM 
  employees 
WHERE 
  age > 30;

2.2 Code Breakdown

SELECT * FROM employees: This query selects all columns from the employees table.
WHERE age > 30: This filter in the select query fetches only those rows where the age column is greater than 30.

3. The FILTER Keyword

The FILTER keyword has a role in aggregate functions as it filters rows before the function is executed. It is commonly used in combination with GROUP BY clause to compute aggregates that meet specific criteria. The syntax is shown in the following manner:

SELECT aggregate_function(column) FROM table_name WHERE condition FILTER (WHERE filter_condition);

3.1 Example

Using the employees table created above, we will find the average age of employees who work in the ‘IT’ department:

SELECT 
  AVG(age) FILTER (
    WHERE 
      department = 'IT'
  ) AS avg_age_it 
FROM 
  employees;

3.2 Code Breakdown

SELECT AVG(age): This keyword calculates the average of the age column.
FILTER (WHERE department = 'IT'): This filters in the query fetches those rows where the department is ‘IT’ before calculating the average.
AS avg_age_it: This keyword gives an alias name to the resulting column, avg_age_it.

4. Using in Application Development

These days most people focus on using such queries in application development. Here is a snippet of how the above SQL queries can be used in Spring Java application development services.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeesDao extends JpaRepository<Employee, Integer> {

    @Query(nativeQuery = true, value = "SELECT * FROM employees WHERE age > ?1")
    List<Employee> getEmployeesByAge(int age);

    @Query(nativeQuery = true, value = "SELECT AVG(age) FILTER (WHERE department = 'IT') AS avg_age_it FROM employees;")
    int getAverageAgeIT();
}

In this example, we have created a Spring dao interface and used the native SQL approach to do the query operations.

5. Comparison and Conclusion

The PostgreSQL keywords WHERE and FILTER play important roles in data querying, yet they fulfill different functions:

WHERE clause is used to narrow down rows in standard queries, affecting which rows are selected or manipulated
FILTER clause is used with aggregate functions to define conditions that determine which rows are considered in the aggregate calculation.

Understanding the differences and applications of WHERE and FILTER keywords enhances the accuracy and efficiency of queries in PostgreSQL.