PostgreSQL MOD() Function

PostgreSQL MOD() Function

The PostgreSQL MOD() function is used to calculate the remainder of the division of one number by another. This function is essential for mathematical computations involving modular arithmetic, periodic functions, and cyclic phenomena.

---

Syntax

MOD(number1, number2)

The MOD() function has the following components:

• number1: The dividend.
• number2: The divisor.

---

Example PostgreSQL MOD() Queries

Let's look at some examples of PostgreSQL MOD() function queries:

1. Basic MOD() Example

SELECT MOD(10, 3) AS remainder;

This query calculates the remainder of 10 divided by 3, which is 1.

2. MOD() with Column Values

SELECT number1, number2, MOD(number1, number2) AS remainder
FROM number_pairs;

This query retrieves the number1, number2, and their remainder from the number_pairs table.

3. MOD() with Negative Values

SELECT number1, number2, MOD(number1, number2) AS remainder
FROM number_pairs
WHERE number1 < 0 OR number2 < 0;

This query retrieves the number1, number2, and their remainder from the number_pairs table where either number1 or number2 is negative.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the MOD() function to calculate the remainders.

Step 1: Creating a Table

This step involves creating a new table named number_pairs to store numerical data.

CREATE TABLE number_pairs (
    id SERIAL PRIMARY KEY,
    number1 INTEGER,
    number2 INTEGER
);

In this example, we create a table named number_pairs with columns for id, number1, and number2.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the number_pairs table.

INSERT INTO number_pairs (number1, number2)
VALUES (10, 3),
       (20, 4),
       (-30, 5),
       (40, 6);

Here, we insert data into the number_pairs table.

Step 3: Using the MOD() Function

This step involves using the MOD() function to calculate the remainders from the number_pairs table.

-- Basic MOD()
SELECT number1, number2, MOD(number1, number2) AS remainder
FROM number_pairs;

-- MOD() with Negative Values
SELECT number1, number2, MOD(number1, number2) AS remainder
FROM number_pairs
WHERE number1 < 0 OR number2 < 0;

These queries demonstrate how to use the MOD() function to calculate the remainders from the number_pairs table, including basic usage and handling negative values.

Conclusion

The PostgreSQL MOD() function is a fundamental tool for calculating the remainder of the division of one number by another. Understanding how to use the MOD() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.