This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
An operator is another type of special character symbol which is used to perform an operation on identifiers, or constants, returning a resultant value. These can be used in the middle of a query, in order to return data which has been operated upon, and to modify the effect of an SQL statement by subsituting the operator's results. This can be done in place of the values being operated upon within the SQL statement.
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 1500. This can be achieved by substituting the result of an operation (an operator expression ) in an UPDATE statement upon the author_id column. This requires use of the + operator.
Example 3-10. Operators in Statements
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 literal bit level.
In addition to these character symbol operators, it's important to acknowledge the existence of 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-5 lists some fundamental PostgreSQL operators.
Table 3-5. Fundamental PostgreSQL Operators
Category | Operator | Definition |
---|---|---|
Mathematical Operators | + | Addition operator: adds two numeric types |
- | Subtraction operator: subtracts one numeric type from another | |
/ | Division operator: divides one numeric type into another | |
* | Multiplication operator: multiplies one numeric type by another | |
! | Factorial operator: returns an integer's factorial | |
@ | Absolute value operator: returns the absolute value of a numeric value | |
Comparison Operators | = | Equivalence operator: compares two values for equivalence |
< | Less-than comparison operator: compares two values for the smaller value | |
> | Greater-than operator: compares two values for the larger value | |
~ | Regular expression operator: performs a regular expression comparison on between text values | |
Logical Operators | AND | Logical AND operator: returns true if both boolean conditions are true |
OR | Logical OR operator: returns true if at least one of two boolean conditions is true | |
AND | Logical NOT operator: returns the opposite of a boolean condition |
While many operators have different 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 probably implies.
For more information on operators, see the section called Using Operators in Chapter 4 .
Continue to: