|
 |
|
| |
PostgreSQL's numeric operator support can be divided into three general groups:
- Mathematical operators
-
Mathematical operators affect one or two values, perform a mathematical operation, and return a value of a numeric
data type.
- Numeric comparison operators
-
Numeric comparison operators draw a conclusion based on two numeric values (such as whether one is larger than the
other) and returns a value of type boolean, set to either true or false.
- Binary (or bit string) operators
-
Binary, or bit string, operators manipulate numeric values at the bit level of zeroes and ones.
The following sections address each of these operator groups.
Mathematical operators can be used in the target list, in the WHERE clause of a
SELECT statement, or anywhere else a numeric result may be appropriate. This sometimes
will include the ORDER BY clause, a JOIN qualifier, or
a GROUP BY clause.
Table 5-4 describes each of the mathematical operators available in PostgreSQL,
along with example usage.
Table 5-4. Mathematical operators
| Operator |
Usage |
Description |
|
+
|
a
+
b
|
Addition of numeric quantities
a
and
b
|
|
-
|
a
-
b
|
Subtraction of numeric quantity
b
from
a
|
|
*
|
a
*
b
|
Multiplication of numeric quantities
a
and
b
|
|
/
|
a
/
b
|
Division of numeric quantity
a
by
b
|
|
%
|
a
%
b
|
Modulus, or remainder, from dividing
a
by
b
|
|
^
|
a
^
b
|
Exponential operator, the value of
a
to the power of
b
|
|
|/
|
|/
a
|
Square root of
a
|
|
||/
|
||/
a
|
Cube root of
a
|
|
!
|
a
!
|
Factorial of
a
|
|
!!
|
!!
a
|
Factorial prefix, factorial of
a
, different only in syntactic placement from !
|
|
@
|
@
a
|
Absolute value of
a
|
As an example of mathematical operators in the target list, the statement in Example 5-9 takes the retail price for each book and divides the cost with the
/ operator in order to determine the profit margin. This value is then typecast to a
truncated numeric value with only two digits of precision. Finally, the integer constant 1 is subtracted from the
division result, to yield only the percentage points over 100.
Example 5-9. Using Mathematical Operators
booktown=#
SELECT isbn,
booktown-#
(retail / cost)::numeric(3, 2) - 1 AS margin
booktown-#
FROM stock
booktown-#
ORDER BY margin DESC
booktown-#
LIMIT 4;
isbn | margin
------------+--------
0451457994 | 0.35
0760720002 | 0.33
0451198492 | 0.30
0441172717 | 0.29
(4 rows)
Notice that the column name is temporarily aliased to margin by using the
AS keyword. Remember that the column name created by the
AS keyword is a temporary name, and used only for the duration of the query.
Comparison operators are used to compare values of types such as integer or
text to one another, but they will always return a value of type
boolean. These operators are most commonly used in the
WHERE clause, but may be used anywhere in a SQL statement where a value of type
boolean would be valid.
Table 5-5 shows the available comparison operators.
Table 5-5. Comparison operators
|
Operator
|
Description
|
|
<
|
Less-than, returns true if the value to the left is smaller in quantity than the value to the right
|
|
>
|
Greater-than, returns true if the value to the left is greater in quantity than the value to the right
|
|
<=
|
Less-than or equal-to, returns true if the value to the left is smaller, or equal to, in quantity than the value to the right
|
|
>=
|
Greater-than or equal-to, returns true if the value to the left is greater, or equal to, in quantity than the value to the right
|
|
=
|
Equal-to, returns true if the values to the left and right of the operator are equivalent
|
|
< > or !=
|
Not-equal, returns true if the values to the left and right of the operator not equivalent
|
Note: The < > operator exists as an alias to the
!= operator for functional compatibility with other SQL-capable database
implementations. They are effectively identical.
For an example of mathematical comparison operator usage, observe Example 5-10. The
query involved uses the <= operator first, to check if the
retail value is less-than or equal-to 25. Subsequently, the
!= operator is employed with the AND keyword to ensure
that only books which are in stock (whose stock value are not equal to 0) are returned.
Example 5-10. Using comparison operators
booktown=#
SELECT isbn, stock
booktown-#
FROM stock
booktown-#
WHERE retail <= 25
booktown-#
AND stock != 0;
isbn | stock
------------+-------
0441172717 | 77
0590445065 | 10
0679803335 | 18
0760720002 | 28
0929605942 | 25
1885418035 | 77
(6 rows)
The BETWEEN keyword (sometimes called an operator) allows you to check a
value for existence within a range of values. For instance, Example 5-11 shows a
SELECT statement that looks for books with cost between 10 and 17 dollars.
Example 5-11. Using BETWEEN
booktown=#
SELECT isbn FROM stock
booktown-#
WHERE cost BETWEEN 10 AND 17;
isbn
------------
0394800753
0441172717
0451457994
(3 rows)
You can achieve the same output using the less-than-or-equal-to operator (<=)
in conjunction with the greater-than-or-equal-to (>=) operator. See
Example 5-12.
Example 5-12. Operator equivalents to BETWEEN
booktown=#
SELECT isbn FROM stock
booktown-#
WHERE cost >= 10 AND cost <= 17;
isbn
------------
0394800753
0441172717
0451457994
(3 rows)
The BETWEEN syntax simply adds to the readability of an SQL statement. Since both
the keyword and operator forms are equally valid to PostgreSQL, it's mostly a matter of user preference.
Binary operators perform bitwise operations on the literal bits of a bit string or integer. These operators may
affect integer values, or directly on bit string values. Each of PostgreSQL's binary operators are described in Table 5-6.
Table 5-6. Bit-string operators
| Operator |
Usage |
Description |
|
&
|
a
&
b
|
Binary AND between bit string values of
a
and
b
(which may be provided as integers)
|
|
|
|
a
|
b
|
Binary OR between bit string values of
a
and
b
(which may be provided as integers)
|
|
#
|
a
#
b
|
Binary XOR between bit string values of
a
and
b
(which may be provided as integers)
|
|
~
|
~
b
|
Binary NOT, returns the inverted bit string of
b
|
|
<<
|
b
<<
n
|
Binary shifts
b
to the left by
n
bits
|
|
>>
|
b
>>
n
|
Binary shifts
b
to the right by
n
bits
|
Example 5-13 demonstrates shifting a numeric value, and its equivalent bit string, two bits
to the right with the >> operator. It also demonstrates the use of the
bittoint4() function, described in the Section called Functions
."
Example 5-13. Shifting bit strings
booktown=#
SELECT b'1000' >> 2 AS "8 shifted right",
booktown-#
bittoint4(b'1000' >> 2) AS integer,
booktown-#
8 >> 2 AS likewise;
8 shifted right | integer | likewise
-----------------+---------+----------
0010 | 2 | 2
(1 row)
Note: When shifting bit strings, the original length of the string does not change, and any digits pushed either to the
left or right of the bit string will be truncated. When using &,
|, or #, the bit strings operated on must be of
equal length in order to properly compare each bit on a one-to-one basis.
|
|
|
