PostgreSQL | PostgreSQL Basic Syntax | Using Arithmetic Operators
Arithmetic operators are used not only for calculations such as the four basic arithmetic operations, but also for square roots and factorials. This page explains how to use arithmetic operators.
Types and Usage of Arithmetic Operators
The arithmetic operators available in PostgreSQL are as follows.
+ addition 2 + 3 -> 5
- subtraction 2 - 3 -> -1
* multiplication 2 * 3 -> 6
/ division 4 / 2 -> 2 ※ integer division discards the remainder
% modulo 5 % 4 -> 1
^ power 2.0^3.0 -> 8 ※ applied from left to right
|/ square root |/25.0 -> 5
||/ cube root ||/27.0 -> 3
! factorial 5! -> 120
!! factorial !!5 -> 120 ※ prefix operator
@ absolute value @-5.0 -> 5
The arithmetic operators above can be used with all numeric data types.
–
Try it in practice by creating the following test table.
mydb=# create table test( num1 real, num2 real);
CREATE TABLE
mydb=#
Add the following data to the table.
mydb=# insert into test values (15.4, 7.25), (-5.9, 9.0), (18.225, -7.3);
INSERT 0 3
mydb=# select * from test;
num1 | num2
--------+------
15.4 | 7.25
-5.9 | 9
18.225 | -7.3
(3 rows)
Then use a SELECT command to retrieve the results of operations on the num1 and num2 columns.
mydb=# select num1, num2,
mydb-# num1 + num2 as "num1 + num2",
mydb-# num1 * num2 as "num1 * num2",
mydb-# num1 / num2 as "num1 / num2"
mydb-# from test;
num1 | num2 | num1 + num2 | num1 * num2 | num1 / num2
--------+------+-------------+-------------+-------------
15.4 | 7.25 | 22.65 | 111.649994 | 2.1241379
-5.9 | 9 | 3.1 | -53.100002 | -0.65555555
18.225 | -7.3 | 10.925 | -133.04251 | -2.4965754
(3 rows)
You can obtain the results of operations on values stored in columns by using arithmetic operators.
Using Bit Operators
Arithmetic operators also include operators for bit operations.
& bitwise AND 91 & 15 -> 11
| bitwise OR 32 | 3 -> 35
# bitwise XOR 17 # 5 -> 20
~ bitwise NOT ~1 -> -2
<< bitwise left shift 1 << 4 -> 16
>> bitwise right shift 8 >> 2 -> 2
Bit operators can be used with integer data types or bit strings.
The bitwise AND operator compares bits at the same positions on the left and right, and returns 1 only when both bits are 1.
85 & 15
0000000001010101 = 85
0000000000001111 = 15
------------------------
0000000000000101 = 5
The bitwise OR operator compares bits at the same positions on the left and right, and returns 1 when either bit is 1.
85 | 15
0000000001010101 = 85
0000000000001111 = 15
------------------------
0000000001011111 = 95
The bitwise XOR operator compares bits at the same positions on the left and right, and returns 1 when either one of the bits is 1. If both are 1 or both are 0, it returns 0.
85 # 15
0000000001010101 = 85
0000000000001111 = 15
------------------------
0000000001011010 = 90
The bitwise NOT operator inverts each bit of the value on the right, changing 0 to 1 and 1 to 0.
~ 85
0000000001010101 = 85
------------------------
1111111110101010 = -86
The bitwise left shift moves the target value to the left by the specified number of bits.
85 << 2
0000000001010101 = 85
------------------------
0000000101010100 = 340
The bitwise right shift moves the target value to the right by the specified number of bits.
85 >> 2
0000000001010101 = 85
------------------------
0000000000010101 = 21
Try a simple example using a SELECT command.
mydb=# select 85 & 15 as "85 & 15",
mydb-# 85 | 15 as "85 | 15",
mydb-# 85 # 15 as "85 # 15",
mydb-# ~ 85 as "~ 85",
mydb-# 85 << 2 as "85 << 2",
mydb-# 85 >> 2 as "85 >> 2";
85 & 15 | 85 | 15 | 85 # 15 | ~ 85 | 85 << 2 | 85 >> 2
---------+---------+---------+------+---------+---------
5 | 95 | 90 | -86 | 340 | 21
(1 row)
You can obtain the results of operations by using bit operators.
–
This page explained how to use the arithmetic operators provided by PostgreSQL.