This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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)
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. |
Continue to: