PostgreSQL Operators

Operator Types

+, -, *, /, %

=, !=, <, >, <=, >=

AND, OR, NOT

||, LIKE, ILIKE

🔹 Arithmetic Operators

Arithmetic operators perform mathematical calculations on numeric values. You can add, subtract, multiply, divide, and find remainders. These operators are useful for calculating totals, discounts, percentages, and other numeric transformations directly within your SQL queries for efficient data processing.

-- Addition
SELECT price + 10 AS new_price FROM products;

-- Multiplication (calculate tax)
SELECT price, price * 0.08 AS tax FROM products;

-- Division
SELECT total_amount / quantity AS unit_price FROM orders;

-- Modulo (remainder)
SELECT id, id % 2 AS is_odd FROM products;

price  | tax
-------+------
100.00 | 8.00
50.00  | 4.00

🔹 Comparison Operators

Comparison operators test relationships between values and return true or false. Equal to, not equal to, greater than, less than, and their combinations allow you to filter data precisely. These operators form the backbone of WHERE clauses and conditional logic in database queries.

-- Equal to
SELECT * FROM products WHERE price = 99.99;

-- Not equal to
SELECT * FROM products WHERE category != 'Electronics';

-- Greater than
SELECT * FROM employees WHERE salary > 50000;

-- Less than or equal to
SELECT * FROM products WHERE stock <= 10;

-- Between range
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

name     | salary
---------+--------
John     | 75000
Sarah    | 65000
Mike     | 80000

🔹 Logical Operators

Logical operators combine multiple conditions in WHERE clauses. AND requires all conditions to be true, OR requires at least one condition to be true, and NOT negates a condition. These operators enable complex filtering logic to retrieve exactly the data you need from your database.

-- AND operator (both conditions must be true)
SELECT * FROM products 
WHERE price > 50 AND category = 'Electronics';

-- OR operator (at least one condition must be true)
SELECT * FROM products 
WHERE category = 'Electronics' OR category = 'Computers';

-- NOT operator (negates condition)
SELECT * FROM products 
WHERE NOT category = 'Electronics';

-- Combining operators
SELECT * FROM products 
WHERE (price > 100 OR stock < 5) AND category = 'Electronics';

product_name | price  | category
-------------+--------+-------------
Laptop       | 999.99 | Electronics
Tablet       | 599.99 | Electronics

🔹 String Operators

String operators manipulate and compare text data. The concatenation operator joins strings together. LIKE performs pattern matching with wildcards, while ILIKE does case-insensitive matching. These operators are essential for searching, formatting, and combining text data in your database queries effectively.

-- Concatenation
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- LIKE (case-sensitive pattern matching)
SELECT * FROM products WHERE name LIKE 'Lap%';

-- ILIKE (case-insensitive pattern matching)
SELECT * FROM products WHERE name ILIKE '%phone%';

-- Combining strings with data
SELECT 'Product: ' || product_name || ' - $' || price AS description
FROM products;

full_name
--------------
John Smith
Sarah Jones

🔹 NULL Operators

NULL represents missing or unknown data in PostgreSQL. Use IS NULL to find rows with missing values and IS NOT NULL to find rows with data. Regular comparison operators don't work with NULL because NULL is not equal to anything, including itself.

-- Check for NULL values
SELECT * FROM employees WHERE phone IS NULL;

-- Check for non-NULL values
SELECT * FROM employees WHERE email IS NOT NULL;

-- COALESCE (return first non-NULL value)
SELECT name, COALESCE(phone, 'No phone') AS contact 
FROM employees;

name  | contact
------+------------
John  | 555-1234
Sarah | No phone

🔹 IN and EXISTS Operators

IN checks if a value matches any value in a list or subquery. EXISTS tests whether a subquery returns any rows. These operators simplify complex queries by replacing multiple OR conditions and enabling efficient checks for related data in other tables without joining them.

-- IN operator with list
SELECT * FROM products 
WHERE category IN ('Electronics', 'Computers', 'Phones');

-- IN operator with subquery
SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

-- EXISTS operator
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

product_name | category
-------------+-------------
Laptop       | Electronics
Mouse        | Computers
iPhone       | Phones