This chapter expands on the operators and functions available to PostgreSQL. These character symbols and identifiers
allow you to flexibly modify and compare results within SQL statements. The results of these operations
can be used in a variety of ways, from updating existing row data, to constraining query results to
only rows matching particular conditions.
PostgreSQL supports the usual variety of standard SQL operators and functions as defined by the ANSI/ISO SQL standards,
such as mathematical operators, basic text formatting functions, and date and time value extraction. PostgreSQL also comes
with a rich set of custom PostgreSQL extensions, such as regular expression comparison operators, and the flexible
to_char() text conversion function.
Take note that these sections describe the
native
operators and functions available to PostgreSQL.
An excellent feature of PostgreSQL is its extensibility in this area. Once you have a solid understanding of operators and functions, you
may be interested in developing your own. These techniques are described in Chapter 7.
In Chapter 3, operators are defined syntactically as tokens that are used to perform
operations on values (e.g., constants, or identifiers), and return the results of that operation. In addition to these
syntactic character symbols, there are some SQL keywords that are considered operators due to their effect on values in
a SQL statement. Throughout this section, both these symbols and keywords will be referred to as operators.
The function of each operator is highly dependent on its context. Applications of operators range from performing
mathematical operations and concatenating character strings, to performing a wide variety of comparisons yielding Boolean
results. This section describes the general usage of operators in SQL, with successive sections on the following families of
operators:
-
Character string
-
Numeric
-
Logical
Note: For an up-to-date and complete list of PostgreSQL supported operators, you can use
psql
's \do slash command to view a list of available
operators. Understand that many of the listed operators are PostgreSQL-specific, and therefore may not exist in other
SQL-capable databases implementations.
Following the discussions of the various types of operators, you'll find information on dealing with
NULL values in expressions, and on the order in which operators are evaluated.
