Arithmetic Operators in PostgreSQL

Introduction

ARithmetic operator allows us to perform mathematical calculations on the numeric data type and it also allows us to change the numeric values by performing operations within the SQL query. Arithmetic operators in PostgreSQL include Addition Operator (+), Subtraction Operator (-), Multiplication Operator (*), Division Operator (/), Modulus Operator (%), Exponentiation Operator (^), etc.

Basic Syntax and Structure

The syntax of using arithmetic operators in PostgreSQL is similar to the syntax of using operators in any mathematical calculations. The operator is placed between the values of both the operands, and operands are the values on which we want to operate.

The parameters of the syntax are:

• operand1 is the first operand on which the operation is performed.
• operator specifies the operation to be performed.
• operand2 is the first operand on which the operation is performed.

Addition Operator (+)

Addition operator adds the value of both the operands and returns the sum of both values as a result.

Example: Let us assume we have a table named Student and in this table, we have attributes Student_Id, Student_Name and Student_Marks. And following data is stored in the table.

Now we want to fetch the marks record of all the students and we want that 10 will be added to the marks of every student in the output. Then we have to write the query given below:

Output:

Subtraction Operator (-)

The subtraction operator subtracts the value of the right operand from the value of the left operand and returns the difference of both values as a result.

Example: Let us assume we have a table named Student and in this table, we have attributes Student_Id, Student_Name and Student_Marks. And following data is stored in the table.

Now we want to fetch the marks record of all the students and we want that 10 will be subtracted from the marks of every student in the output. Then we have to write the query given below:

Output:

Multiplication Operator (*)

The multiplication operator multiplies the value of the right operand with the value of the left operand and returns the multiplication of both values as a result.

Example: Let us assume we have a table named Student and in this table, we have attributes Student_Id, Student_Name and Student_Marks. And following data is stored in the table.

Now we want to fetch the marks record of all the students and we want that 10 will be multiplied by the marks of every student in the output. Then we have to write the query given below:

Output:

Division Operator (/)

The division operator divides the value of both operands and returns the quotient of the division of both values as a result.

Example: Let us assume we have a table named Student and in this table, we have attributes Student_Id, Student_Name and Student_Marks. And following data is stored in the table.

Now we want to fetch the marks record of all the students and we want the marks of every student will be divided by 10 in the output. Then we have to write the query given below:

Output:

Modulus Operator (%)

The modulus operator returns the remainder of the division of values of both operands as a result.

Example: Let us assume we have a table named Student and in this table, we have attributes Student_Id, Student_Name and Student_Marks. And following data is stored in the table.

Now we want to fetch the marks record of all the students and we want that marks % 8(remainder when marks are divided by 8) in the output. Then we have to write the query given below:

Output:

Exponentiation Operator (^)

The Exponentiation Operator in PostgreSQL calculates the exponent of the right-hand operand. And returns the calculated value as an output. Example:

Output

Explanation: As we have written 2^3 in the query, so 2 raise to power 3 will be calculated and the result of the calculation i.e. 8 is returned as an output.

Understanding Operator Precedence

Operators have the precedence order which is used during the calculation of the result of expressions. Operator precedence tells the sequence of calculation of operators in an expression. The operator precedence table of PostgreSQL is given below:

Combining and Chaining Operators

Multiple operators can be combined and chained to form a complex expression. In complex expressions, the expression value will be calculated according to the operator's precedence.

Example:

Output:

Explanation: Here we have 2^2*3-5 in the query, among all the operators, the exponent operator has higher precedence so firstly it will be calculated.

2^2*3-5 = 4*3-5

Now among * and - operators, * has higher precedence so now * will be calculated.

4*3-5 = 12-5

Now we have left with a single operator in the expression, - will be calculated.

12-5 = 7

In this way, we are getting the 7 as an answer.

Importance of Parentheses

Parentheses plays a very important role in the calculation of complex expression. Parentheses will always be calculated first irrespective of the precedence of the operator. But inside the parentheses calculation again operator precedence is followed.

Example:

Output:

Explanation: Here we have (2+2)*3 in the query, in the expression * has the higher precedence, but as we have parentheses in the expression so firstly parentheses will be solved.

( 2 + 2 ) * 3 = 4 * 3

Now we have left with a single operator in the expression, * will be calculated.

4 * 3 = 12

In this way, we are getting the 12 as an answer.

Handling Arithmetic Operators With NULL Values

We have to handle NULL values properly while using arithmetic operators in PostgreSQL. PostgreSQL does not perform arithmetic calculations on NULL values. It will return nothing when we try to use NULL values with arithmetic operators in PostgreSQL.

Example:

Output

Explanation: As we have written 4+null in the query, so here we are performing addition operation with null value, so we are getting nothing in the output.