PostgreSQL MOD()

Introduction to PostgreSQL MOD()

The PostgreSQL MOD() function is a mathematical function. The keyword MOD stands for the ‘modulo’, which is used to perform modulo operation(divide the two number and return remainder). It is used to calculate and return the remainder of a number. It takes two numbers as an input parameter. It uses a formula to divide the first number by the second number, and it returns the remainder. If the second parameter value is zero, then the MOD() function throws an error or exception, which is the divide by zero error message.

Syntax:

Given below is the syntax:

MOD(p,q)

Explanation:

• p: This defines the number whose remainder to be calculated.
• q: This defines the number which is used to divide into value n.

How does MOD() Function work in PostgreSQL?

• The PostgreSQL MOD() function takes two numbers as input and divides them, and return the remainder.
• It divides the first parameter by the second parameter and then returns the remainder.
• If we consider the p as a first parameter and q as a second parameter, then the value of the second parameter q should be non – zero (0). If the value of the second parameter q is zero, then it throws an error or exception, which is divided by zero error message.
• The return type of it is the same as the type of the arguments passed to the function.
• We can use this function with PostgreSQL 8.4, PostgreSQL 9.0, PostgreSQL 9.1, PostgreSQL 9.2, PostgreSQL 9.3, and PostgreSQL 9.4, etc versions.
• It does not accept non-numeric data type values as input. This will throw an error or exception related to typecasting if we try do use non-numeric value.

Examples of PostgreSQL MOD()

Given below are the examples of postgreSQL MOD():

Example #1

First argument value as a positive number

Code:

SELECT MOD(13,2) AS MOD_13_BY_2;

Output:

Code:

SELECT MOD(13,-2) AS MOD_13_BY_MIN_2;

Output:

Here we firstly kept the second parameter as a positive value, and in the secondly, we have kept the second parameter as a negative value, but the result of both the examples is the same, which is the positive remainder.

Example #2

First argument value as a negative number

Code:

SELECT MOD(-13,2) AS MOD_MIN_13_BY_2;

Output:

Code:

SELECT MOD(-13,-2) AS MOD_MIN_13_BY_MIN_2;

Output:

Here we firstly kept the second parameter as a positive value. Secondly, we have kept the second parameter as a negative value, but the result of both examples is the same, which is the negative remainder.

Example #3

General examples to perform modulo operation.

Code:

SELECT MOD(14,10) AS MOD_14_BY_10;

Output:

Code:

SELECT MOD(400,2.33) AS MOD_400_BY_2_33;

Output:

Example #4

Example to illustrate the divide by zero error in modulo operation.

Code:

SELECT MOD(10,0) AS MOD_10_BY_0;

Output:

In the above example, we have used the second parameter as zero value which will throw an error or exception, which is divide by zero error.

Example #5

Example with non-numeric value as an input. Consider the following example in which we will pass the first and second parameter as in text format.

Code:

SELECT MOD('10','0') AS MOD_10_BY_0;

Output:

In the above snapshot, we can see that it shows an error message in which the data type of the arguments is unknown and not able to cast them to the numeric value implicitly.

This is a guide to PostgreSQL MOD(). Here we discuss how does the MOD() function work in PostgreSQL with respective query examples. You may also have a look at the following articles to learn more –

1. PostgreSQL ARRAY_AGG()
2. PostgreSQL CTE
3. PostgreSQL GRANT
4. PostgreSQL Functions