This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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
 
Continue to: