Functions

The functions below behave as in MySQL and MariaDB unless a note says otherwise. Names are not case-sensitive.

Aggregate functions

Any aggregate takes FILTER (WHERE cond): SUM(amount) FILTER (WHERE status = 'paid').

Window functions

ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE(n), LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and SUM, AVG, COUNT, MIN, MAX with OVER.

SELECT customer, amount,
       ROW_NUMBER() OVER (PARTITION BY customer ORDER BY trans_date DESC) AS rn,
       SUM(amount)  OVER (PARTITION BY customer ORDER BY trans_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales;

Frames take ROWS or RANGE with UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING. Named windows work: OVER w ... WINDOW w AS (...). Window functions are not allowed in WHERE, GROUP BY, HAVING or ON.

String

CONCAT, CONCAT_WS, LENGTH, CHAR_LENGTH, CHARACTER_LENGTH, OCTET_LENGTH, BIT_LENGTH, UPPER, UCASE, LOWER, LCASE, TRIM([LEADING | TRAILING | BOTH] x FROM s), LTRIM, RTRIM, SUBSTRING / SUBSTR / MID (also FROM ... FOR), LEFT, RIGHT, LPAD, RPAD, REPLACE, INSERT, REVERSE, REPEAT, SPACE, LOCATE, INSTR, POSITION(x IN s), SUBSTRING_INDEX, FIELD, FIND_IN_SET, MAKE_SET, ELT, ASCII, ORD, CHAR, CHR, HEX, UNHEX, BIN, OCT, CONV, STRCMP, FORMAT, QUOTE, SOUNDEX, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR.

Numeric

ABS, ROUND, TRUNCATE, TRUNC, FLOOR, CEIL, CEILING, SIGN, MOD, POW, POWER, SQRT, EXP, LN, LOG, LOG2, LOG10, PI, SIN, COS, TAN, COT, ASIN, ACOS, ATAN, ATAN2, DEGREES, RADIANS, GREATEST, LEAST, RAND, BIT_COUNT, CRC32.

ROUND of a DOUBLE rounds the value as written; see Support and differences.

Date and time

EXTRACT takes YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND and YEAR_MONTH.

Conditional and null handling

IF(cond, a, b), CASE ... END, IFNULL, NVL, COALESCE, NULLIF, ISNULL, INTERVAL(n, n1, n2, ...).

Conversion

CAST(x AS type), CONVERT(x, type), CONVERT(x USING charset), x::type, BINARY x.

CAST targets: SIGNED, UNSIGNED, INTEGER, DECIMAL(p,s), DOUBLE, FLOAT, REAL, CHAR(n), BINARY(n), DATE, DATETIME, TIME.

JSON

JSON_EXTRACT, JSON_UNQUOTE, JSON_VALUE, JSON_QUERY, JSON_OBJECT, JSON_ARRAY, JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_MERGE, JSON_MERGE_PRESERVE, JSON_MERGE_PATCH, JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_EXISTS, JSON_SEARCH, JSON_KEYS, JSON_LENGTH, JSON_DEPTH, JSON_TYPE, JSON_VALID, JSON_QUOTE, JSON_PRETTY, JSON_DETAILED, JSON_COMPACT, JSON_LOOSE.

doc->'$.path' is JSON_EXTRACT(doc, '$.path'); doc->>'$.path' also unquotes the result.

Hashing and encoding

MD5, SHA, SHA1, SHA2(s, bits), TO_BASE64, FROM_BASE64, AES_ENCRYPT, AES_DECRYPT (the default aes-128-ecb only), ENCODE, DECODE, COMPRESS, UNCOMPRESS, UNCOMPRESSED_LENGTH, RANDOM_BYTES, UUID.

Network

INET_ATON, INET_NTOA, IS_IPV4, IS_IPV6.

Geometry

WKT text in, WKT text out: ST_GEOMFROMTEXT, ST_POINTFROMTEXT, ST_LINESTRINGFROMTEXT, ST_POLYGONFROMTEXT, ST_ASTEXT, ST_ASWKT, POINT(x, y), ST_X, ST_Y, ST_LATITUDE, ST_LONGITUDE, ST_GEOMETRYTYPE, ST_SRID, ST_DISTANCE, ST_DISTANCE_SPHERE, ST_CONTAINS, ST_WITHIN, MBRCONTAINS, MBRWITHIN.

Full-text search

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('replica lag' IN NATURAL LANGUAGE MODE);

IN BOOLEAN MODE and WITH QUERY EXPANSION are accepted.

Session and server

DATABASE, SCHEMA, VERSION, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CONNECTION_ID, LAST_INSERT_ID, FOUND_ROWS, ROW_COUNT, CHARSET, COLLATION, BENCHMARK, SLEEP, VALUES(col), DEFAULT(col), NAME_CONST.

Named locks: GET_LOCK, RELEASE_LOCK, RELEASE_ALL_LOCKS, IS_FREE_LOCK, IS_USED_LOCK.

ASQL functions

Sequences

ASQL_CHANGES is how a client can tell cheaply whether to run a query again; Grain's live() uses it.

Not available

DATE_TRUNC, TO_CHAR, SPLIT_PART, MEDIAN, PERCENTILE_CONT, PERCENTILE_DISC, LISTAGG, UUID_SHORT and the INET6_ functions return "unknown function".