Arithmetic Operators in PostgreSQL

PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source. One of the most fundamental properties of database systems is arithmetical operations, without which a multitude of tasks, from simple arithmetic to complex analysis, are unfeasible.

The arithmetic operators are fundamental in doing the calculations in PostgreSQL, by which you can be sure that the process will be fast and correct. In this article, we are going to discuss different arithmetical operators in PostgreSQL.

Introduction to Arithmetic Operators

In PostgreSQL, these are arithmetic operators that are symbols or keywords used for doing mathematical operations over numerical data types that are stored in the database. A user can carry out such expressions using these operators including addition, subtraction, multiplication, and division operations, enabling many data manipulation tasks.

Various Arithmetic Operators in PostgreSQL

Given below are the most commonly used arithmetic operators in PostgreSQL:

Operators	Names	Description
+	Addition	Add the two values
–	Subtraction	Subtracts right-hand operand from left-hand operand
*	Multiplication	Multiply two values
/	Division	Divide one value by another
%	Modulo	Returns the remainder of a division operation
^	Raised To	This gives the exponent value of the right-hand operand
|\	Square Root	Calculates the square root
!	Factorial	Calculates the factorial of a non-negative integer
@	Absolute Value	This operator returns the absolute value of a number
&	Bitwise AND	It performs a bitwise AND operation on two integers, returning a new integer where each bit position is the result of the AND operation of the corresponding bits in the input integers.
|	Bitwise OR	It performs a bitwise OR operation on two integers, returning a new integer where each bit position is the result of the OR operation of the corresponding bits in the input integers.
#	Bitwise XOR	It performs a bitwise XOR (exclusive OR) operation on two integers, returning a new integer where each bit position is the result of the XOR operation of the corresponding bits in the input integers.
~	Bitwise NOT	It performs a bitwise negation operation on an integer, returning a new integer where each bit is flipped.
<<	Bitwise shift right	It shifts the bits of an integer to the left by a specified number of positions, adding zeros at the right end.
>>	Bitwise shift left	It shifts the bits of an integer to the right by a specified number of positions, adding zeros at the left end for positive numbers and sign bits for negative numbers.

Consider a database table called Sales with the following records:

Sales Table

Now, let’s see the examples of each arithmetic operator using this table:

Examples of Arithmetic Operator

1. Addition (+): Adds values on either side of the operator.

Syntax:

SELECT price + 5 AS New_price FROM Sales;

This query is to produce a result set containing the prices from the Sales table with 5 added to each.

Output:

You can see below that 5 get added to each price and the result set will have a column named New_price containing the result of the addition.

Addition

2. Subtraction (-): Subtracts right-hand operand from left-hand operand

Syntax:

SELECT price - 2 AS New_price FROM sales;

This query is to produce a result set containing the prices from the sales table with 2.50 subtracted from each.

Output:

You can see below that 2 get subtracted to each price and the result set will have a column named New_price containing the result of the subtraction.

Subtraction

3. Multiplication (*): Multiplies values on either side of the operator

Syntax:

SELECT price * 2 AS New_price FROM sales;

This query is to produce a result set containing the prices from the sales table multiplied by 2.

Output:

You can see below that 2 gets multiplied to each price and the result set will have a column named New_price containing the result of the multiplication.

Multiplication

4. Division (/): Divides left-hand operand by right-hand operand

Syntax:

SELECT price / 2 AS New_price FROM sales;

This query is to produce a result set containing the prices from the sales table divided by 2.

Output:

You can see below that 2 get divided to each price and the result set will have a column named New_price containing the result of the division.

Division

5. Modulus (%): Divides left-hand operand by right-hand operand and returns the remainder

Syntax:

SELECT price % 2 AS New_price FROM sales;

This query is to produce a result set containing the remainder when each price from the sales table is divided by 2.

Output:

You can see below that we have modulo 2 with each price and the result set will have a column named New_price containing the result of the modulus.

Modulus

6. Exponentiation (^ or ): This gives the exponent value of the right-hand operand

Syntax:

SELECT POWER(price, 2) AS New_price FROM sales;

This query calculates the square of the price column for each row in the sales table and renames the result as New_price.

Output:

You can see that the result set represents the square of the price by 2 values from the corresponding row in the sales table.

Power

7. Square Root (|/): Finds the square root of the particular column.

Syntax:

SELECT SQRT(price) AS New_price FROM sales;

This query calculates the square root of the price column for each row in the sales table and renames the result as New_price.

Output:

You can see that each row in the result set represents the square root of the price value from the corresponding row in the sales table.

Square Root

Consider a database table called Numbers with the following records:

Numbers Table

8. Factorial: It calculates the factorial of a non-negative integer.

Syntax:

SELECT value, factorial(value) AS factorial FROM numbers;

This query selects the value column from the numbers table and calculates the factorial of each value.

Output:

Factorial

9. Absolute Value: It returns the absolute value of a number, i.e., its distance from zero.

Syntax:

SELECT value, abs(value) AS absolute_value FROM numbers;

This query selects the value column from the numbers table and calculates the absolute value of each value using the built-in abs() function.

Output:

Absolute Value

10. Bitwise AND: It performs a bitwise AND operation on two integers, returning a new integer where each bit position is the result of the AND operation of the corresponding bits in the input integers.

Syntax:

SELECT value, (value & 3) AS bitwise_and_result FROM numbers;

This query selects the value column from the numbers table and performs a bitwise AND operation between each value and the integer 3.

Output:

Bitwise AND

11. Bitwise OR: It performs a bitwise OR operation on two integers, returning a new integer where each bit position is the result of the OR operation of the corresponding bits in the input integers.

Syntax:

SELECT value, (value | 3) AS bitwise_or_result FROM numbers;

This query selects the value column from the numbers table and performs a bitwise OR operation between each value and the integer 3.

Output:

Bitwise OR

12. Bitwise XOR: It performs a bitwise XOR (exclusive OR) operation on two integers, returning a new integer where each bit position is the result of the XOR operation of the corresponding bits in the input integers.

Syntax:

SELECT value, (value # 3) AS bitwise_xor_result FROM numbers;

This query selects the value column from the numbers table and performs a bitwise XOR operation between each value and the integer 3.

Output:

Bitwise XOR

13. Bitwise NOT: It performs a bitwise negation operation on an integer, returning a new integer where each bit is flipped.

Syntax:

SELECT value, (~value) AS bitwise_not_result FROM numbers;

This query also selects the value column from the numbers table and performs a bitwise NOT operation on each value.

Output:

Bitwise NOT

14. Bitwise shift left: It shifts the bits of an integer to the right by a specified number of positions, adding zeros at the left end for positive numbers and sign bits for negative numbers.

Syntax:

SELECT value, (value << 1) AS bitwise_shift_left_result FROM numbers;

The bitwise left shift operation shifts all the bits of the value to the left by one position. This is equivalent to multiplying the value by 2.

Output:

Bitwise Shift Left

15. Bitwise shift right: It shifts the bits of an integer to the left by a specified number of positions, adding zeros at the right end.

Syntax:

SELECT value, (value >> 1) AS bitwise_shift_right_result FROM numbers;

The bitwise right shift operation shifts all the bits of the value to the right by one position. This is equivalent to dividing the value by 2 and truncating any remainder.

Output:

Bitwise Shift Right

Conclusion

In PostgreSQL, the Arithmetic Operators make the complex mathematical calculations possible in the database system’s environment. It could be a simple sum or a difficult statistical operation but the ability to use the operators well is what is important for achieving good results in tasks related to data manipulation and analysis. By mastering arithmetic operators, database users can unlock the full potential of PostgreSQL for their data-driven applications and workflows.