icon

Filtering Data In PostgreSQL

Head of Development
Filtering Data In PostgreSQL
  • Author:
  • Max Levi

    Head of Development

Articles Table

Before we begin, let's introduce the articles table and its properties. This table will be our dataset containing the sample data so we will be able to filter the entities based on some criterias. 

 

Column 

Data Type 

id 

integer 

title 

text 

author 

text 

published 

date 

views 

integer 

 

We've also inserted some sample data into the Articles table. This table contains both string and numeric values. So you can understand how to filter different data types in PostgreSQL. 

 

 

id

 

title

 

author

 

published

 

views

 

1

 

Introduction to SQL

 

John Doe

 

2022-05-10

 

500

 

2

 

Data Analysis Tips

 

Jane Smith

 

2023-01-15

 

800

 

3

 

Advanced Python

 

John Doe

 

2022-11-02

 

1200

4

PostgreSQL Basics

Mark Johnson

2023-04-20

300

5

Web Development 101

Jane Smith

2023-02-28

1000

 

WHERE Clause

The WHERE statement in SQL is a fundamental clause that allows us to include only records that satisfy one or multiple conditions from the entire dataset in the query result. The basic syntax of the where clause is as follows:

 

```sql

SELECT column1, column2, ...

FROM table_name WHERE condition;

```

 

The condition part represents a boolean expression returned as true or false for each row in the table. If the condition is true, the row is included in the result set; otherwise, it is excluded. The WHERE clause supports various operators:

– Comparison Operators: These operators are used to compare values. The comparison operators in PostgreSQL include =, <> (not equal), <, >, <=, >= and BETWEEN.

– Logical Operators: These operators are used to combine multiple conditions. The logical operators in PostgreSQL include AND, OR and NOT. 4 PostgreSQL Basics Mark Johnson 2023-04-20 300 5 Web Development 101 Jane Smith 2023-02-28 1000

– Pattern Matching Operators: These operators are used to find a pattern in string values. The pattern matching operators in PostgreSQL include LIKE, ILIKE, SIMILAR TO and regular expressions (~ and ~*).

– Array Operators: PostgreSQL allows you to filter data based on array values. The array operators include = ANY, <> ALL, IN, and NOT IN.

– NULL Comparison Operators: These operators are used to check for NULL values. The NULL comparison operators in PostgreSQL include IS NULL and IS NOT NULL. 

 

Examples of Where Clause

Let's explore some practical examples to understand how to filter data using the PostgreSQL where clause.

Basic Filtering The first example is for equality check. Consider that, you want to select posts of a specific author from the articles table, In this case your select query is as follows:

 

```sql

SELECT * FROM articles WHERE author = 'John Doe';

```

This query will return all articles authored by 'John Doe' as shown below 

id

title

author

published

views

 

1

 

Introduction to SQL

 

John Doe

 

2022-05-10

 

500

 

3

 

Advanced Python

 

John Doe

 

2022-11-02

 

1200

 

Multiple Conditional Filtering

If you want to filter data based on multiple conditions, you can use logical operators. For example, with the following query, you can get 1000+ viewed posts by John Doe from the articles table.

 

```sql

SELECT * FROM articles WHERE author = 'John Doe' AND views > 1000;

```

 

This query including multiple condition will return the following row.

 

id

title

author

published

views

 

3

 

Advanced Python

 

John Doe

 

2022-11-02

 

1200

 

Pattern Matching

You can filter data based on patterns. The most popular one is LIKE operator used often with wildcard characters such as % and _ to match a pattern. The % character represents zero or more characters, while the _ character represents a single character.

For instance, if we want to retrieve articles with titles containing the word 'SQL', we can use the following query:

 

```sql

SELECT * FROM articles WHERE title LIKE '%SQL%';

```

Here is the result:

 

id

title

author

published

views

 

1

 

Introduction to SQL

 

John Doe

 

2022-05-10

 

500

 

4

 

PostgreSQL Basics

 

Mark Johnson

 

2023-04-20

 

300

 

PostgreSQL also allows creating more complex patterns using the regular expressions. The ~ and ~* operators can be used in the WHERE clause to filter data based on specific patterns. Here's an example:

 

```sql

SELECT * FROM articles WHERE title ~ '[0-9]+';

```

This query will return all posts with the title that contains at least one digit.

 

 

id

 

title

 

author

 

published

 

views

 

5

 

Web Development 101

 

Jane Smith

 

2023-02-28

 

1000

 

Filtering Null Values

Null values in a dataset represents an unknown or missing value, it can behaves differently than other values when filtered. PostgreSQL have IS NULL and NULL operators to check if a column contains a NULL value. For example, to filter rows where the title column is NULL, you can use:

 

```sql

SELECT * FROM articles WHERE title IS NULL;

```

Subqueries

 

Subqueries allows building nested queries in PostgreSQL. With the help of the IN operator, we can filter the data in a table associated to other tables in the database. Since we have a single articles table, let's give a different example.

 

```sql SELECT *

FROM employees

WHERE department IN (SELECT department FROM departments

WHERE location = 'New York');

``

In this query, the subquery retrieves departments located in New York and the main query fetches employees who belong to those departments.

 

Group by and Having

The GROUP BY clause allows filtering by grouping rows of data based on one or more columns. The general syntax for the GROUP BY clause is as follows:

 

```sql

SELECT column1, column2, ..., FROM table

GROUP BY column1, column2, ...

```

 

Let's say we want to calculate the total number of views for each author. We can use the GROUP BY clause to group the rows by the author column and then use the SUM() function to calculate the total views for each author.

 

```sql

SELECT author, SUM(views) AS total_views FROM articles

GROUP BY author;

``

 

The result will show the total number of views for each author:

 

author

total_views

 

John Doe

 

1700

 

Jane Smith

 

1800

 

Mark Johnson

 

300

 

The HAVING clause is used in conjunction with the GROUP BY clause to filter the result set based on a condition applied. It allows filtering the grouped data after the GROUP BY operation has been performed.

 

For example, let's say we want to retrieve the authors who have a total view count greater than 1000. We can use the HAVING clause to filter the results based on the aggregated total_views column

 

```sql

SELECT author, SUM(views) AS total_views FROM articles

GROUP BY author

HAVING SUM(views) > 1000;

```

 

The result will show the authors with a total view count greater than 1000:

 

author

total_views

 

John Doe

 

1700

 

Jane Smith

 

1800

 

We can also control the number of rows returned by a query and to specify the starting point for the result with the LIMIT and OFFSET clauses.

 

Suppose we want to retrieve the top 2 authors based on the total views of their articles, skipping the first author. Here's an example query:

 

```sql

 

SELECT author, SUM(views) AS total_views FROM articles

GROUP BY author

 

ORDER BY total_views DESC LIMIT 2 OFFSET 1;

```


The query will return the following result including the second and third authors based on the sorted total_views.

 

author

total_views

Jane Smith

1800

John Doe

1700

 

Conclusion

Filtering is an important operation in databases to reach a specific part of a dataset based on some criteria. For this purpose, PostgreSQL provides clauses and operators. In this post, we discussed how to filter data in a PostgreSQL database using the where, group by and having clauses.

If you have problems with integrating PostreSQL in your project, contact Sunny Security Labs - we can find a proper specialist for your project.

 

 

Request a quote
Book a consultation