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
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

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

  




 

 

Chapter 5. Operators and Functions

Table of Contents
Operators
Functions

This chapter expands on the operators and functions available to PostgreSQL. These character symbols and identifiers allow you to flexibly modify and compare results within SQL statements. The results of these operations can be used in a variety of ways, from updating existing row data, to constraining query results to only rows matching particular conditions.

PostgreSQL supports the usual variety of standard SQL operators and functions as defined by the ANSI/ISO SQL standards, such as mathematical operators, basic text formatting functions, and date and time value extraction. PostgreSQL also comes with a rich set of custom PostgreSQL extensions, such as regular expression comparison operators, and the flexible to_char() text conversion function.

Take note that these sections describe the native operators and functions available to PostgreSQL. An excellent feature of PostgreSQL is its extensibility in this area. Once you have a solid understanding of operators and functions, you may be interested in developing your own. These techniques are described in Chapter 7.

Operators

In Chapter 3, operators are defined syntactically as tokens that are used to perform operations on values (e.g., constants, or identifiers), and return the results of that operation. In addition to these syntactic character symbols, there are some SQL keywords that are considered operators due to their effect on values in a SQL statement. Throughout this section, both these symbols and keywords will be referred to as operators.

The function of each operator is highly dependent on its context. Applications of operators range from performing mathematical operations and concatenating character strings, to performing a wide variety of comparisons yielding Boolean results. This section describes the general usage of operators in SQL, with successive sections on the following families of operators:

  • Character string

  • Numeric

  • Logical

Note: For an up-to-date and complete list of PostgreSQL supported operators, you can use psql 's \do slash command to view a list of available operators. Understand that many of the listed operators are PostgreSQL-specific, and therefore may not exist in other SQL-capable databases implementations.

Following the discussions of the various types of operators, you'll find information on dealing with NULL values in expressions, and on the order in which operators are evaluated.

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

booktown=# SELECT id + 1 AS id_plus_one, last_name
booktown-#        FROM authors
booktown-#        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

booktown=# SELECT id + last_name AS mistake 
booktown-#        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.

Character String Operators

PostgreSQL contains a comprehensive set of character string operators, from simple text concatenation and string comparison, to a strong set of regular expression matching. Character string operators are valid upon values of types char, varchar, and PostgreSQL's own text type.

The following sections describe the basic comparison and concatenation operators, as well as the implementation of case-sensitive and case-insensitive regular expression operators.

Basic comparison

Each of the basic character string comparison and concatenation operators supported by PostgreSQL are listed in Table 5-1.

Note: Note that the LIKE and ILIKE keywords, which call to the like() function, are sometimes referred to as string comparison operators. These keywords are covered in the Section called Functions".

Table 5-1. Basic Character String Operators

Operator

Usage

Description

=

'string' = 'comparison'

A comparison returning true if string matches comparison identically

!=

'string' != 'comparison'

A comparison returning true if string does not match comparison identically

<>

'string' <> 'comparison'

Identical to the != operator

<

'string' < 'comparison'

A comparison returning true if string should be sorted alphabetically before comparison

<=

'string' <= 'comparison'

A comparison returning true if string should be sorted alphabetically before comparison, or if the values are identical

>

'string' > 'comparison'

A comparison returning true if string should be sorted alphabetically after comparison

>=

'string' >= 'comparison'

A comparison returning true if string should be sorted alphabetically after comparison, or if the values are identical

Each of the string comparison operators returns a Boolean result of either true or false. The alphabetical sorting referred to by Table 5-1 compares each sequential character in a string, determining if one character is considered 'greater than' or 'less than' the other. If the leading characters in two strings are at all identical, each character is checked from left to right until two different characters are found for comparison. In this sorting scheme, characters are determined to be higher than one another based on their ASCII value, as demonstrated in the following example:

booktown=# SELECT letter,
booktown-#        ascii(letter)
booktown-#        FROM text_sorting
booktown-#        ORDER BY letter ASC;
 letter | ascii
--------+-------
 0      |    48
 1      |    49
 2      |    50
 3      |    51
 A      |    65
 B      |    66
 C      |    67
 D      |    68
 a      |    97
 b      |    98
 c      |    99
 d      |   100
(12 rows)

If you are unsure of how a character will be sorted, you can use the ascii() function to determine the ASCII value of the character. This function is described further in the Section called Functions." Example 5-3 illustrates a comparative check on the books table, and returns all titles whose first letter would be sorted before the letter D.

Example 5-3. Comparing strings

booktown=# SELECT title FROM books
booktown-#              WHERE substr(title, 1, 1) < 'D';
            title
-----------------------------
 2001: A Space Odyssey
 Bartholomew and the Oobleck
(2 rows)

String concatenation

The text concatenation operator (||) is an invaluable tool for formatting output results. Like all operators, it may be used anywhere a constant value is allowed in a SQL statement. Values may be repeatedly concatenated in a single statement by simply appending the || operator after each appended string constant or identifier.

As an example, it might be used in the WHERE clause in order to constrain rows by comparing against a dynamically generated character string. Example 5-4 demonstrates how to use this operator.

Example 5-4. Concatenating strings

booktown=# SELECT 'The Title: ' || title || ', by ' ||
booktown-#        first_name || ' ' || last_name AS book_info
booktown-#        FROM books NATURAL JOIN authors AS a (author_id)
booktown-#        LIMIT 3;
                       book_info
--------------------------------------------------------
 The Title: The Shining, by Stephen King
 The Title: Dune, by Frank Herbert
 The Title: 2001: A Space Odyssey, by Arthur C. Clarke
(3 rows)

Regular expression matching operators

For times when normal equivalence comparisons are inadequate, PostgreSQL has several operators designed to perform pattern matching against regular expressions. A regular expression is similar to any other string to be matched against, with the exception that some characters (such as the square braces, pipe, and backslash) have special meaning in a comparison. If you have used UNIX programs such as sed, grep, or perl, you may already be familiar with this kind of syntax.

Note: For more detailed information on regular expressions in general, refer to O'Reilly's Mastering Regular Expressions, by Jeffrey E. F. Friedl.

When a value is compared against a regular expression, the expression itself (or regex) may match both literal character sequences, as well as several variable character sequences. Both literal and variable sequences may be specified throughout the expression. Example 5-5 illustrates an example of such a sequence. It searches the Book Town authors table for names beginning with eitherA or T.

Example 5-5. An example regular expression

booktown=# SELECT first_name, last_name
booktown-#        FROM authors
booktown-#        WHERE first_name ~ '^A|^T';
  first_name   |  last_name
---------------+--------------
 Ariel         | Denham
 Tom           | Christiansen
 Arthur C.     | Clarke
 Andrew        | Brookins
 Theodor Seuss | Geisel
(5 rows)

The ~ symbol is the regular expression operator, within the WHERE clause, and the regular expression sequence itself in Example 5-5 is ^A|^T. The special characters in this sequence are the caret (^), and the pipe (|), while the literal characters are A and T. The special characters used in regular expressions are explained in detail later in this section.

The most important syntactic difference between the use of the like() function and regular expression operators is that like() uses wild-card symbols (e.g., %) at the beginning and end of its expression in order to match a substring. In contrast, (with the beginning and end-line symbols found in Table 5-3) regular expression operators will implicitly look for the regular expression sequence anywhere in the compared character string unless otherwise instructed.

Table 5-2 lists the regular expression operators. These operators compare a text value (either an identifier or a constant) to a regular expression. Each operator provides a Boolean result, depending on the nature of the operator.

Table 5-2. Regular expression comparison operators

Operator

Usage

Description

~

'string' ~ 'regex'

A regular expression comparison, yielding true if the expression matches

!~

'string' !~ 'regex'

A regular expression comparison, yielding true if the expression does not match

~*

'string' ~* 'regex'

A case-insensitive regular expression, yielding true if the expression matches

!~*

'string' !~* 'regex'

not equal to regular expression, case insensitive

The special characters available to a regular expression are listed in Table 5-3. These are the characters which may be used in a regular expression string to represent special meaning.

Table 5-3. Regular expression symbols

Symbol(s)

Usage

Description

^

^ expression

Matches the beginning (^  ) of the character string

$

expression $

Matches the end ($ ) of the character string

.

.

Matches any single character

[ ]

[ abc ]

Matches any single character which is between brackets (e.g., a, b, or c)

[^]

[^abc ]

Matches any single character not between brackets, following caret (e.g., not a, b, or c)

[-]

[ a-z ]

Matches any character which is between the range of characters between brackets and separated by the dash (e.g., within a through z)

[^-]

[^a-z ]

Matches any characters not between the range of characters between brackets and separated by the dash (e.g., not within a through z)

?

a ?

Matches zero or one instances of the character (or regex sequence) preceding it

*

a *

Matches zero or more instances of the character (or regex sequence) preceding it

+

a+

Matches one or more instances of the character (or regex sequence) preceding it

|

expr1| expr2

Matches character sequences to the left or right of it (e.g., either expr1, or expr2)

( )

(expr1) expr2

Explicitly groups expressions, to clarify precedence of special character symbols

Note: Note that in order to use a literal version of any of the characters in Table 5-3, they must be prefixed with two backslashes (e.g., \\$ represents a literal dollar sign).

A common use of regular expressions is to search for a literal substring within a larger string. This can be achieved either with the ~ operator, if case is important, or with the ~* operator if the comparison should be case-insensitive. These operators are each demonstrated in Example 5-6.

Example 5-6. A Simple Regular Expression Comparison

booktown=# SELECT title FROM books
booktown-#        WHERE title ~ 'The';
        title
----------------------
 The Shining
 The Cat in the Hat
 The Velveteen Rabbit
 The Tell-Tale Heart
(4 rows)

booktown=# SELECT title FROM books
booktown-#        WHERE title ~* 'The';
            title
-----------------------------
 The Shining
 The Cat in the Hat
 Bartholomew and the Oobleck
 Franklin in the Dark
 The Velveteen Rabbit
 The Tell-Tale Heart
(6 rows)

As you can see in Example 5-6, two more rows are returned when using the ~* operator, as it matches not just "the" sequence, but modification of case on the same sequence (including the, tHe, ThE, and so on).

The same regular expression sequence can be modified to use the ^ symbol, to match only the character string The when it is at the beginning of the comparison string, as shown in Example 5-7. Additionally, the .* sequence is then appended, to indicate any number of characters may match until the next following grouped expression. In this case, the .* sequence is followed by a parenthetically grouped pair of strings (rabbit and heart), which are separated by the | symbol, indicating that either of the strings will be considered a match.

Example 5-7. A more involved regular expression comparison

booktown=# SELECT title FROM books
booktown-#        WHERE title ~* '^The.*(rabbit|heart)';
        title
----------------------
 The Velveteen Rabbit
 The Tell-Tale Heart
(2 rows)

In Example 5-7, the results should fairly clearly indicate the effect of the regular expression comparison. Translated into English, the expression ^The.*(rabbit|heart) states that a match will be found only if the compared string begins with the character sequence The and, any amount of any characters thereafter, contain either the character sequence rabbit, or heart. The use of the ~* operator (rather than just the ~ operator) makes the comparison case-insensitive.

Example 5-8 executes an even more complicated regular expression comparison.

Example 5-8. A Complicated Regular Expression Comparison

booktown=# SELECT title FROM books
booktown-#        WHERE title ~* '(^t.*[ri]t)|(ing$|une$)';
        title
----------------------
 The Shining
 Dune
 The Velveteen Rabbit
 The Tell-Tale Heart
(4 rows)

booktown=#

The regular expression used in Example 5-8 is a good example of how regular expressions can be intimidating! Breaking it down an element at a time, you can see that there are two parenthetically grouped expressions, separated by a | symbol. This means that if either of these expressions are found to match the title, the comparison will be considered a match.

Breaking it down further, you can see that the expression to the left of the | symbol consists of, from left to right: a caret (^  ) followed by the character t, a period (.) followed by an asterisk (*  ), and a pair of square brackets ([] ) enclosing the characters r and i, followed by the character t. Translated into English, this sub-expression essentially says that in order to match, the compared string must begin with the letter t, and be followed by a sequence of zero or more characters until either the letter r, or i is found, which must be followed immediately by the letter t. If any of these conditions is not found, the comparison will not be considered a match.

The expression to the right of the | symbol is a bit simpler, consisting of two character string sequences (ing and une), each followed by the $ character, and separated by another | symbol. This sub-expression, translated into English, describes a match as a relationship in which either ends with the value ing, or une. If either of these are found, the expression is considered a match, because of the | symbol.

Numeric Operators

PostgreSQL's numeric operator support can be divided into three general groups:

Mathematical operators

Mathematical operators affect one or two values, perform a mathematical operation, and return a value of a numeric data type.

Numeric comparison operators

Numeric comparison operators draw a conclusion based on two numeric values (such as whether one is larger than the other) and returns a value of type boolean, set to either true or false.

Binary (or bit string) operators

Binary, or bit string, operators manipulate numeric values at the bit level of zeroes and ones. The following sections address each of these operator groups.

Mathematical operators

Mathematical operators can be used in the target list, in the WHERE clause of a SELECT statement, or anywhere else a numeric result may be appropriate. This sometimes will include the ORDER BY clause, a JOIN qualifier, or a GROUP BY clause.

Table 5-4 describes each of the mathematical operators available in PostgreSQL, along with example usage.

Table 5-4. Mathematical operators

OperatorUsageDescription

+

a + b

Addition of numeric quantities a and b

-

a - b

Subtraction of numeric quantity b from a

*

a * b

Multiplication of numeric quantities a and b

/

a / b

Division of numeric quantity a by b

%

a % b

Modulus, or remainder, from dividing a by b

^

a ^ b

Exponential operator, the value of a to the power of b

|/

|/ a

Square root of a

||/

||/ a

Cube root of a

!

a!

Factorial of a

!!

!! a

Factorial prefix, factorial of a, different only in syntactic placement from !

@

@ a

Absolute value of a

As an example of mathematical operators in the target list, the statement in Example 5-9 takes the retail price for each book and divides the cost with the / operator in order to determine the profit margin. This value is then typecast to a truncated numeric value with only two digits of precision. Finally, the integer constant 1 is subtracted from the division result, to yield only the percentage points over 100.

Example 5-9. Using Mathematical Operators

booktown=# SELECT isbn,
booktown-#        (retail / cost)::numeric(3, 2) - 1 AS margin
booktown-#        FROM stock
booktown-#        ORDER BY margin DESC
booktown-#        LIMIT 4;
    isbn    | margin
------------+--------
 0451457994 |   0.35
 0760720002 |   0.33
 0451198492 |   0.30
 0441172717 |   0.29
(4 rows)

Notice that the column name is temporarily aliased to margin by using the AS keyword. Remember that the column name created by the AS keyword is a temporary name, and used only for the duration of the query.

Numeric comparison operators

Comparison operators are used to compare values of types such as integer or text to one another, but they will always return a value of type boolean. These operators are most commonly used in the WHERE clause, but may be used anywhere in a SQL statement where a value of type boolean would be valid.

Table 5-5 shows the available comparison operators.

Table 5-5. Comparison operators

Operator

Description

<

Less-than, returns true if the value to the left is smaller in quantity than the value to the right

>

Greater-than, returns true if the value to the left is greater in quantity than the value to the right

<=

Less-than or equal-to, returns true if the value to the left is smaller, or equal to, in quantity than the value to the right

>=

Greater-than or equal-to, returns true if the value to the left is greater, or equal to, in quantity than the value to the right

=

Equal-to, returns true if the values to the left and right of the operator are equivalent

< > or !=

Not-equal, returns true if the values to the left and right of the operator not equivalent

Note: The < > operator exists as an alias to the != operator for functional compatibility with other SQL-capable database implementations. They are effectively identical.

For an example of mathematical comparison operator usage, observe Example 5-10. The query involved uses the <= operator first, to check if the retail value is less-than or equal-to 25. Subsequently, the != operator is employed with the AND keyword to ensure that only books which are in stock (whose stock value are not equal to 0) are returned.

Example 5-10. Using comparison operators

booktown=# SELECT isbn, stock
booktown-#        FROM stock
booktown-#        WHERE retail <= 25
booktown-#        AND stock != 0;
    isbn    | stock
------------+-------
 0441172717 |    77
 0590445065 |    10
 0679803335 |    18
 0760720002 |    28
 0929605942 |    25
 1885418035 |    77
(6 rows)

Numeric comparison keywords

The BETWEEN keyword (sometimes called an operator) allows you to check a value for existence within a range of values. For instance, Example 5-11 shows a SELECT statement that looks for books with cost between 10 and 17 dollars.

Example 5-11. Using BETWEEN

booktown=# SELECT isbn FROM stock
booktown-#        WHERE cost BETWEEN 10 AND 17;
    isbn
------------
 0394800753
 0441172717
 0451457994
(3 rows)

You can achieve the same output using the less-than-or-equal-to operator (<=) in conjunction with the greater-than-or-equal-to (>=) operator. See Example 5-12.

Example 5-12. Operator equivalents to BETWEEN

booktown=# SELECT isbn FROM stock
booktown-#        WHERE cost >= 10 AND cost <= 17;
    isbn
------------
 0394800753
 0441172717
 0451457994
(3 rows)

The BETWEEN syntax simply adds to the readability of an SQL statement. Since both the keyword and operator forms are equally valid to PostgreSQL, it's mostly a matter of user preference.

Binary operators

Binary operators perform bitwise operations on the literal bits of a bit string or integer. These operators may affect integer values, or directly on bit string values. Each of PostgreSQL's binary operators are described in Table 5-6.

Table 5-6. Bit-string operators

OperatorUsageDescription

&

a & b

Binary AND between bit string values of a and b (which may be provided as integers)

|

a | b

Binary OR between bit string values of a and b (which may be provided as integers)

#

a # b

Binary XOR between bit string values of a and b (which may be provided as integers)

~

~ b

Binary NOT, returns the inverted bit string of b

<<

b << n

Binary shifts b to the left by n bits

>>

b >> n

Binary shifts b to the right by n bits

Example 5-13 demonstrates shifting a numeric value, and its equivalent bit string, two bits to the right with the >> operator. It also demonstrates the use of the bittoint4() function, described in the Section called Functions."

Example 5-13. Shifting bit strings

booktown=# SELECT b'1000' >> 2 AS "8 shifted right",
booktown-#        bittoint4(b'1000' >> 2) AS integer,
booktown-#        8 >> 2 AS likewise;
 8 shifted right | integer | likewise
-----------------+---------+----------
 0010            |       2 |        2
(1 row)

Note: When shifting bit strings, the original length of the string does not change, and any digits pushed either to the left or right of the bit string will be truncated. When using &, |, or #, the bit strings operated on must be of equal length in order to properly compare each bit on a one-to-one basis.

Logical Operators

The AND, OR, and NOT keywords are PostgreSQL's Boolean operators. They are commonly used to join or invert conditions in a SQL statement, particularly in the WHERE clause and the HAVING clause.

Table 5-7 illustrates the Boolean values returned for the AND, OR, and NOT keywords, with each possible value for a Boolean field (true, false, or NULL).

Table 5-7. The AND, OR, and NOT operators

aba AND ba OR bNOT aNOT b
truetruetruetruefalsefalse
truefalsefalsetruefalsetrue
trueNULLNULLtruefalseNULL
falsefalsefalsefalsetruetrue
falseNULLfalseNULLtrueNULL
NULLNULLNULLNULLNULLNULL

Example 5-14 sequentially uses the OR and AND keywords in two queries to combine a pair of conditions by which rows should be retrieved. In the first query, if a book has either a cost of greater than thirty dollars, or is out of stock, its information will be returned. As you can see from the result set, matching one or both of these conditions causes a row to be returned.

The second query in Example 5-14 uses the same conditions, but combines them with the AND keyword. This results in a stricter condition, as both criteria must be met. As such, only one row is returned, since only one book is found which both has a cost of greater than thirty dollars, and is out of stock.

Example 5-14. Combining comparisons with Boolean operators

booktown=# SELECT isbn, cost, stock
booktown-#        FROM stock
booktown-#        WHERE cost > 30
booktown-#           OR stock = 0;
    isbn    | cost  | stock
------------+-------+-------
 0394900014 | 23.00 |     0
 044100590X | 36.00 |    89
 0451198492 | 36.00 |     0
 0451457994 | 17.00 |     0
(4 rows)

booktown=# SELECT isbn, cost, stock
booktown-#        FROM stock
booktown-#        WHERE cost > 30
booktown-#          AND stock = 0;
    isbn    | cost  | stock
------------+-------+-------
 0451198492 | 36.00 |     0
(1 row)

Using Operators with NULL

If a table has NULL values in it, a special pair of comparison operators that can be used to include or omit NULL valued fields. You can check for fields set to NULL using the IS NULL keyword phrase. In order to check for a non-NULL value, use the IS NOT NULL keyword phrase.

Example 5-15 uses the IS NULL keyword to check for authors whose first_name column value are set to NULL.

Example 5-15. Comparisons using IS NULL

booktown=# SELECT last_name, first_name
booktown-#        FROM authors
booktown-#        WHERE first_name IS NULL;
 last_name | first_name
-----------+------------
 Geisel    |
(1 row)

Examining Example 5-15 and Example 5-16, you might think that the syntax in the two statements provided are identical. There is, however, a key difference.

Example 5-16. Comparisons equal to NULL

booktown=# SELECT last_name, first_name
booktown-#        FROM authors
booktown-#        WHERE first_name = NULL;
 last_name | first_name
-----------+------------
 Geisel    |
(1 row)

PostgreSQL provides a translation from = NULL to IS NULL, and likewise for the != NULL operation with IS NOT NULL. This is provided only for compatibility with existing client applications (such as Microsoft Access).

When comparing a value to NULL in an expression, be in the habit of using the IS NULL and IS NOT NULL keyword operators rather than the = or != math-style operators. While this translation is provided for the sake of compatibility with other systems, it may be discontinued in the future, as it is not a standard SQL procedure (and it is certainly not guaranteed to be a portable procedure to other SQL-based database systems for the same reason).

Any as-yet undiscussed comparison operator used on a NULL value will return a NULL value, as NULL will never be larger, smaller, or otherwise related to any non-NULL value. (See Example 5-17.) A direct query on the result of a comparison against a NULL value will therefore return NULL. You can think of a NULL value as being a sort of SQL black hole, from which no comparison (outside of IS NULL, and its special = translation) may return true, and to which no values may be added, or concatenated.

Example 5-17. Using operators with NULL values

booktown=# \pset null *null* Null display is '*null*'.
booktown=# SELECT 5 > NULL;
 ?column?
----------
 *null*
(1 row)

booktown=# SELECT NULL IS NULL;
 ?column?
----------
 t
(1 row)

booktown=# SELECT NULL || 'Test';
 ?column?
----------
 *null*
(1 row)

Operator Precedence

When utilizing several operators in large expressions, it can be helpful to know in what order PostgreSQL processes operators. It is not, as you might think, strictly from left to right. If not properly understood, the order of execution can introduce potential for accidental side-effects, such as those shown in Example 5-18.

Example 5-18. Operator precedence

booktown=# SELECT 60 + 12 * 5 AS "sixty plus twelve times five",
booktown-#        12 + 60 * 5 AS "twelve plus sixty times five";
 sixty plus twelve times five | twelve plus sixty times five
------------------------------+------------------------------
                          120 |                          312
(1 row)

As you can see by the two column values returned in Example 5-18, the use of several operators without parentheses to enforce precedence can return very different results, despite the same numbers being manipulated in only a slightly different order. In this example, the multiplication is actually executed first (regardless of the fact that the addition sign (+) precedes it sequentially, from left to right).

Table 5-8 lists, in order of PostgreSQL's execution from the top down, the precedence of each group of operators.

Table 5-8. Operator precedence

OperatorUsageDescription

::

value::type

Explicit typecast

[ ]

value[index ]

Array element index

.

table.column

Table and column name separator

-

-value

Unary minus

^

value ^ power

Exponent

* / %

value1 * value2

Multiplication, division, and modulus

+ -

value1 + value2

Addition and subtraction

IS

value IS boolean

Compares against true or false

ISNULL

value ISNULL

Compares against NULL

IS NOT NULL

value IS NOT NULL

Checks for value inequivalent to NULL

Other

Variable

Includes all other native and user-defined character operators

IN

value IN set

Checks for membership of value in set

BETWEEN

value BETWEEN a AND b

Checks for value in range between values a and b

LIKE, ILIKE

string LIKE comparison

Checks for matching pattern comparison in string

< > <= >=

value1 < value2

Quantity comparisons for less than, greater than, less than or equal to, and greater than or equal to.

=

value1 = value2

Equality comparison

NOT

NOT value

Logical NOT inversion

AND

value1 AND value2

Logical AND conjunction

OR

value1 OR value2

Logical OR conjunction

Note: The operator precedence listed in Table 5-8 applies to user-defined operators that have the same character sequence as built-in operators. For example, if you define the plus symbol (+) operator for your own user-defined data type, it has the same precedence as the built in plus (+) operator, regardless of its function.


 
 
  Published courtesy of O'Reilly Design by Interspire