builtins-sql.adoc

SQL Builtins

Preface

This document contains the builtin functions as specified by the SQL standard, as well as a section on extended builtins which are not in the SQL spec, but are common amongst SQL implementations.

References

• https://www.postgresql.org/docs/current/functions-aggregate.html

• https://www.postgresql.org/docs/current/functions-string.html

• https://www.postgresql.org/docs/current/functions-math.html

Standard

Aggregations

Identifier	Names	Signatures	Description
COUNT_STAR	count	() → bigint	Returns the number of rows
COUNT	count	(x: any) → bigint	Returns the number of rows for which x is not null
SUM	sum	(x: T) → T T — any numeric, interval	Sum of x for all non-null input values
MIN	min	(x: T) → T T — any numeric, string, date/time	Minimum of x for all non-null input values
MAX	max	(x: T) → T T — any numeric, string, date/time	Maximum of x for all non-null input values
EVERY	every	(x: bool) → bool	Returns true if all x are true
ANY	any	(x: bool) → bool	??
SOME	some	(x: bool) → bool	??
STDDEV_POP	stddev_pop, stddev_population	(x: T) → double T — smallint, int, bigint, real, double precision, or numeric	Population standard deviation
STDDEV_SAMP	stddev_samp, stddev_sample, stddev	(x: T) → double T — smallint, int, bigint, real, double precision, or numeric	Sample standard deviation
VAR_POP	var_pop, var_population	(x: T) → double T — smallint, int, bigint, real, double precision, or numeric	Population variance
VAR_SAMP	var_samp, var_sample, variance	(x: T) → double T — smallint, int, bigint, real, double precision, or numeric	Sample variance
COVAR_POP	covar_pop, covar_population	(x: double, y: double) → double	Population covariance
COVAR_SAMP	covar_samp, covar_sample, covar	(x: double, y: double) → double	Sample covariance
CORR	corr	(x: double, y: double) → double	Correlation coefficient

Comparisons

All arguments must be comparable types T as defined by the SQL specification subclause 4.12.

Identifier	Names	Signatures	Description
LT	<	(x: T, y: T) → boolean	Returns true if x is less than y
LTE	<=	(x: T, y: T) → boolean	Returns true if x is less than or equal to y
GT	>	(x: T, y: T) → boolean	Returns true if x is greater than y
GTE	>=	(x: T, y: T) → boolean	Returns true if x is greater than or equal to y
EQ	=	(x: T, y: T) → boolean	Returns true if x is equal to y
NEQ	!=, <>	(x: T, y: T) → boolean	Returns true if x is not equal to y

Numeric

Identifier	Names	Signatures	Description
PLUS	+	(x: T) + (y: S)	Addition of two numeric types T and S
MINUS	-	(x: T) - (y: S)	Subtraction of two numeric types T and S
MULT	*	(x: T) * (y: S)	Multiplication of two numeric types T and S
DIV	/	(x: T) / (y: S)	Integer division of two numeric types T and S
ABS	abs	(x: T) → T	Returns the absolute value of x
MOD	mod	(x: T, y: T) → T	Returns x modulo y
CEILING	ceil, ceiling	(x: T) → T	Returns the smallest integer greater than or equal to x
FLOOR	floor	(x: T) → T	Returns the largest integer less than or equal to x
SQRT	sqrt	(x: T) → T T — double or numeric	Square root
EXP	exp	(x: T) → T T — double or numeric	Exponential function e^x
POWER	power	(x: double, y: double) → double	Returns x raised to the power of y
LN	ln	(x: T) → T T — double or numeric	Natural log of x

Strings

Identifier	Names	Signatures	Description
CONCAT	||	(x: text, y: text) → text	Concatenates x and y Special form: x || y
LOWER	lower	(x: text) → text	Converts x to lowercase
UPPER	upper	(x: text) → text	Converts x to uppercase
BIT_LENGTH	bit_length	(x: text) → text	Returns the number of bits in x
CHAR_LENGTH	char_length, character_length	(x: text) → text	Returns the number of characters in x
OCTET_LENGTH	octet_length	(x: text) → text	Returns the number of bytes in x
SUBSTRING	substring	(s: text, start: int, len: int) → text	Returns the substring of s starting at start index for len characters. If start is not specified, 0 is the default. If len is not specified, the substring will span to the end of s. Special form: SUBSTRING(s [FROM start] [FOR len])
SUBSTRING_PATTERN	substring	(s: text, pattern: text) → text (s: text, pattern: text, esc: text) → text	Extracts the first substring matching SQL regular expression Special form: SUBSTRING(s SIMILAR pattern ESCAPE esc)
TRIM	trim	(x: text, y: text) → text	Trims the characters y (a space by default) from either the start, end, or both ends of x. Special form: TRIM([LEADING|TRAILING|BOTH] [FROM] x [, y])
POSITION	position	(x: text, y: text) → text	Returns first starting index of y within x, or zero if it’s not present. Special form: POSITION(x IN y)
OVERLAY	overlay	(x: text, y: text, start: int) → text (x: text, y: text, start: int, count: int) → text	Replaces the content x with y starting at start and extending for count or the length of y. Overlay has the special form: OVERLAY(x PLACING y FROM start [FOR count])

Extended

Aggregations

-- Postgres regression functions 9.58
-- Trino approximate aggregations
-- https://trino.io/docs/current/functions/aggregate.html#approximate-aggregate-functions

Numeric

x % y                -- MOD(x, y)
x ^ y                -- POWER(x,y)
x & y                -- bitwise AND
x | y                -- bitwise OR
~x                   -- bitwise NOT
x << y               -- bitwise shift left
x >> y               -- bitwise shift right
SIGN(x)              -- sign
ROUND(x)             -- round to nearest integer
ROUND(x, d)          -- round to d decimal places
TRUNC(x)             -- truncate toward 0
TRUNC(x, d)          -- truncate to d decimal places
LOG(b, x)            -- log x base b
LOG(x)               -- log x base 10
GREATEST(collection) -- returns the largest in collection
LEAST(collection)    -- returns the smallest in collection
SIN(x)               -- sine
COS(x)               -- cosine
TAN(x)               -- tangent
COT(x)               -- cotangent
ASIN(x)              -- arc sine
ACOS(x)              -- arc cosine
ATAN(x)              -- arc tangent
PI()                 -- pi constant
TRUNC(x)             -- truncate to integer

Strings

ASCII(x)           -- convert character x to ASCII
CHR(x)             -- convert ASCII x to character
POSITION(x IN y)   -- alias for SUBSTRING_REGEX(x IN y FROM 0)
LENGTH(x)          -- number of characters in string
REPLACE(x, y, z)   -- replace all occurrences of y in x to z
REVERSE(x)         -- reverse x
TRIM(x)            -- alias for TRIM(BOTH ' ' FROM x)