PostgreSQL Operators

Welcome to The Coding College, where we simplify coding and database management! In this guide, we’ll explore PostgreSQL Operators, which are essential tools for performing operations on data stored in your database.

What Are Operators in PostgreSQL?

Operators in PostgreSQL are special symbols or keywords that allow you to perform actions such as comparisons, mathematical calculations, and logical operations on data.

Types of Operators

PostgreSQL offers various operators categorized as follows:

  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical Operators
  4. Bitwise Operators
  5. String Operators
  6. Other Specialized Operators

1. Arithmetic Operators

These operators perform basic mathematical operations.

OperatorDescriptionExampleResult
+Addition5 + 38
-Subtraction5 - 32
*Multiplication5 * 315
/Division10 / 25
%Modulus (Remainder)10 % 31

Example Query

SELECT 5 + 3 AS addition, 10 / 2 AS division;

2. Comparison Operators

Used to compare two values.

OperatorDescriptionExampleResult
=Equal to5 = 5TRUE
!= or <>Not equal to5 != 3TRUE
<Less than5 < 3FALSE
>Greater than5 > 3TRUE
<=Less than or equal to5 <= 5TRUE
>=Greater than or equal to5 >= 3TRUE

Example Query

SELECT * FROM students WHERE age >= 18;

3. Logical Operators

Used for combining multiple conditions.

OperatorDescriptionExampleResult
ANDLogical ANDTRUE AND FALSEFALSE
ORLogical ORTRUE OR FALSETRUE
NOTLogical NOTNOT TRUEFALSE

Example Query

SELECT * FROM students WHERE age >= 18 AND age <= 25;

4. Bitwise Operators

Perform operations at the binary level.

OperatorDescriptionExampleResult
&Bitwise AND5 & 31
``Bitwise OR`5
#Bitwise XOR5 # 36
<<Bitwise shift left5 << 110
>>Bitwise shift right5 >> 12

5. String Operators

Used for manipulating string data.

OperatorDescriptionExampleResult
``Concatenation
LIKEPattern matching'Hello' LIKE 'H%'TRUE
ILIKECase-insensitive pattern matching'hello' ILIKE 'H%'TRUE
~Regex match'abc' ~ 'a.c'TRUE

6. Other Specialized Operators

PostgreSQL-specific operators include:

  • Array Operators Operator Description Example Result @> Contains ARRAY[1, 2] @> ARRAY[1] TRUE <@ Is contained by ARRAY[1] <@ ARRAY[1, 2] TRUE
  • JSON Operators Operator Description Example Result -> Get JSON object field json_col->'name' "John" ->> Get JSON text value json_col->>'name' John

Common Use Cases

Example 1: Filtering Records

SELECT * FROM students WHERE age > 18 AND name LIKE 'A%';

Example 2: Calculating Grades

SELECT name, (marks / total_marks) * 100 AS percentage FROM students;

Example 3: Joining Tables with Conditions

SELECT s.name, c.course_name 
FROM students s 
JOIN courses c 
ON s.id = c.student_id 
WHERE c.duration > 3;

Learn More at The Coding College

For more in-depth tutorials and practical examples, visit The Coding College. Our PostgreSQL guides are designed to enhance your database skills and ensure you follow Google’s E-E-A-T principles for reliable, expert content.

Conclusion

PostgreSQL operators are versatile tools for handling data efficiently. By mastering these operators, you can build powerful queries and manage your database with ease.

Have any questions? Drop a comment or connect with The Coding College for more personalized help!

Leave a Comment