Operators in PostgreSQL

PostgreSQL supports a variety of operators that allow you to perform different types of operations on data. These operators can be used in conditions, expressions, and queries to manipulate and compare data.

1. Comparison Operators

Comparison operators are used to compare values in queries.

• =: Equal to

  SELECT * FROM employees WHERE salary = 50000;
  

  ✅ Finds employees with a salary of 50,000.

• != or <>: Not equal to

  SELECT * FROM employees WHERE department != 'HR';
  

  ✅ Finds employees not in the HR department.

• >: Greater than

  SELECT * FROM employees WHERE salary > 60000;
  

  ✅ Finds employees with a salary greater than 60,000.

• <: Less than

  SELECT * FROM employees WHERE salary < 50000;
  

  ✅ Finds employees with a salary less than 50,000.

• >=: Greater than or equal to

  SELECT * FROM employees WHERE salary >= 40000;
  

  ✅ Finds employees with a salary greater than or equal to 40,000.

• <=: Less than or equal to

  SELECT * FROM employees WHERE salary <= 80000;
  

  ✅ Finds employees with a salary less than or equal to 80,000.

2. Logical Operators

Logical operators are used to combine multiple conditions.

• AND: True if both conditions are true

  SELECT * FROM employees WHERE department = 'IT' AND salary > 50000;
  

  ✅ Finds employees in the IT department with a salary greater than 50,000.

• OR: True if at least one condition is true

  SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';
  

  ✅ Finds employees in either the IT or HR department.

• NOT: Negates a condition

  SELECT * FROM employees WHERE NOT department = 'Sales';
  

  ✅ Finds employees not in the Sales department.

3. String Operators

String operators are used to perform operations on text data.

• ||: Concatenation

  SELECT first_name || ' ' || last_name AS full_name FROM employees;
  

  ✅ Concatenates the first name and last name to create a full name.

• LIKE: Pattern matching

  SELECT * FROM employees WHERE first_name LIKE 'J%';
  

  ✅ Finds employees whose first name starts with 'J'.

• ILIKE: Case-insensitive pattern matching

  SELECT * FROM employees WHERE first_name ILIKE 'j%';
  

  ✅ Finds employees whose first name starts with 'j' (case insensitive).

• SIMILAR TO: Pattern matching with regular expressions

  SELECT * FROM employees WHERE first_name SIMILAR TO 'J[aeiou]%';
  

  ✅ Finds employees whose first name starts with 'J' followed by a vowel.

4. Arithmetic Operators

Arithmetic operators are used for mathematical calculations.

• +: Addition

  SELECT salary + 1000 FROM employees;
  

  ✅ Increases the salary by 1000.

• -: Subtractio

  SELECT salary - 500 FROM employees;
  

  ✅ Decreases the salary by 500.

• *: Multiplication

  SELECT salary * 1.10 FROM employees;
  

  ✅ Increases the salary by 10%.

• /: Division

  SELECT salary / 12 FROM employees;
  

  ✅ Calculates the monthly salary from an annual salary.

• %: Modulo (remainder of division)

  SELECT salary % 2 FROM employees;
  

  ✅ Returns the remainder when dividing the salary by 2.

5. Range Operators

Range operators are used to check if a value falls within a specified range.

• BETWEEN: Checks if a value is within a range

  SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
  

  ✅ Finds employees with a salary between 40,000 and 80,000.

• NOT BETWEEN: Checks if a value is not within a range

  SELECT * FROM employees WHERE salary NOT BETWEEN 40000 AND 80000;
  

  ✅ Finds employees with a salary outside the range of 40,000 to 80,000.

6. Null Operators

Operators to handle NULL values.

• IS NULL: Checks if a value is NULL

  SELECT * FROM employees WHERE manager_id IS NULL;
  

  ✅ Finds employees with no manager (i.e., manager_id is NULL).

• IS NOT NULL: Checks if a value is not NULL

  SELECT * FROM employees WHERE manager_id IS NOT NULL;
  

  ✅ Finds employees who have a manager (i.e., manager_id is not NULL).

7. IN and EXISTS Operators

• IN: Checks if a value is in a list of values

  SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');
  

  ✅ Finds employees in the IT, HR, or Finance department.

• EXISTS: Checks if a subquery returns any rows

  SELECT * FROM employees eWHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.location = 'New York');
  

  ✅ Finds employees whose department is located in New York.

8. JSON and Array Operators

PostgreSQL provides operators to work with JSON and arrays.

• ->: Extracts a JSON object field by key

  SELECT data->'name' FROM users;
  

  ✅ Retrieves the name field from a JSON column data.

• ->>: Extracts a JSON object field by key as text

  SELECT data->>'name' FROM users;
  

  ✅ Retrieves the name field from a JSON column data as text.

• @>: Checks if a JSON contains another JSON

  SELECT * FROM users WHERE data @> '{"status": "active"}';
  

  ✅ Finds users with a status of active.

• array[]: Checks if an array contains an element

  SELECT * FROM products WHERE tags @> ARRAY['electronics'];
  

  ✅ Finds products that have 'electronics' as a tag.

Performance Considerations

• Use indexes on columns involved in filtering and comparisons to improve performance.
• Optimize queries using appropriate operators for large datasets.