Operators

An operator is similar to a function, but differs from a function in the following ways:
  • An operator is usually represented by a symbol, not a name.
  • An operator usually takes two arguments.
  • The arguments of an operator are usually located on the left and right of the operator symbol.

The following table describes the Netezza Performance Server SQL operators.

Table 1. Operators
Operator type Operator Symbol Example
Binary arithmetic Addition + 3+4 returns 7
Subtraction - 3-4 returns -1
Multiplication * 3*4 returns 12
Division /
3/4 returns 0
3/4.0 returns 0.750000
Exponentiation ^ 3^4 returns 81
** 3**4 returns 81
Modulo % 30%4 returns 2
Unary arithmetic Plus + +3 returns 3
Minus - -3 returns -3
Factorial ! 3! returns 6
Binary text Concatenate || 'ab'||'ef' returns 'abef'
Like ~~ 'ab'~~'ef' returns f (false)
like 'ab' like 'ef' returns f (false)
Not like !~~ 'ab'!~~'ef' returns t (true)
not like 'ab' not like 'ef' returns t (true)
Relational Equal = 3=4 returns f (false)
Not equal <> 3<>4 returns t (true)
!= 3!=4 returns t (true)
Greater than > 3>4 returns f (false)
Greater than or equal >= 3>=4 returns f (false)
Less than < 3<4 returns t (true)
Less than or equal <= 3<=4 returns t (true)
Netezza Performance Server SQL follows the operator precedence defined by Postgres. The standard arithmetic operators use the standard order of precedence:
  1. Exponentiation
  2. Multiplication, division, and modulo
  3. Addition and subtraction
Operators of the same precedence are evaluated from left to right. You can use parentheses to alter this precedence. For example, the following statements are equivalent:
5+7*30%4*2^3/2-1
(5+((((7*30)%4)*(2^3))/2))-1
The following table lists all operators in their order of precedence, and the associativity of each operator.
Table 2. Operator precedence
Operator Associativity Description
. Left Table or column name separator
:: Left Typecast (PostgreSQL extension to standard SQL)
- Right Unary minus
^ Left Exponentiation
* / % Left Multiplication, division, modulo
+ - Left Addition, subtraction
IS Left IS TRUE, IS FALSE, IS UNKNOWN, IS NULL, IS NOT NULL
ISNULL Left Test for null (PostgreSQL extension to standard SQL)
NOTNULL Left Test for not null (PostgreSQL extension to standard SQL.)
(other native operators) Left All other native operators
IN Left Set membership
BETWEEN Left Containment
OVERLAPS Left Time interval overlap
~~ or like Left String pattern matching
< <= > >= Left Less than, less than or equal to, greater than, greater than or equal to
= != <> Right Equality, assignment, inequality
NOT Right Logical negation
AND Left Logical conjunction
OR Left Logical disjunction