



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 54 describes each of the mathematical operators available in PostgreSQL,
along with example usage.
Table 54. 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 59 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 59. 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 55 shows the available comparison operators.
Table 55. Comparison operators
Operator

Description

<

Lessthan, returns true if the value to the left is smaller in quantity than the value to the right

>

Greaterthan, returns true if the value to the left is greater in quantity than the value to the right

<=

Lessthan or equalto, returns true if the value to the left is smaller, or equal to, in quantity than the value to the right

>=

Greaterthan or equalto, returns true if the value to the left is greater, or equal to, in quantity than the value to the right

=

Equalto, returns true if the values to the left and right of the operator are equivalent

< > or !=

Notequal, 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 SQLcapable database
implementations. They are effectively identical.
For an example of mathematical comparison operator usage, observe Example 510. The
query involved uses the <= operator first, to check if the
retail value is lessthan or equalto 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 510. 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 511 shows a
SELECT statement that looks for books with cost between 10 and 17 dollars.
Example 511. 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 lessthanorequalto operator (<=)
in conjunction with the greaterthanorequalto (>=) operator. See
Example 512.
Example 512. 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 56.
Table 56. Bitstring 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 513 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 513. 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 onetoone basis.


