Using Operators

Concatentation

Pattern matching operators

Pattern matching keywords

Mathematical operators

Arithmetic or mathematical operators can be used in the target list, and in the WHERE clause of a SELECT statement. The statement in Example 4-38 looks at the suggested retail price for each book and triples the price. Note also that the column name is temporarily changed to triple by using the AS keyword.

booktown=#  SELECT p.sugg_price * 3 AS triple, b.title
booktown-#    FROM publish p, books b
booktown-#   WHERE p.isbn = b.isbn;

 triple |            title
--------+-----------------------------
 $24.00 | Franklin in the Dark
 $50.55 | Bartholomew and the Oobleck
 $13.65 | Goodnight Moon
 $39.87 | The Velveteen Rabbit
 $38.97 | Little Women
 $59.97 | The Cat in the Hat
(6 rows)

	The Lifespan of the AS Clause
	The column name created by the AS keyword acts as a temporary alias. It cannot be used in another query to access that column.

Comparison operators

Comparison operators are used to compare data types like integer or text, but they will always return a boolean value. Table 4-9 shows the available comparison operators.

	Not Equal Operator Conventions
	If you specify the not equal to (!=) operator in your query, it is converted to <>.

This select statement returns all publisher names except for a publisher named ABC Books.

  SELECT * 
    FROM publish
   WHERE publ_name <> 'ABC Books';
 
  isbn   |    publ_name     | sugg_price
---------+------------------+------------
 2567841 | ODG Books        |     $12.99
 8741205 | Children's Books |     $19.99
  214872 | Young Readers    |      $8.00
  676883 | Reading Rainbow  |     $13.29
(4 rows)   

To see a display of book titles that were published after 1990, use the comparison operators:

  SELECT title, cpright_date
   FROM  book
   WHERE cpright_date >= '1990-01-01'; 
   

The result displays:

            title            | cpright_date
-----------------------------+--------------
 Goodnight Moon              | 1991-04-01
 Bartholomew and the Oobleck | 1997-02-01
(2 rows)          

The comparison operators can be used to look at a table with the book titles, author name, and price of books that cost less than or equal to $13.00. Remember that you are accessing columns from two different tables. This operation requires a join. The command to accomplish this is:

 
  SELECT p.sugg_price, b.title, b.author_id
   FROM publish p, book b
   WHERE p.isbn = b.isbn
   AND p.sugg_price <= '13.00';
   

The output is:

 sugg_price |        title         | author_id
------------+----------------------+-----------
      $8.00 | Franklin in the Dark |       142
      $4.55 | Goodnight Moon       |       136
     $12.99 | Little Women         |       231
(3 rows)
   

Comparison keywords

The BETWEEN keyword provides the ability to compare a value to a range of values. For instance, Example 4-41 shows a SELECT statement which looks for author names between 200 and 250:

   SELECT lastname, firstname
     FROM author
    WHERE a_id BETWEEN 200 AND 250; 
   

The output is:

----------+------------
 Williams | Margary
 Alcott   | Luoisa May
 Williams | Margary
 Alcott   | Luoisa May
(4 rows)

You can return the exact same output using the less-than operator (<) in conjunction with the greater-than (>) operator, as you can see in Example 4-42.

   SELECT lastname, firstname
     FROM author
    WHERE a_id >= 200 AND a_id <= 250;   

Using the BETWEEN keyword and a combination of less-than/greater-than operators will output the same result, because when PostgreSQL interprets BETWEEN, it converts it into the less-than/greater-than form. Both forms are valid, and as far as PostgreSQL is concerned, it's really just a matter of user preference.

If a table has NULL values in it, comparison operators can be used to display NULL valued rows:

booktown=# SELECT last_name, first_name 
booktown-#   FROM author 
booktown-#  WHERE first_name IS NULL;

Examining Example 4-43 and Example 4-44, you might think that the syntax in the two statements provided are identical.

booktown=# SELECT last_name, first_name 
booktown-#   FROM authors
booktown-#  WHERE first_name = NULL; 

NULL Equivalence

When comparing a value to NULL in an expression, it is a good idea to be in the habit of using the IS NULL and IS NOT NULL keyword operators. You should avoid using the expression = NULL, because it prompts the parser to look for a value that is literally equal to NULL. If NULL is an unknown value, this kind of comparison is ambiguous. PostgreSQL provides a translation from = NULL to IS NULL, and likewise for the != NULL operation. This is provided only for compatibility with client applications (such as Microsoft Access), but be aware that this functionality may be discontinued in the future. The appropriate choice is to use IS NULL or IS NOT NULL.

Binary operators

Binary operators also work on bit strings, as shown in the table below.

	Shifting Bit Strings
	When shifting bit strings, the original length of the string does not change. Also, when using operators such as &, |, and #, the bit strings within the argument must be of equal length.