Mostly ANSI-compliant SQL with analytical extensions.
SELECT
→ SELECT, SELECT DISTINCT, SELECT DISTINCT ON
WHERE
→ Filter rows based on conditions
TIMESTAMP AS OF
→ Time travel queries (table version as of given
timestamp)
JOIN
→ INNER, LEFT, RIGHT, CROSS, LEFT SEMI, LEFT ANTI
GROUP BY → Group rows by columns
HAVING → Filter grouped results
ORDER BY → ORDER BY column ASC|DESC
LIMIT → Limit number of rows returned
OFFSET → Skip specified number of rows
UNION
→ UNION, UNION ALL to combine query results
Loading functions…
= → Equal to
<> → Not equal to
< → Less than
> → Greater than
<= → Less than or equal to
>= → Greater than or equal to
IN → Value is in a list
NOT IN → Value is not in a list
LIKE → String pattern matching
NOT LIKE → Negation of LIKE
ILIKE
→ Case-insensitive pattern matching
NOT ILIKE → Negation of ILIKE
RLIKE
→ Regular expression matching (aliases: ~, SIMILAR
TO)
NOT RLIKE
→ Negation of RLIKE (aliases: !~, NOT SIMILAR TO)
~*
→ Case-insensitive regular expression matching
IS
→ Special comparison for true, false, and null
BETWEEN
→ Test if value is within range (a BETWEEN x AND
y)
NOT BETWEEN
→ Test if value is outside range (a NOT BETWEEN x AND
y)
+
→ Addition (numeric, timestamp + interval)
-
→ Subtraction (numeric, timestamp - interval, timestamp
- timestamp)
* → Multiplication
/ → Division
DIV → Integer division
% → Modulo (remainder)
|| → String concatenation
& → Bitwise AND
|
→ Bitwise OR, or IP address containment
^ → Bitwise XOR
@>
→ Array contains any (set overlap)
@>> → Array contains all