Using Operators

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 unary operators , and either precede or follow the value they affect (e.g., the @ operator preceding a value is a unary operator indicating the absolute value).

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 identifiers.

         Table "authors"
 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 last_name  | text    |
 first_name | text    |
Index: authors_pkey

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 (+) operator.

booktown=# 
SELECT id + 1 AS id_plus_one, last_name

booktown-# 
       FROM authors

booktown-# 
       ORDER BY id DESC LIMIT 5;

 id_plus_one |  last_name
-------------+--------------
       25042 | Bianco
       15991 | Bourgeois
        7807 | Christiansen
        7806 | Lutz
        4157 | King
(5 rows)

Notice the result of trying to add incompatible types in Example 5-2.

booktown=# 
SELECT id + last_name AS mistake 

booktown-# 
       FROM authors; 

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.