Operators are logical, mathematical, and equality symbols used in SQL to evaluate, compare, or calculate values.
This is the multipage printable view of this section. Click here to print.
Operators
 1: Bitwise operators
 2: Logical operators
 3: Comparison operators
 4: Data type coercion operators (CAST)
 4.1: Cast failures
 5: Date/time operators
 6: Mathematical operators
 7: NULL operators
 8: String concatenation operators
1  Bitwise operators
Bitwise operators perform bit manipulations on INTEGER and BINARY/VARBINARY data types:
Operator  Description  Example  Result 

& 
AND 
12 & 4 
4 
 
OR 
32  3 
35 
# 
XOR 
17 # 5 
20 
~ 
NOT 
~1 
2 
<< ^{†} 
Bitwise shift left 
1 << 4 
16 
>> ^{†} 
Bitwise shift right 
8 >> 2 
2 
^{†} Invalid for BINARY/VARBINARY data types
String argument handling
String arguments must be explicitly cast as BINARY or VARBINARY data types for all bitwise operators. For example:
=> SELECT 'xyz'::VARBINARY & 'zyx'::VARBINARY AS AND;
AND

xyx
(1 row)
=> SELECT 'xyz'::VARBINARY  'zyx'::VARBINARY AS OR;
OR

zyz
(1 row)
Bitwise operators treats all string arguments as equal in length. If the arguments have different lengths, the operator function rightpads the smaller string with one or more zero bytes to equal the length of the larger string.
For example, the following statement ANDs unequal strings xyz
and zy
. Vertica rightpads string zy
with one zero byte. The last character in the result is represented accordingly, as \000
:
=> SELECT 'xyz'::VARBINARY & 'zy'::VARBINARY AS AND;
AND

xy\000
(1 row)
2  Logical operators
Vertica supports the logical operators AND, OR, and NOT:

AND evaluates to true when both of the conditions joined by AND are true.

OR evaluates to true when either condition is true.

NOT negates the result of any Boolean expression.
AND and OR are commutative—that is, you can switch left and right operands without affecting the result. However, the order of evaluation of subexpressions is not defined. To force evaluation order, use a CASE construct.
Caution
Do not confuse Boolean operators with the Boolean predicate or Boolean data type, which can have only two values: true and false.Logic
SQL uses a threevalued Boolean logic where NULL
represents "unknown":

true
ANDNULL
=NULL

true
ORNULL
=true

false
ANDNULL
=false

false
ORNULL
=NULL

NULL
ANDNULL
=NULL

NULL
ORNULL
=NULL

NOT
NULL
=NULL
3  Comparison operators
Comparison operators are available for all data types where comparison makes sense. All comparison operators are binary operators that return values of true, false, or NULL (unknown).
Operator  Description  Binary function 

< 
less than 
binary_lt 
> 
greater than 
binary_gt 
<= 
less than or equal to 
binary_le 
>= 
greater than or equal to 
binary_ge 
= , <=> 
equal NoteDo not use the negation operator ( 
binary_eq 
!= , <> 
not equal (unsupported for correlated subqueries) 
binary_ne 
NULL handling
Comparison operators return NULL (unknown) if either or both operands are null. One exception applies: <=>
returns true if both operands are NULL, and false if one operand is NULL.
Collections
When comparing collections, null collections are ordered last. Otherwise, collections are compared element by element until there is a mismatch, and then they are ordered based on the nonmatching elements. If all elements are equal up to the length of the shorter one, then the shorter one is ordered first.
4  Data type coercion operators (CAST)
Data type coercion (casting) passes an expression value to an input conversion routine for a specified data type, resulting in a constant of the indicated type. In Vertica, data type coercion can be invoked by an explicit cast request that uses one of the following constructs:
Syntax
SELECT CAST ( expression AS datatype )
SELECT expression::datatype
SELECT datatype 'string'
Parameters
expression 
An expression of any type 
datatype 
An SQL data type that Vertica supports to convert expression . 
Truncation
If a binary value is cast (implicitly or explicitly) to a binary type with a smaller length, the value is silently truncated. For example:
=> SELECT 'abcd'::BINARY(2);
?column?

ab
(1 row)
Similarly, if a character value is cast (implicitly or explicitly) to a character value with a smaller length, the value is silently truncated. For example:
=> SELECT 'abcd'::CHAR(3);
?column?

abc
(1 row)
Binary casting and resizing
Vertica supports only casts and resize operations as follows:

BINARY to and from VARBINARY

VARBINARY to and from LONG VARBINARY

BINARY to and from LONG VARBINARY
On binary data that contains a value with fewer bytes than the target column, values are rightextended with the zero byte '\0'
to the full width of the column. Trailing zeros on variablelength binary values are not rightextended:
=> SELECT 'ab'::BINARY(4), 'ab'::VARBINARY(4), 'ab'::LONG VARBINARY(4);
?column?  ?column?  ?column?
++
ab\000\000  ab  ab
(1 row)
Automatic coercion
The explicit type cast can be omitted if there is no ambiguity as to the type the constant must be. For example, when a constant is assigned directly to a column, it is automatically coerced to the column's data type.
Examples
=> SELECT CAST((2 + 2) AS VARCHAR);
?column?

4
(1 row)
=> SELECT (2 + 2)::VARCHAR;
?column?

4
(1 row)
=> SELECT INTEGER '123';
?column?

123
(1 row)
=> SELECT (2 + 2)::LONG VARCHAR
?column?

4
(1 row)
=> SELECT '2.2' + 2;
ERROR: invalid input syntax for integer: "2.2"
=> SELECT FLOAT '2.2' + 2;
?column?

4.2
(1 row)
See also
4.1  Cast failures
When you invoke data type coercion (casting) by an explicit cast and the cast fails, the result returns either an error or NULL. Cast failures commonly occur when you try to cast conflicting conversions, such as coercing a VARCHAR expression that contains letters to an integer.
When a cast fails, the result returned depends on the data type.
Data type  Cast failure default 

date, time  NULL 
literals  error 
all other types  error 
Enabling strict time casts
You can enable all cast failures to result in an error, including those for date/time data types. Doing so lets you see the reason why some or all of the cast failed. To return an error instead of NULL, set the configuration parameter EnableStrictTimeCasts to 1:
ALTER SESSION SET EnableStrictTimeCasts=1;
By default, EnableStrictTimeCasts is set to 0. Thus, the following attempt to cast a VARCHAR to a TIME data type returns NULL:
==> SELECT current_value from configuration_parameters WHERE parameter_name ilike '%EnableStrictTimeCasts%';
current_value

0
(1 row)
=> CREATE TABLE mytable (a VARCHAR);
CREATE TABLE
=> INSERT INTO mytable VALUES('one');
OUTPUT

1
(1 row)
=> INSERT INTO mytable VALUES('1');
OUTPUT

1
(1 row)
=> COMMIT;
COMMIT
=> SELECT a::time FROM mytable;
a

(2 rows)
If EnableStrictTimeCasts is enabled, the cast failure returns an error:
=> ALTER SESSION SET EnableStrictTimeCasts=1;
ALTER SESSION
=> SELECT a::time FROM mytable;
ERROR 3679: Invalid input syntax for time: "1"
Returning all cast failures as NULL
To explicitly cast an expression to a requested data type, use the following construct:
SELECT expression::datatype
Using this command to cast any values to a conflicting data type returns the following error:
=> SELECT 'one'::time;
ERROR 3679: Invalid input syntax for time: "one"
Vertica also supports the use of the coercion operator ::!
, which is useful when you want to return:

NULL instead of an error for any nondate/time data types

NULL instead of an error after setting EnableStrictTimeCasts
Returning all cast failures as NULL allows those expressions that succeed during the cast to appear in the result. Those expressions that fail during the cast, however, have a NULL value.
The following example queries mytable
using the coercion operator ::!
. The query returns NULL where column a
contains the string one
, and returns 1
where the column contains 1
. Query results are identical no matter how EnableStrictTimeCasts is set:
=> SELECT current_value FROM configuration_parameters WHERE parameter_name ilike '%EnableStrictTimeCasts%';
current_value

0
(1 row)
=> SELECT a::!int FROM mytable;
a

1
(2 rows)
ALTER SESSION SET EnableStrictTimeCasts=1;
ALTER SESSION
=> SELECT a::!int FROM mytable;
a

1
(2 rows)
You can use ::!
for casts of arrays and sets. The cast resolves each element individually, producing NULL for elements that cannot be cast.
Note that this functionality only applies to table data. It does not work on inline constant casts and in expressions automatically reduced to such. For example,
SELECT constant ::! FLOAT from (select 'some string' as constant) a;
results in ERROR 2826: Could not convert "some string" to a float8
. However, the following returns cast failures as NULL as described:
SELECT string_field ::! float FROM (SELECT 'a string' as string_field UNION ALL SELECT 'another string' ) a;
5  Date/time operators
Vertica supports usage of arithmetic operators on DATE/TIME operands:
+
(addition)
(subtraction)*
(multiplication)/
(division)
Examples
The operators described below that take TIME
or TIMESTAMP
input have two variants:

Operators that take
TIME WITH TIME ZONE
orTIMESTAMP WITH TIME ZONE
input. 
Operators that take
TIME WITHOUT TIME ZONE
orTIMESTAMP WITHOUT TIME ZONE
input.
For brevity, these variants are not shown separately.
The +
and *
operators come in commutative pairs—for example, both DATE
+ INTEGER
and INTEGER
+ DATE
. Only one of each pair is shown.
Example  Result Type  Result 

DATE '20010928' + INTEGER '7' 
DATE 
'20011005' 
DATE '20010928' + INTERVAL '1 HOUR' 
TIMESTAMP 
'20010928 01:00:00' 
DATE '20010928' + TIME '03:00' 
TIMESTAMP 
'20010928 03:00:00' 
INTERVAL '1 DAY' + INTERVAL '1 HOUR' 
INTERVAL 
'1 DAY 01:00:00' 
TIMESTAMP '20010928 01:00' + INTERVAL '23 HOURS' 
TIMESTAMP 
'20010929 00:00:00' 
TIME '01:00' + INTERVAL '3 HOURS' 
TIME 
'04:00:00' 
 INTERVAL '23 HOURS' 
INTERVAL 
'23:00:00' 
DATE '20011001' – DATE '20010928' 
INTEGER 
'3' 
DATE '20011001' – INTEGER '7' 
DATE 
'20010924' 
DATE '20010928' – INTERVAL '1 HOUR' 
TIMESTAMP 
'20010927 23:00:00' 
TIME '05:00' – TIME '03:00' 
INTERVAL 
'02:00:00' 
TIME '05:00' INTERVAL '2 HOURS' 
TIME 
'03:00:00' 
TIMESTAMP '20010928 23:00' – INTERVAL '23 HOURS' 
TIMESTAMP 
'20010928 00:00:00' 
INTERVAL '1 DAY' – INTERVAL '1 HOUR' 
INTERVAL 
'1 DAY 01:00:00' 
TIMESTAMP '20010929 03:00' – TIMESTAMP '20010927 12:00' 
INTERVAL 
'1 DAY 15:00:00' 
900 * INTERVAL '1 SECOND' 
INTERVAL 
'00:15:00' 
21 * INTERVAL '1 DAY' 
INTERVAL 
'21 DAYS' 
DOUBLE PRECISION '3.5' * INTERVAL '1 HOUR' 
INTERVAL 
'03:30:00' 
INTERVAL '1 HOUR' / DOUBLE PRECISION '1.5' 
INTERVAL 
'00:40:00' 
6  Mathematical operators
Mathematical operators are provided for many data types.
Operator  Description  Example  Result 

! 
Factorial 
5 ! 
120 
+ 
Addition 
2 + 3 
5 
– 
Subtraction 
2 – 3 
–1 
* 
Multiplication 
2 * 3 
6 
/ 
Division (integer division produces NUMERIC results). 
4 / 2 
2.00... 
// 
With integer division, returns an INTEGER rather than a NUMERIC. 
117.32 // 2.5 
46 
% 
Modulo (remainder). For details, see
MOD . 
5 % 4 
1 
^ 
Exponentiation 
2.0 ^ 3.0 
8 
/ 
Square root 
/ 25.0 
5 
/ 
Cube root 
/ 27.0 
3 
!! 
Factorial (prefix operator) 
!! 5 
120 
@ 
Absolute value 
@ 5.0 
5 
Factorial operator support
Vertica supports use of factorial operators on positive and negative floating point (DOUBLE PRECISION
) numbers and integers. For example:
=> SELECT 4.98!;
?column?

115.978600750905
(1 row)
Factorial is defined in terms of the gamma function, where (1) = Infinity and the other negative integers are undefined. For example:
(–4)! = NaN
–(4!) = –24
Factorial is defined as follows for all complex numbers z
:
z! = gamma(z+1)
For details, see Abramowitz and Stegun: Handbook of Mathematical Functions.
7  NULL operators
To check whether a value is or is not NULL, use the following equivalent constructs:
Standard:
[expression IS NULL  expression IS NOT NULL]
Nonstandard:
[expression ISNULL  expression NOTNULL]
Do not write expression
= NULL
: NULL represents an unknown value, and two unknown values are not necessarily equal. This behavior conforms to the SQL standard.
Note
Some applications might expect thatexpression
= NULL
returns true if expression
evaluates to NULL. In this case, modify the application to comply with the SQL standard.
8  String concatenation operators
To concatenate two strings on a single line, use the concatenation operator (two consecutive vertical bars).
Syntax
string  string
Parameters
string 
Expression of type CHAR or VARCHAR 
Notes
  is used to concatenate expressions and constants. The expressions are cast to
VARCHAR
if possible, otherwise toVARBINARY
, and must both be one or the other.  Two consecutive strings within a single SQL statement on separate lines are automatically concatenated
Examples
The following example is a single string written on two lines:
=> SELECT E'xx'> '\\';
?column?

xx\
(1 row)
The following examples show two strings concatenated:
=> SELECT E'xx' > '\\';
?column?

xx\\
(1 row)
=> SELECT 'auto'  'mobile';
?column?

automobile
(1 row)
=> SELECT 'auto'> 'mobile';
?column?

automobile
(1 row)
=> SELECT 1  2;
?column?

12
(1 row)
=> SELECT '1'  '2';
?column?

12
(1 row)
=> SELECT '1'> '2';
?column?

12
(1 row)