PostgreSQL Operators

PostgreSQL Operators

Operators are reserved words or characters used primarily in a WHERE clause. They perform operations such as comparisons and arithmetic logic.

These operations are used to specify conditions in an SQL statement. They serve as the foundation for manipulating and querying data.

What is an Operator?

An operator is a symbol specifying an action or calculation. PostgreSQL provides several types of operators to handle different data types.

You use them to filter data accurately and effectively in your queries. Without operators, databases would just return raw, unfiltered lists of records.

Arithmetic Operators

Arithmetic operators perform mathematical operations on numeric data types. The addition operator (+) adds values on either side.

The subtraction operator (-) subtracts the right operand from the left. The multiplication operator (*) multiplies values together.

The division operator (/) divides the left operand by the right. The modulo operator (%) divides the left operand and returns the remainder.

SELECT 10 + 5 AS Addition;
SELECT 10 - 5 AS Subtraction;
SELECT 10 * 5 AS Multiplication;
SELECT 10 / 5 AS Division;
SELECT 10 % 3 AS Modulo;

Comparison Operators

Comparison operators compare two expressions and return a boolean value. They output either TRUE, FALSE, or NULL depending on the match.

The equal operator (=) checks if the values of two operands are identical. The not equal operators (!= or <>) check if values are entirely different.

The greater than operator (>) checks if the left value is strictly greater. The less than operator (<) checks if the left value is strictly lesser.

SELECT * FROM products WHERE price = 100;
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price <= 200;

Logical Operators

Logical operators are used to combine multiple conditions into a single statement. The AND operator returns true if all combined conditions are true.

The OR operator returns true if any one of the combined conditions is true. The NOT operator reverses the expected value of any boolean result.

SELECT * FROM employees WHERE age > 30 AND department = 'Sales';
SELECT * FROM employees WHERE city = 'London' OR city = 'Paris';

Summary

Operators are fundamental to writing dynamic SQL queries in PostgreSQL. They allow you to perform calculations, compare values, and construct logic.