We use the Pagila database, a sample DVD rental database, to illustrate the various examples for demonstration purposes.
Requirements:
- Installed PostgreSQL on your machine.
- The Pagila sample database that is loaded into your PostgreSQL server.
Let us proceed.
PostgreSQL Not Equal Operator
In PostgreSQL, the NOT EQUAL operator is represented by the “<>” symbols. This operator compares two values and retrieves the rows where the value is not equal. We can apply this operator to various PostgreSQL operators such as numeric, character types, date, time data types, etc.
PostgreSQL Not Equal Examples
Explore the examples to understand how the NOT EQUAL operator works in a PostgreSQL database. As stated, we use the Pagila database for demonstration purposes. However, feel free to use any database available at your disposal.
Example 1: Fetch the Films that Are Not Rated PG
An example of the NOT EQUAL operator is to retrieve all the fims from the Pagila database that are not rated PG.
We can run the query as follows:
FROM film
WHERE rating <> 'PG';
The given query retrieves all films from the film table with a rating that is different from “PG”. The “<>” operator compares the rating column with the “PG” string and returns the rows where the values are not equal.
Example 2: Fetch the Payments that Are Not Equal to a Given Value
In the payment table, you can use the not equal operator to fetch the payments that are not equal to a given value. For example, the query is as follows:
FROM payment
WHERE amount <> 5.99;
In this example, we retrieve all the payment records from the payment table where the amount is not equal to 5.99.
Example 3: Using the Not Equal Operator with the Update Statement
We can also use the NOT EQUAL operator in an UPDATE statement to modify the rows that do not match the specified condition.
Consider the following example:
SET address2 = 'N/A'
WHERE address <> '123 Main St';
The given query updates the address2 column from the address table and sets its values to N/A for all the rows where the address is not equal to “123 Main St”. The operator compares the address with the provided string. If they are not equal, update the address2 column as necessary.
Example 4: Using the Not Equal Operator with the Delete Statements
Similarly, we can also use the NOT EQUAL operator to remove the records that do not match the specified conditions.
NOTE: This is a destructive operation. Ensure to backup your data and that the target condition is to your criteria.
An example is as follows:
WHERE release_year <> 2006;
The given query deletes all rows from the film table where the release_year is not equal to 2006.
Example 5: Not Equal Operator with Null Values
When using the NOT EQUAL operator with NULL values, it can be a little confusing. In PostgreSQL, when comparing a value with a NULL value, the NOT EQUAL operator always returns a NULL, not TRUE, or FALSE. We can use the IS NOT NULL operator instead to handle the NULL operators.
Consider the following example:
FROM film
WHERE description <> 'NULL' OR description IS NOT NULL;
As you can see, the given query selects all the films from the film table where the description is not set to NULL or the NULL string. Since we are comparing with a NULL value, the NOT EQUAL operator returns NULL. Hence, we need to add the IS NOT NULL conditional statement to exclude the NULL values.
Example 6: Using the NOT EQUAL Operator with Multiple Conditions
We can also combine the NOT EQUAL operator with other logical operators to create more complex conditions.
An example is as follows:
FROM film
WHERE rental_rate <> 4.99 AND release_year = 2006;
This should return all the films where the rental_rate is not equal to 4.99 and the release year is equal to 2006.
There you have it!
Conclusion
We walked you through how you can work with the NOT EQUAL operator in PostgreSQL to perform the actions on the rows that do not match a specific condition. This is very useful when you need to negate a given result.
