PostgreSQL Operators

PostgreSQL Operators

Operators in PostgreSQL are symbols or keywords used to perform operations on data in SQL queries. They are mainly used in SELECT, WHERE, UPDATE, and DELETE statements.

1️⃣ Arithmetic Operators

Used for mathematical calculations.

2️⃣ Comparison Operators

Used to compare values.

3️⃣ Logical Operators

Used to combine conditions.

4️⃣ LIKE / ILIKE Operators

Used for pattern matching.

Case-insensitive:

5️⃣ IN Operator

Matches values in a list.

6️⃣ BETWEEN Operator

Checks value in a range.

7️⃣ IS NULL / IS NOT NULL

Used to check NULL values.

8️⃣ EXISTS Operator

Checks if a subquery returns any row.

9️⃣ ANY / ALL Operators

🔟 Bitwise Operators

1️⃣1️⃣ String Operators

1️⃣2️⃣ Set Operators

Used to combine query results.

1️⃣3️⃣ Operator Precedence (High → Low)

1. Arithmetic (*, /)

2. Arithmetic (+, -)

3. Comparison (=, >, <)

4. NOT

5. AND

6. OR

Use parentheses to control order:

1️⃣4️⃣ Best Practices

✔ Use parentheses for clarity
✔ Prefer ILIKE for case-insensitive search
✔ Avoid unnecessary complex expressions
✔ Index columns used with operators