Functions
Contents
Aggregate Functions
AVG
AVG(DOUBLE PRECISION) DOUBLE PRECISION
Returns the average value. If any of the expressions are NULL the result
will also be NULL.
Examples
SELECT AVG(price) FROM products;
-- COL1: 4.36666
SELECT city, AVG(price) FROM products GROUP BY city;
-- CITY: New York COL1: 8.96666
-- CITY: San Francisco COL1: 6.5
COUNT
COUNT(ANY) INTEGER
Count the number of non-NULL expressions.
There is a special form COUNT(*) that will count all rows.
Examples
These example show the difference between count all rows and only counting those
that have a non-NULL first_name.
SELECT COUNT(*) FROM people;
-- 12
SELECT COUNT(first_name) FROM people;
-- 10
MAX
MAX(DOUBLE PRECISION) DOUBLE PRECISION
Returns the maximum value. If any of the expressions are NULL the result
will also be NULL.
Examples
SELECT MAX(price) FROM products;
-- COL1: 20.45
SELECT city, MAX(price) FROM products GROUP BY city;
-- CITY: New York COL1: 18.05
-- CITY: San Francisco COL1: 17.5
MIN
MIN(DOUBLE PRECISION) DOUBLE PRECISION
Returns the minimum value. If any of the expressions are NULL the result
will also be NULL.
Examples
SELECT MIN(price) FROM products;
-- COL1: 10.45
SELECT city, MIN(price) FROM products GROUP BY city;
-- CITY: New York COL1: 8.05
-- CITY: San Francisco COL1: 7.5
SUM
SUM(DOUBLE PRECISION) DOUBLE PRECISION
Returns the sum (total) of all values. If any of the expressions are NULL
the result will also be NULL.
Examples
SELECT SUM(price) FROM products;
-- COL1: 487.75
SELECT city, SUM(price) FROM products GROUP BY city;
-- CITY: New York COL1: 196.35
-- CITY: San Francisco COL1: 291.4
Date & Time
CURRENT_DATE
See Dates and Times.
CURRENT_TIME
See Dates and Times.
CURRENT_TIMESTAMP
See Dates and Times.
LOCALTIME
See Dates and Times.
LOCALTIMESTAMP
See Dates and Times.
Mathematical Functions
ABS
ABS(DOUBLE PRECISION) DOUBLE PRECISION
ABS(NUMERIC) NUMERIC
Absolute value. When using NUMERIC the same scale and precision is returned.
Examples
VALUES ABS(1.2e0);
-- 1.2e0
VALUES ABS(-1.23e0);
-- 1.23e0
VALUES ABS(-1.23);
-- 1.23
ACOS
ACOS(DOUBLE PRECISION) DOUBLE PRECISION
Inverse (arc) cosine.
Examples
VALUES ACOS(0.2);
-- COL1: 1.369438
ASIN
ASIN(DOUBLE PRECISION) DOUBLE PRECISION
Inverse (arc) sine.
Examples
VALUES ASIN(0.2);
-- COL1: 0.201358
ATAN
ATAN(DOUBLE PRECISION) DOUBLE PRECISION
Inverse (arc) tangent.
Examples
VALUES ATAN(0.2);
-- COL1: 0.197396
CEIL
CEIL(DOUBLE PRECISION) DOUBLE PRECISION
CEIL(NUMERIC) NUMERIC
Round up to the nearest integer. When using NUMERIC the same scale and
precision is returned.
Examples
VALUES CEIL(3.7e0);
-- COL1: 4e0
VALUES CEIL(3.3e0);
-- COL2: 4e0
VALUES CEIL(-3.7e0);
-- COL3: -3e0
VALUES CEIL(-3.3e0);
-- COL4: -3e0
VALUES CEILING(3.7e0);
-- COL1: 4e0
VALUES CEIL(3.7);
-- COL1: 4
VALUES CEILING(-3.7);
-- COL1: -3
CEILING
CEILING(DOUBLE PRECISION) DOUBLE PRECISION
CEILING(NUMERIC) NUMERIC
CEILING is an alias of CEIL.
COS
COS(DOUBLE PRECISION) DOUBLE PRECISION
Cosine.
Examples
VALUES COS(1.2);
-- COL1: 0.362358
COSH
COSH(DOUBLE PRECISION) DOUBLE PRECISION
Hyperbolic cosine.
Examples
VALUES COSH(1.2);
-- COL1: 1.810656
EXP
EXP(DOUBLE PRECISION) DOUBLE PRECISION
Exponential.
Examples
VALUES EXP(3.7);
-- COL1: 40.447304
FLOOR
FLOOR(DOUBLE PRECISION) DOUBLE PRECISION
FLOOR(NUMERIC) NUMERIC
Round down to the nearest integer. When using NUMERIC the same scale and
precision is returned.
Examples
VALUES FLOOR(3.7e0);
-- COL1: 3e0
VALUES FLOOR(3.3e0);
-- COL1: 3e0
VALUES FLOOR(-3.7e0);
-- COL1: -4e0
VALUES FLOOR(-3.3e0);
-- COL1: -4e0
VALUES FLOOR(3.7);
-- COL1: 3
VALUES FLOOR(-3.7);
-- COL1: -4
LN
LN(DOUBLE PRECISION) DOUBLE PRECISION
Natural logarithm (base e).
Examples
VALUES LN(13.7);
-- COL1: 2.617396
LOG10
LOG10(DOUBLE PRECISION) DOUBLE PRECISION
Logarithm in base 10.
Examples
VALUES LOG10(13.7);
-- COL1: 1.136721
MOD
MOD(DOUBLE PRECISION, DOUBLE PRECISION) DOUBLE PRECISION
MOD(NUMERIC, NUMERIC) NUMERIC
Modulus. When using NUMERIC the result will have a precision that is the
highest precision between either parameter. The scale is undetermined.
Examples
VALUES MOD(232e0, 3e0);
-- COL1: 1e0
VALUES MOD(10.7e0, 0.8e0);
-- COL1: 0.3e0
VALUES MOD(10.7, 0.8);
-- COL1: 0.3
POWER
POWER(DOUBLE PRECISION, DOUBLE PRECISION) DOUBLE PRECISION
Power.
Examples
VALUES POWER(3.7, 2.5);
-- COL1: 26.333241
SIN
SIN(DOUBLE PRECISION) DOUBLE PRECISION
Sine.
Examples
VALUES SIN(1.2);
-- COL1: 0.932039
SINH
SINH(DOUBLE PRECISION) DOUBLE PRECISION
Hyperbolic sine.
Examples
VALUES SINH(1.2);
-- COL1: 1.509461
SQRT
SQRT(DOUBLE PRECISION) DOUBLE PRECISION
Square root.
Examples
VALUES SQRT(3.7);
-- COL1: 1.923538
TAN
TAN(DOUBLE PRECISION) DOUBLE PRECISION
Tangent.
Examples
VALUES TAN(1.2);
-- COL1: 2.572152
TANH
TANH(DOUBLE PRECISION) DOUBLE PRECISION
Hyperbolic tangent.
Examples
VALUES TANH(1.2);
-- COL1: 0.833655
String Functions
CHAR_LENGTH
CHAR_LENGTH(CHARACTER VARYING) INTEGER
Returns the character length (multibyte chatracters are counted as a single character).
VALUES CHAR_LENGTH('😊£');
-- COL1: 2
CHARACTER_LENGTH
CHARACTER_LENGTH(CHARACTER VARYING) INTEGER
CHARACTER_LENGTH is an alias of CHAR_LENGTH.
LOWER
LOWER(CHARACTER VARYING) CHARACTER VARYING
Returns the input string converted to lower-case.
VALUES LOWER('Hello');
-- COL1: hello
OCTET_LENGTH
OCTET_LENGTH(CHARACTER VARYING) INTEGER
Returns the byte length (multibyte chatracters are ignored).
VALUES OCTET_LENGTH('😊£');
-- COL1: 6
POSITION
POSITION(CHARACTER VARYING IN CHARACTER VARYING) INTEGER
Returns the start of the left most (first) match of one string within another. 1 will be the smallest index on a match and 0 is returned if the substring does not exist.
Matching is case-sensitive.
Examples
VALUES POSITION('He' IN 'hello Hello');
-- COL1: 7
VALUES POSITION('xx' IN 'hello Hello');
-- COL1: 0
SUBSTRING
SUBSTRING can be constructed in several forms:
SUBSTRING(
value
FROM start_position
[ FOR string_length ]
[ USING { CHARACTERS | OCTETS } ]
)
start_position starts at 1 for the first character or byte. If
start_position is out of bounds (either before the start or after the end)
the returned value will be empty.
If string_length is not provided, all characters or bytes until the end will
be included. Otherwise, only string_length will be included. If
string_length goes beyond the end of the string it will only be used until
the end.
If CHARACTERS is specified the start_position and string_length will
count in characters (this works with multibyte characters) whereas OCTETS
will strictly count in bytes. If USING is not provided, CHARACTERS will
be used.
VALUES SUBSTRING('hello' FROM 2);
-- COL1: ello
VALUES SUBSTRING('hello' FROM 20);
-- COL1:
VALUES SUBSTRING('hello world' FROM 3 FOR 5);
-- COL1: llo w
VALUES SUBSTRING('Жabڣc' FROM 4 USING OCTETS);
-- COL1: bڣc
TRIM
TRIM can be constructed in several forms:
TRIM(
[ [ { LEADING | TRAILING | BOTH } ] [ trim_character ] FROM ]
trim_source
)
If LEADING, TRAILING or BOTH is not provided, BOTH is used.
If trim_character is not provided, a space (’ ‘) is used.
VALUES TRIM(' hello world ');
-- COL1: hello world
VALUES TRIM('a' FROM 'aaababccaa');
-- COL1: babcc
VALUES TRIM(LEADING 'a' FROM 'aaababccaa');
-- COL1: babccaa
VALUES TRIM(TRAILING 'a' FROM 'aaababccaa');
-- COL1: aaababcc
UPPER
UPPER(CHARACTER VARYING) CHARACTER VARYING
Returns the input string converted to upper-case.
VALUES UPPER('Hello');
-- COL1: HELLO
Other Functions
COALESCE
COALESCE(VALUE, ...)
COALESCE returns the first value that is not NULL. If all values are
NULL then NULL is also returned.
VALUES COALESCE(1, 2);
-- COL1: 1
CURRENT_CATALOG
CURRENT_CATALOG
CURRENT_CATALOG always reflects the file name, only up to the first ..
So, if the complete file path is /tmp/mydb.cool.vsql the CURRENT_CATALOG
would be mydb.
When using in-memory databases, the CURRENT_CATALOG will be :memory:.
CURRENT_SCHEMA
CURRENT_SCHEMA
CURRENT_SCHEMA reports the current schema. The current schema is where
objects (such as tables, sequences, etc) are located or created. The default
schema is PUBLIC. The current schema can be changed with SET SCHEMA.
NULLIF
NULLIF(X, Y)
If X and Y are equal, NULL will be returned. Otherwise X is
returned.
NULLIF is equivilent to:
CASE WHEN X=Y THEN NULL ELSE X END
VALUES NULLIF(123, 123);
-- COL1: NULL
VALUES NULLIF(123, 456);
-- COL1: 123