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

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

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

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

Operator Usage Description

::

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire