Punctuation symbols

Some special character symbols help to make up the "punctuation" of a SQL statement, much like parentheses, periods and commas do in the English language. Table 3-5 shows some common PostgreSQL-recognized syntactic symbols.

Operator symbols

An operator is another type of special character symbol; it is used to perform operations on identifiers or constants, returning resultant values. Operators can be used for mathematical operations, such as addition, as well as to perform comparison and logical operations.

Consider again the books table, and its numeric author_id field. Recall that the author_id column is an integer used to identify an author. Now imagine that, due to a system modification, all author identifiers must be incremented by 1,500. This can be achieved by evaluating the result of an operation (an operator expression ) in an UPDATE statement upon the author_id column. This requires use of the addition (+) operator. An example of this can be seen in Example 3-10.

booktown=# 
SELECT * FROM books;

  id  |            title            | author_id | subject_id
------+-----------------------------+-----------+------------
 7808 | The Shining                 |      4156 |          9
  156 | The Tell-Tale Heart         |        15 |          9
 4513 | Dune                        |      1866 |         15
 4267 | 2001: A Space Odyssey       |      2001 |         15
 1608 | The Cat in the Hat          |      1809 |          2
 1590 | Bartholomew and the Oobleck |      1809 |          2
(6 rows)

booktown=# 
UPDATE books SET author_id = author_id + 1500; 

UPDATE 6
booktown=# 
SELECT * FROM books;

  id  |            title            | author_id | subject_id
------+-----------------------------+-----------+------------
 7808 | The Shining                 |      5656 |          9
  156 | The Tell-Tale Heart         |      1515 |          9
 4513 | Dune                        |      3366 |         15
 4267 | 2001: A Space Odyssey       |      3501 |         15
 1608 | The Cat in the Hat          |      3309 |          2
 1590 | Bartholomew and the Oobleck |      3309 |          2
(6 rows)

As you can see in Example 3-10, each author_id record is modified with the results of the + operator's operation upon the previous author_id value.

Common operators that you are may already familiar with include the basic mathematical operators: the + sign for the addition of two numeric values, the - sign for the subtraction of one numeric value from another, etc. Some of the more esoteric operators include the bitwise & and | operators, which modify binary values at the bit level.

In addition to these character symbol operators, it's important to remember the SQL keywords, which are frequently called operators as well. Most notably, this includes the logical operators AND, OR, and NOT. While technically keywords, these terms are grouped with the operators because of their operational effect upon constants and identifiers.

Table 3-6 lists some fundamental PostgreSQL operators.

While many operators have various connotations depending on their context, the = operator is an especially important one due to its meaning when used with an UPDATE statement's SET clause.

While in most expressions the = operator is an equivalence operator (used to compare two values for equivalence), when following the SET clause and an identifier name in an UPDATE statement, the = is read as an assignment operator. This means that it is used to assign a new value to an existing identifier, as the SET term implies.

For more information on operators, see the Section called Operators in Chapter 5."