Operators operate on either a single value or a pair of values. The majority of operators operate on two values,
with the operator placed between the values it is to operate upon (e.g., a - b). Operators
that affect only one value are called
, and either precede or follow the value they
affect (e.g., the @ operator preceding a value is a unary operator indicating the absolute
Many operators, while invoked with the same keyword or character symbol, will have different effects depending on the
data types to which they are applied. Further, operators will not always have a relevant use to every data type (see Chapter 3 for more information about what data types are available to PostgreSQL).
For example, you can use the addition operator (+) to add two integer values
together, but you cannot use it to add an integer to a text type. This is an undefined (and therefore ambiguous and
disallowed) use of the operator. The operator character itself (+, in this case) will
still be recognized, but you will receive an error such as the one shown in Example 5-2
if you try to misuse an operator:
Consider the Book Town authors table, which correlates author's names with numeric
Attribute | Type | Modifier
id | integer | not null
last_name | text |
first_name | text |
Two identifiers in this table are the columns id, and
last_name, which are types integer (a 4-byte integer)
and text, respectively. Since the id column is type
integer, it may be used with a mathematical operator along with another numeric value.
Example 5-1 demonstrates correct usage of the addition (+)
Example 5-1. Correct operator usage
SELECT id + 1 AS id_plus_one, last_name
ORDER BY id DESC LIMIT 5;
id_plus_one | last_name
25042 | Bianco
15991 | Bourgeois
7807 | Christiansen
7806 | Lutz
4157 | King
Notice the result of trying to add incompatible types in Example 5-2.
Example 5-2. Incorrect operator usage
SELECT id + last_name AS mistake
ERROR: Unable to identify an operator '+' for types 'int4' and 'text'
You will have to retype this query using an explicit cast
Fortunately, as you can see in Example 5-2, PostgreSQL's operator-misuse error messages
supply a reason for failure, rather than blindly failing. These can be helpful in determining the next step in
developing your statement, in order to make it a valid query.