Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

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.

Example 5-1. Correct operator usage

SELECT id + 1 AS id_plus_one, last_name

       FROM authors

       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.

Example 5-2. Incorrect operator usage

SELECT id + last_name AS mistake 

       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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire