Each operator has its own associated functionality, and can only operate on specific data types (see the section called Data Types in Chapter 3 , for more information on what data types are available). 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. As the operator character itself ( + , in this case) will still be recognized, you will receive an error if you try to misuse an operator with an explanation similar to that shown in Example 4-37 .

Consider the Book Town authors table, which correlates author's names with numeric identifiers.

booktown=# SELECT * FROM authors;
  id   | last_name |    first_name
-------+-----------+------------------
   136 | Brown     | Margaret W.
  1111 | Denham    | Ariel
 15990 | Bourgeois | Paulette
  2031 | Williams  | Margary
 25041 | Bianco    | Margery Williams
  1809 | Geisel    | Theodor Seuss
    16 | Alcott    | Louisa May
    15 | Poe       | Edgar Allen
  4156 | King      | Stephen
  1866 | Herbert   | Frank
  2001 | Clarke    | Anthony C.
  1212 | Worsley   | John
  1644 | Hogarth   | Burne
(13 rows)

Two identifiers in this table are the columns id , and last_name , which are of type integer (a 4-byte integer) and text , respectively. Since the id column is of type integer, it may be used with a mathematical operator along with another numeric type.

Example 4-36. Correct Operator Usage

booktown=# SELECT (id + 1) AS id_plus_one FROM authors;
 id_plus_one
-------------
         137
        1112
       15991
        2032
       25042
        1810
          17
          16
        4157
        1867
        2002
        1213
        1645
(13 rows)

Notice, however, the result of trying to add incompatible types in Example 4-37 .

Example 4-37. Incorrect Operator Usage

booktown=# SELECT last_name + id AS mistake FROM authors; 
ERROR: Unable to identify an operator '+' for types 'text' and 'int4'
       You will have to retype this query using an explicit cast