Functions

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