AND & OR Clauses in PostgreSQL: The Dynamic Duo of Data Filtering

Introduction

When querying databases, you often need to retrieve records that meet more than one condition. Here’s where the AND and OR operators come in, acting like the superhero duo of PostgreSQL’s query language. With these operators, you can get as granular as you like with your conditions, combining multiple together in various ways. Let’s get to know each of them and understand how to use them in combination to save the day (or at least your query).

Basic Syntax and Usage

The basic structure for using AND and OR in a WHERE clause is straightforward:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 ...;

OR

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 ...;

Examples with tv_series Table

For instance, if you want to fetch TV series that belong to the ‘Fantasy’ genre and are still running, your query would look like this:

SELECT name, genre, is_running
FROM tv_series
WHERE genre = 'Fantasy' AND is_running = true;

Want to find series that are either ‘Fantasy’ or still running? Easy peasy:

SELECT name, genre, is_running
FROM tv_series
WHERE genre = 'Fantasy' OR is_running = true;

Combining AND and OR

Sometimes you need to get fancy and combine both AND and OR. Let’s say you want all running TV series in the ‘Fantasy’ or ‘Crime’ genre. Wrap the OR conditions in parentheses to keep things organized:

SELECT name, genre, is_running
FROM tv_series
WHERE is_running = true AND (genre = 'Fantasy' OR genre = 'Crime');

Common Errors and Solutions

Error Message:

ERROR:  operator does not exist: boolean = integer

Cause:
You tried to compare a boolean field with an integer or some other incompatible data type.

Solution:
Ensure that the data types of your conditions match.

Pro Tips

• When combining AND and OR, always use parentheses to ensure the conditions are evaluated in the order you intend. This eliminates ambiguity and prevents unexpected results.

• Be cautious with OR; it can slow down queries if used excessively or inefficiently because PostgreSQL has to evaluate each OR condition separately.

So, that’s how you become a pro at using AND and OR in PostgreSQL. Got more questions or scenarios you’re curious about? Feel free to drop a comment. Happy querying!