Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Compound Expressions

A compound expression is a series of simple expressions joined by arithmetic operators. A simple expression used in a compound expression must return a numeric value.


expression operator expression | (compound_expression)



A simple expression that evaluates to a value.


A compound arithmetic expression can be constructed using the following operators, in this order of precedence:

  • ( ) : parentheses to control the order of evaluation

  • + , - : positive and negative sign/operator

  • ^ , |/ , ||/ : exponentiation, square root, cube root

  • * , / , % : multiplication, division, and modulo operators

  • @ : absolute value

  • + , - : addition and subtraction

  • & , |, #, ~, <<, >> : AND, OR, NOT, shift left, shift right bitwise operators

  • ||: concatenation


Compound expressions may be nested using parentheses.


Examples of compound expressions include:

('SMITH' || 'JONES') sum(x) / y sqrt(256) * avg(column) rank() over (order by qtysold) / 100 (select (pricepaid - commission) from sales where dateid = 1882) * (qtysold)

Some functions can also be nested within other functions. For example, any scalar function can nest within another scalar function. The following example returns the sum of the absolute values of a set of numbers:


Window functions cannot be used as arguments for aggregate functions or other window functions. The following expression would return an error:

avg(rank() over (order by qtysold))

Window functions can have a nested aggregate function. The following expression sums sets of values and then ranks them:

rank() over (order by sum(qtysold))

On this page: