Postgres offers three main logical operators OR, AND, and NOT. The AND and OR operators combine several conditions to create more sophisticated queries that can extract the exact data you need. While the NOT operator negates the result of a boolean expression. For any database developer working with PostgreSQL, these operators are essential, as they allow you to make decisions based on the data stored in your database.

This blog post will explain the working of the following Postgres Logical operators with suitable examples.

• PostgreSQL Logical Operators
• What Does AND Operator Do in Postgres?
• What Does OR Operator Do in Postgres?
• What Does NOT Operator Do in Postgres?

So, let’s begin!

PostgreSQL Logical Operators

PostgreSQL has three main logical operators: AND, OR, and NOT. All these operators retrieve a boolean value and are hence also referred to as Boolean operators. Postgres also offers advanced logical operators such as BETWEEN, IN, LIKE, etc. These advanced logical operators allow you to perform more complex operations such as comparing values, checking whether a value falls within a range, finding patterns among your data, etc. In PostgreSQL, you can create highly customized and targeted queries using these operators.

What Does AND Operator Do in Postgres?

The logical AND joins two or more conditions and retrieves a boolean value as follows:

- Retrieves TRUE only if both boolean expressions are true.
- Retrieves FALSE if any of the given boolean expressions is false.
- Retrieves NULL if one value is TRUE and the second one is NULL.
- Retrieves FALSE if one value is FALSE and the second one is NULL.
- Retrieves NULL if both boolean expressions are NULL.

Syntax

Follow the below syntax to join different conditions using AND Operator:

SELECT col_list
FROM table_name
WHERE [cond_1] AND [cond_2]...

Example 1: How Do I Use the AND Operator in PostgreSQL?

We have already created a sample table named “example_tab” that contains the following boolean data:

Let’s utilize the AND operator to combine two columns, “val_1” and “val_2”:

SELECT val_1, val_2, val_1 AND val_2 AS result
FROM example_tab;

The output shows the truth table for the AND operator.

Example 2: Practical Implementation of the AND Operator in Postgres

Let’s learn the usage of the AND operator in a real-world scenario. We have a sample table named “article_details”, whose content is shown in the following snippet:

Suppose we want to fetch published articles between the interval '2021-08-10' and '2022-08-10'. For this purpose, we will execute the “SELECT *” as follows:

SELECT * FROM article_details
WHERE published_date > '2021-08-10' AND published_date < '2022-08-10';

The output snippet shows that the AND operator retrieves only those records that satisfy both conditions.

What Does OR Operator Do in Postgres?

The OR operator combines two or more conditions and retrieves a boolean value as follows:

- Retrieves TRUE if both boolean expressions/conditions are TRUE.
- Retrieves TRUE if any of the given boolean expressions are TRUE.
- Retrieves TRUE if one expression/condition is TRUE and the second one is NULL.
- Retrieves NULL if one expression/condition is FALSE and the second one is NULL.
- Retrieves NULL if both boolean expressions are NULL.

Syntax

Follow the below syntax to join different conditions using OR Operator:

SELECT col_list
FROM table_name
WHERE [cond_1] OR [cond_2]...

Example 1: How do I Use the OR Operator in PostgreSQL?

Let’s utilize the OR operator to combine two columns, “val_1” and “val_2”:

SELECT val_1, val_2, val_1 OR val_2 AS result
FROM example_tab;

The output snippet shows the truth table for the OR operator.

Example 2: Practical Implementation of the OR Operator in Postgres

Suppose we want to fetch the articles published after '2021-08-10' or article_id is less than 5. To do that, we will execute the “SELECT *” statement as follows:

SELECT * FROM article_details
WHERE published_date > '2021-10-10' OR article_id < 5;

The output snippet shows that the AND operator retrieves all those records that satisfy at least one condition.

What Does NOT Operator Do in Postgres?

The NOT operator negates a condition and retrieves the result as follows:

- Retrieves TRUE if the original result is FALSE.
- Retrieves FALSE if the original result is TRUE.
- Retrieves NULL if the original result is NULL.

Syntax

To use the NOT operator in Postgres, users must follow the below-provided syntax:

SELECT col_list
FROM table_name
WHERE NOT [condition]

Let’s implement it practically.

Example: Practical Implementation of the NOT Operator in Postgres

Suppose we want to fetch the articles whose id is greater than 5. For this purpose, we can use the NOT operator as follows:

SELECT * FROM article_details
WHERE NOT article_id < 5;

Here the original condition is “article_id < 5”; however, we will get opposing results because of the NOT operator, as shown in the following snippet:

The output shows that the NOT operator negates the results of the original condition.

Conclusion

PostgreSQL has three main logical operators: OR, AND, and NOT. All these operators retrieve a boolean value and are hence also referred to as Boolean operators. The AND and OR operators combine several conditions to create more sophisticated queries. While the NOT operator negates the result of a boolean expression. This write-up explained the usage of the logical operators in Postgres using practical examples.