Operators in PostgreSQL are reserved words that perform the logical, arithmetic, comparison, and bitwise operations in the PostgreSQL statement’s WHERE clause.
The WHERE clause in PostgreSQL refers to statements used to specify conditions while the data is fetched from a single table or combined with multiple tables.
They specify conditions.
They serve as conjunctions between multiple statements.
Arithmetic operators
Bitwise operators
Logical operators
Comparison operators
The arithmetic operators are numerical symbols used to perform numerical operations between variables in PostgreSQL.
These operator functions are used just as they are used in simple mathematical calculations.
Operators | Function | Example(taking x as 2 and y as 3) |
+ | Adds values on either side of operator | "x + y" will give a result of 5 |
- | Subtracts right hand operand from left hand operand | "x - y" will give a result of -1 |
* | Multiplies values on either side of operator | "x * y" will give a result of 6 |
/ | Divides left hand operand by right hand operand | "x / y" will give a result of 0 |
% | Divides left hand operand by right hand operand and returns remainder | "x % y" will give a result of 2 |
^ | Calculates the power of the left hand operand to the right hand operand | "x ^ y" will give a result of 8 |
|/ | Finds the square root of an operand | |/25 will give a result of 5 |
||/ | Finds the cube root of an operand | ||/27 will give a result of 3 |
! | Finds the factorial of an operand | 5 ! will give a result of 120 |
!! | Finds the factorial(prefix) | !! 5 will give a result of 120 |
The code below demonstrates the examples of arithmetic operators illustrated in the table above.
SELECT num1,num2,(num1 + num2) as "2+3",(num1 - num2) as "2-3",(num1 * num2) as "2*3",(num1 / num2) as "2/3",(num1 % num2) as "2%3",(num1 ^ num2) as "2^3"from operators;
Comparison operators in PostgreSQL are symbols used to compare values on either side of the operator.
Operator | Function | Examples( taking variables x as 2 and y as 3) |
= | Checks if the values on either side of the operator are equal | (x = y) is not true |
< | Checks if the value of the left operand is less than that of the right operand | (x < y) is true |
> | Checks if the value of the left operand is greater than that of the right operand | (x > y) is not true |
!= | Checks if the values on either side of the operator are not equal | (x != y) is true |
<> | Checks if the values of two operands are equal or not | (x <> y) is true |
>= | Checks if the value of the left operand is greater than or equal to that of the right operand | (x >= y) is not true |
<= | Checks if the value of the right operand is greater than or equal to that of the left operand | (x <= y) is true |
The code below demonstrates the examples of comparison operators illustrated in the table above.
SELECT num1,num2,(num1 = num2) as "2=3",(num1 > num2) as "2>3",(num1 < num2) as "2<3",(num1 != num2) as "2!=3",(num1 <> num2) as "2<>3",(num1 >= num2) as "2>=3",(num1 <= num2) as "2<=3"from operators;
Logical operators in PostgreSQL are reserved words used to connect two or more PostgreSQL statements to generate a value independent of the meaning of the operator.
Operator | Function |
AND | Used in constructing multiple conditions in statements |
OR | Used to combine multiple conditions |
NOT | Negates the original meaning of the logical operator |
Bitwise operators in PostgreSQL are symbols used to perform bit-by-bit operations. Examples of bitwise operators in PostgreSQL are:
&|~