Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

communitycreator
postgresql

What are operators in PostgreSQL?

Amengor-Kwapong Angela

Overview

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.

Functions of operators in PostgreSQL

  1. They specify conditions.

  2. They serve as conjunctions between multiple statements.

Types of operators in PostgreSQL

  • Arithmetic operators

  • Bitwise operators

  • Logical operators

  • Comparison operators

Arithmetic 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.

Examples of arithmetic operators in PostgreSQL

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;


Execution of the examples related to arithmetic operators

Comparison operators

Comparison operators in PostgreSQL are symbols used to compare values on either side of the operator.

Examples of comparison operators in PostgreSQL

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;


Execution of the examples related to comparison operators

Logical 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.

Examples of logical operators in PostgreSQL

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

Bitwise operators in PostgreSQL are symbols used to perform bit-by-bit operations. Examples of bitwise operators in PostgreSQL are:

  • &
  • |
  • ~

RELATED TAGS

communitycreator
postgresql

CONTRIBUTOR

Amengor-Kwapong Angela
RELATED COURSES

View all Courses

Keep Exploring