Special character symbols are characters with a predefined syntactic meaning in PostgreSQL. They are typically
disallowed from being used in identifier names for this reason, though as mentioned in the section on quoted identifiers, this restriction can usually be worked around with quotes if need
be.
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 310.
Example 310. Operators in statements
booktown=#
SELECT * FROM books;
id  title  author_id  subject_id
+++
7808  The Shining  4156  9
156  The TellTale 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 TellTale 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 310, 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 36 lists some fundamental PostgreSQL operators.
Table 36. Fundamental PostgreSQL operators
Category

Operator

Definition

Mathematical operators

+ (addition)

Adds two numeric types

 (subtraction)

Subtracts one numeric type from another

/ (division)

Divides one numeric type by another

* (multiplication)

Multiplies one numeric type by another

! (factorial)

Returns an integer's factorial

@ (absolute value)

Returns the absolute value of a numeric value

Comparison operators

= (equivalence)

Compares two values for equivalence

< (less than)

Evaluates whether or not one number is less than another

> (greater than)

Evaluates whether or not one number is larger than another

~ (regular expression)

Performs a regular expression comparison on text
values

Logical operators

AND

Returns true if both Boolean conditions are true

OR

Returns true if at least one of two Boolean conditions is true

NOT

Returns the opposite of a Boolean condition

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