Concatentation

Pattern matching operators

Pattern matching keywords

Numeric Operators

Mathematical operators

Table 4-8. Mathematical operators

Operator Description Example Result
+ Addition 2+3 5
- Subtraction 2-3 -1
* Multiplication 2*3 6
/ Division (integer division truncates results) 4/2 2
% Modulo (remainder) 5 % 4 1
^ Exponentiation 2 ^ 3 8
|/ Square root |/25 5
||/ Cube root ||/27 3
! Factorial 5! 120
!! Factorial (prefix operator) !!5 120
@ Absolute value @ -5 5
& Binary AND 91 & 15 11
| Binary OR 32 | 3 35
# Binary XOR 17 # 5 20
~ Binary NOT ~1 -2
<< Binary shift left 1 << 4 16
>> Binary shift right 8 >> 2 2

Arithmetic or mathematical operators can be used in the target list, and in the WHERE clause of a SELECT statement. The statement in Example 4-38 looks at the suggested retail price for each book and triples the price. Note also that the column name is temporarily changed to triple by using the AS keyword.

Example 4-38. Using Mathematical Operators with the AS Clause 

booktown=#  SELECT p.sugg_price * 3 AS triple, b.title
booktown-#    FROM publish p, books b
booktown-#   WHERE p.isbn = b.isbn;

 triple |            title
--------+-----------------------------
 $24.00 | Franklin in the Dark
 $50.55 | Bartholomew and the Oobleck
 $13.65 | Goodnight Moon
 $39.87 | The Velveteen Rabbit
 $38.97 | Little Women
 $59.97 | The Cat in the Hat
(6 rows)
Note The Lifespan of the AS Clause
 

The column name created by the AS keyword acts as a temporary alias. It cannot be used in another query to access that column.