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, date, timestamp, datetime
`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.

Note

In kdb+, timestamp, datetime and date are treated as a numeric types and as such can be used in arithmetic functions, etc. This is a notable difference from PostgreSQL which does not support this.

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
stddev	stddev(numeric_type) -> float	Calculates the sample deviation of a list of values.	stddev(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(positive_numeric) -> positive_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.	'c' between 'a' 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.

Miscellaneous Functions

Function	Input -> Output Type	Description	Example Query
`cast`	`cast(any_type as kdb+ Datatype)` -> boolean	Casts a value from one type to another type.	`cast(10.5 as bigint)` -> 11 `cast('2024-01-01 11:00:00' as date)` -> '2024-01-01'
`lower`	`lower(string_type)` -> string_type	Converts all characters in a string or symbol to be lower case.	`lower('ABC')` -> 'abc'
`upper`	`upper(string_type)` -> string_type	Converts all characters in a string or symbol to be upper case.	`upper('abc')` -> 'ABC'
`date_trunc`	`date_trunc(string, temporal_type)` -> datetime	Truncates temporal type to given precision.	`date_trunc('hour', '2020-10-17 01:01:01')` -> 2020-10-17 01:00:00
`date_part`	`date_part(string, temporal_type)` -> numeric	Retrieves subfields from a temporal.	`date_part('hour', '2020-10-17 21:01:01')` -> 21
`extract`	`extract(subfield from temporal_type)` -> numeric	Retrieves subfields from a temporal.	`extract(hour from '2020-10-17 21:01:01')` -> 21
`random`	`random()` -> numeric	Returns a random value in the range 0.0 <= x < 1.0.	`random()` -> 0.23195317712191965
`sedseed`	`sedseed(float_type)`	Sets the seed for subsequent random() calls; argument must be between -1.0 and 1.0, inclusive.	`sedseed(0.5)`

Differences from PostgreSQL

Casting to binary, varbinary, and image are not currently supported.
Calling date_trunc("millennium", time) on dates prior to 2000.01.01 will result in negative infinity -0W, as kdb+ cannot create a timestamp with year 1001.01.01.