Expressions
Arithmetic Expressions
The standard arithmetic expressions are supported for numerical values:
select 3+2; -- returns 5
select 3-2; -- returns 1
select 3*2; -- returns 6
select 3/2; -- returns 1
select 3::decimal(1,0)/2; -- returns 1.5000000000000000000
select 3%2; -- returns 1 (modulo/remainder)
A subset is also supported for dates/intervals:
select '2023-07-18'::date + interval '5 day'; -- returns 2023-07-23
select '2023-07-18'::date - interval '5 day'; -- returns 2023-07-13
Comparisons
Values can be compared using the standard comparison operations =,!= or <>,<, >, <=, >=.
In addition the following operations can be used to compare values:
x between y and z -- x>=y and x<=z
x not between y and z -- x<y or x>z
x in (1,2,3,4) -- returns true if x is one of 1,2,3,4
x is null -- true if x is null, false otherwise
x is not null -- false if x is null, true otherwise
Boolean Expressions
and, or, and not can be used as expected (including three-valued logic).
select null and false; --returns false
select null and true; --returns null
select null or true; --returns true
select null or false; --returns null
select not true; -- returns false
Casting
Values can be casted to a different type using one of the two cast constructs:
select cast(1.2 as string);
select '1.2'::float(32);
Note that the exact behavior depends on the source and destination types.
Case statement
The case statement can be used to implement switches based on a condition:
select x, case when x>42 then x/2 else 0 end from t; -- simple ternary expression: x>42?x/2:0
select x, case when x=1 then 10 when x=2 then 20 else 0 end from t; -- chained if ... elseif ... else
select x, case x when 1 then 10 when 2 then 20 end from integers; -- switch x case 1: ...
Coalesce
Coalesce returns the first non-null argument:
select coalesce(null,1); -- returns 1
Sub Queries
(Correlated) sub-queries are supported in the select and where clauses. The following kinds of sub-queries are supported:
(select ...) -- can return at most one value
exists(select ...) -- checks if any row is returned
not exists(select ...) -- checks that no row is returned
x in(select y from ...) -- is x in the returned values
x not in (select y from ...) -- is x not contained in the returned values
x = all(select y from ...) -- true if every y has the value of x
x = any(select y from ...) -- true if any y value has the value of x
Strings
select 'a' || 'b'; -- string concatenation -> 'ab'
select 'hello world' like 'hello %'; -- pattern matching -> true
Scalar Functions
Currently only few built-in functions are supported as others simply weren’t necessary until now.
| Function | Description | Example | Result |
|---|---|---|---|
date_part | extract a part of a date | date_part('year','2023-07-11') | 2023 |
substring | substring of string | substring('hello world',2,5) | "ello" |
abs | absolute value of numer | abs(-1.5) | 1.5 |
upper | transform string to upper case | upper('abc'::string) | "ABC" |
round | found numer to number of digits | round(1.0006,3) | 1.0010 |
hash | fast hash function | hash(42) | 539517765266996231 |