Skip to content

SQL2 Functions and Operators

Introduction

The SQL2 API supports a broad array of functions and operators for use within an SQL query. This page outlines the currently implemented functions and operators, and highlights any differences in behavior or functionality between versions 1 and 2 of the SQL implementation, as well as between SQL2 and PostgreSQL.

Warning

Only the Functions and Operators listed below are currently supported by SQL2.

Info

Functions used in SQL queries are case-insensitive. If for example you are taking a max on a table column, one can use max, MAX, or even mAx.

Datatypes

This section regularly mentions the datatypes that can be used as inputs to functions and the datatypes that those functions can return. The table below maps PostgreSQL datatypes to kdb+ datatypes:

PostgreSQL Datatype kdb+ Datatype
bigint long
int int
smallint short
tinyint byte
bit boolean
decimal real/float
numeric real/float
datetime timestamp
smalldatetime datetime
date date
varchar string
text string
nchar string
nvarchar string
ntext string

The following shorthand notations are often used to refer to multiple types:

PostgreSQL Shorthand Notation kdb+ Datatype
numeric_type long, int, short, byte, boolean, real
positive_numeric A numeric_type with value greater than 0
temporal_type timestamp, datetime, date
any_type all types

For more information on kdb+ datatypes, visit the kdb+ datatypes documentation.

Warning

PostgreSQL types such as binary, varbinary, and image are not currently supported.

Aggregations

The following functions are commonly used with GROUP BY to aggregate a series of values into a single atomic value.

Function Input -> Output Type Description Example Query
sum sum(numeric_type) -> numeric_type Computes the sum of numeric values. sum(array[10, 20]) -> 30
avg avg(numeric_type) -> float Computes the average of numeric values. avg(array[10, 20]) -> 15
count count(any_type) -> long Returns the count of elements passed to it. count(array[10, 20]) -> 2
first first(any_type) -> any_type Returns the first element of a list. first(array[10, 20]) -> 10
last last(any_type) -> any_type Returns the last element of the list. last(array[10, 20]) -> 20
min min(numeric_type)-> numeric_type
min(temporal_type)-> temporal_type		 Returns the lowest value from a list. min(array[10, 5, 25, 20]) -> 5
max max(numeric_type)-> numeric_type
max(temporal_type)-> temporal_type		 Returns the largest value from a list. max(array[10, 5, 25, 20]) -> 25
prod prod(numeric_type) -> numeric_type Multiplies all the elements of a list together. prod(array[10, 5, 25, 20]) -> 250000
var var(numeric_type) -> float Calculates the statistical variance of a list of values. var(array[10, 20, 30]) -> 66.66667
dev dev(numeric_type) -> float Calculates the standard deviation of a list of values. dev(array[10, 20, 30]) -> 8.164966
sdev sdev(numeric_type) -> float Calculates the sample deviation of a list of values. sdev(array[10, 20, 30]) -> 10
svar svar(numeric_type) -> float Calculates the sample variance of a list of values. svar(array[10, 20, 30]) -> 100
wsum wsum(numeric_type, numeric_type) -> float Calculates the weighted sum of a list of values. wsum(array[1, 2, 3], array[10, 20, 30]) -> 140
wavg wavg(numeric_type, numeric_type) -> float Computes the weighted average. wavg(array[1, 2, 3], array[10, 20, 30]) -> 23.333333

Differences from PostgreSQL

  • The current implementation does not support using min (or max) on symbols and strings to find the first (or last) element of the list in lexicographical order.

  • The aggregation prod() behaves the same way as the q implementation of prd().

Mathematical Functions

The SQL2 API provides a variety of mathematical operators and functions. These are described in the following table:

Function Input -> Output Type Description Example Query
abs abs(numeric_type) -> numeric_type Returns the absolute value of a number. abs(-25) -> 25
ceil ceil(numeric_type) -> long Rounds a number up to nearest long. ceil(12.5) -> 13
ceiling ceiling(numeric_type) -> long Rounds a number up to nearest long. ceiling(12.5) -> 13
floor floor(numeric_type) -> long Rounds a number down to nearest long. floor(12.5) -> 12
round round(numeric_type, long) -> float Rounds a number to as many decimal places as second argument. round(10.325, 2) -> 10.33
width_bucket[2] width_bucket(numeric_type, numeric_type[]) -> long
width_bucket(temporal_type, temporal_type[]) -> long		 Returns the bucket number to which the first value belongs in based on the array list of values in the second argument. width_bucket(35, array[0, 25, 50, 75, 100]) -> 2
width_bucket[4] width_bucket(numeric_type, numeric_type, numeric_type, numeric_type) -> numeric_type
width_bucket(temporal_type, temporal_type, temporal_type, temporal_type) -> temporal_type		 Converts a numerical or temporal value into a number representing which bucket it would be assigned to. First argument defines the expression to bucket, second and third arguments set the min and max values, and the last argument sets the number of buckets. width_bucket(35, 0, 100, 4) -> 2
neg neg(numeric_type) -> numeric_type Returns the negative value of a number. neg(-10) -> 10
degrees degrees(numeric_type) -> numeric_type Converts a value in degrees to radians. degrees(3.141593) -> 180.
radians radians(numeric_type) -> numeric_type Converts a value in radians to degrees. radians(180) -> 3.141593.
factorial factorial(postive_numeric) -> postive_numeric Applies the factorial function n!. Must be a positive number. factorial(4) -> 24
div div(numeric_type, numeric_type) -> long Returns the long remainder of a number divided by another number. div(10, 3) -> 3
log log(numeric_type) -> float Performs a logarithm with base 10. log(10) -> 1
log10 log10(numeric_type) -> float Performs a logarithm with base 10. log10(100) -> 2
ln log(numeric_type) -> float Performs a logarithm with base 'e'. ln(2.718) -> 0.99989...
exp exp(numeric_type) -> float Performs an exponent with number 'e'. exp(1) -> 2.71828...
gcd gcd(long, long)-> long Calculates the greatest common divisor between two numbers, or two lists of numbers. gcd(10, 20) -> 10
lcm lcm(long, long) -> long Calculates the least common multiple between two numbers, or two lists of numbers. lcm(75, 50) -> 150
pi pi() -> float Returns the value of pi, e.g 3.14159.. pi() -> 3.14159...
power power(numeric_type, numeric_type) -> float Computes the first argument to the power of the second argument. power(10, 2) -> 100
trunc[1] trunc(numeric_type) -> long Returns the integer part of a number. trunc(10.25368) -> 10
trunc[2] trunc(numeric_type, long) -> numeric_type Returns the first argument to as many decimal points as the second argument. trunc(10.25368, 2) -> 10.25
sqrt sqrt(numeric_type) -> float Returns the square root of a numeric type. sqrt(25) -> 5
cbrt cbrt(numeric_type) -> float Returns the cubed root of a numeric type. cbrt(125) -> 5

Differences from PostgreSQL

  • The trunc[2] and round functions may return an incorrect number of decimal places depending on the \P setting of the kdb console receiving the data. For example, in test done on kdb+ with a \P of 17, the following call round(10.2536851231561315315, 13) returned a value of 10.253685123156099 instead of 10.2536851231561. This is an artifact of the way kdb+ handles floats and display precision. Read more here

Mathematical Operators

The following operators are used in expressing arithmetical expressions or to do bitwise operations.

Function Input -> Output Type Description Example Query
+ numeric_type + numeric_type -> numeric_type Performs addition on the two arguments. 10 + 5 -> 15
- numeric_type - numeric_type -> numeric_type Performs subtraction on the two arguments. 10 - 5 -> 5
* numeric_type * numeric_type -> numeric_type Performs multiplication on the two arguments. 10 * 5 -> 50
/ numeric_type / numeric_type -> numeric_type Performs division on the two arguments. 10 / 5 -> 2
@ @ numeric_type -> numeric_type Returns the absolute value of a number. Operator form of abs. @ -25 -> 25
% numeric_type % numeric_type -> numeric_type Performs a modulo operation of first argument modulo second argument. 9 % 5 -> 4
^ numeric_type ^ numeric_type -> numeric_type Performs an exponent operation of first argument to the power of the second argument. 2 ^ 3 -> 8
\|/ \|/ numeric_type -> float Takes the square root of a number. \|/ 25 -> 5
\|\|/ \|\|/ numeric_type -> float Takes the cubed root of a number. \|\|/ 125 -> 5
& numeric_type & numeric_type -> numeric_type Does a bitwise AND of the two arguments. 42 & 35 -> 34
\| numeric_type \| numeric_type -> numeric_type Does a bitwise OR of the two arguments. 42 \| 35 -> 43
~ ~ numeric_type -> numeric_type Does a bitwise NOT. ~ 42 -> -43
# # numeric_type -> numeric_type Does a bitwise XOR. 42 # 35 -> 9
<< numeric_type << numeric_type -> numeric_type Performs a bitwise left shift. 35 << 2 -> 140
>> numeric_type >> numeric_type -> numeric_type Performs a bitwise right shift. 35 >> 2 -> 8

Logical Operators

Named functions to specifically perform logical operations after the WHERE clause of a query are listed below:

Function Input Type Description Example Query
AND any_type condition AND any_type condition Data set returned from the query must comply with all conditions applied in the WHERE clause. SELECT * FROM table WHERE condition1 AND condition2 ... AND condition_n
OR any_type condition OR any_type condition Data set returned from query must comply with at least one of the conditions applied in the WHERE clause. SELECT * FROM table WHERE condition1 OR condition2 ... OR condition_n
NOT NOT any_type condition Data set will exclude data that is specified in the NOT condition. SELECT * FROM table WHERE NOT condition1

Comparison Operators and Functions

Function Input -> Output Type Description Example Query
= any_type = any_type -> boolean Equality operator. Checks whether arguments are equal to each other. 10 = 11 -> false
!= any_type != any_type -> boolean Non-equality operator. Checks whether arguments are not equal to each other. 10 != 11 -> true
> any_type > any_type -> boolean Greater than operator. Checks whether left argument is larger than right argument. 10 > 11 -> false
< any_type < any_type -> boolean Less than operator. Checks whether left argument is smaller than right argument. 10 < 11 -> true
>= any_type >= any_type -> boolean Greater than or equal to operator. Checks whether the left argument is larger than or equal to right argument. 10 >= 11 -> false
<= any_type <= any_type -> boolean Less than or equal to operator. Checks whether the left argument is smaller than or equal to right argument. 10 <= 11 -> true
in any_type in any_type[] Returns the values in the left argument are in the list of the right argument. 'ABC' in ('ABD', 'DEF') -> false
'ABC' in ('ABD', 'DEF', 'ABC') -> true
!< any_type !< any_type -> boolean Returns whether the values in the left argument are not less than the arguments on the right. 100.0 !< 120 -> false
!> numeric_type !> numeric_type Returns whether the values in the left argument are not greater than the arguments on the right. 100.0 !> 120 -> true
between any_type BETWEEN any_type AND any_type Returns if the left argument is between the two right arguments. 'a' between 'c' AND 'd' -> true
10 between 5 9 -> false
like string_type like 'SEARCH_STRING' The kdb+ string or symbol text matching operator. Does not support full set of regex. See here. select avg(price) from trade where sym like 'MS*'
select avg(price) from trade where sym like '*LE'
select avg(price) from trade where sym like '*L*'

Differences from SQL

  • Unlike SQL, the like operator in SQL2 does not support the full PostgreSQL regex. Instead, it behaves like the kdb+ function like. For more information, see kdb+ like function.
  • The sqrt and cbrt functions currently do not support negative numbers.

Differences from PostgreSQL

  • The like operator in SQL2 does not support the full PostgreSQL regex. Instead, it behaves like the kdb+ function like. For more information, see kdb+ like function.
  • The = operator in SQL2 cannot be used on string types, unlike in PostgreSQL. Use the in operator instead.
  • The > operator does not work on alphabetical lists.

Differences from PostgreSQL

  • Casting to binary, varbinary, and image are not currently supported.