SQL REFERENCE

Scalar Functions

Built-in scalar functions for math, string manipulation, date/time operations, null handling, and vector similarity.

Math Functions

Function	Description	Example
ABS(x)	Absolute value	ABS(-1.0) → 1.0
CEIL(x) / CEILING(x)	Round up to nearest integer	CEIL(1.2) → 2.0
FLOOR(x)	Round down to nearest integer	FLOOR(2.7) → 2.0
ROUND(x)	Round to nearest integer	ROUND(2.7) → 3.0
ROUND(x, n)	Round to n decimal places	ROUND(3.14159, 2) → 3.14
SQRT(x)	Square root	SQRT(9.0) → 3.0
LN(x) / LOG(x)	Natural logarithm	LN(1.0) → 0.0
EXP(x)	Exponential (e^x)	EXP(0.0) → 1.0
LEAST(a, b, ...)	Minimum of arguments	LEAST(3, 1, 2) → 1
GREATEST(a, b, ...)	Maximum of arguments	GREATEST(3, 1, 2) → 3

SELECT ABS(x) FROM nums;
SELECT SQRT(x) FROM nums WHERE x > 0.0;
SELECT ROUND(price, 2) FROM products;
SELECT LEAST(a, b, c), GREATEST(a, b, c) FROM t;

String Functions

Function	Description	Example
UPPER(s)	Convert to uppercase	UPPER('abc') → 'ABC'
LOWER(s)	Convert to lowercase	LOWER('ABC') → 'abc'
LENGTH(s)	String length	LENGTH('hello') → 5
TRIM(s)	Remove leading/trailing whitespace	TRIM(' hi ') → 'hi'
SUBSTR(s, start, len)	Extract substring (1-based)	SUBSTR('Hello', 1, 3) → 'Hel'
REPLACE(s, from, to)	Replace all occurrences	REPLACE('aabaa', 'aa', 'x') → 'xbx'
CONCAT(a, b, ...)	Concatenate strings	CONCAT('abc', '!') → 'abc!'
a \|\| b	String concatenation operator	'foo' \|\| 'bar' → 'foobar'

Aliases: LEN, CHAR_LENGTH, CHARACTER_LENGTH (all equivalent to LENGTH); BTRIM (equivalent to TRIM); SUBSTRING (equivalent to SUBSTR).

SELECT UPPER(name) FROM users;
SELECT LENGTH(description) FROM products;
SELECT TRIM(raw_input) FROM imports;
SELECT SUBSTR(name, 1, 5) FROM users;
SELECT REPLACE(text, 'old', 'new') FROM docs;
SELECT first_name || ' ' || last_name FROM users;

Date/Time Functions

Function	Description	Return Type
CURRENT_DATE()	Current date (at query time)	DATE
CURRENT_TIMESTAMP() / NOW()	Current timestamp (at query time)	TIMESTAMP
EXTRACT(field FROM expr)	Extract a date/time component	INTEGER
DATE_TRUNC(unit, expr)	Truncate timestamp to specified precision	TIMESTAMP
DATE_DIFF(unit, start, end)	Difference between two timestamps in specified units	BIGINT

EXTRACT Fields

Supported fields for EXTRACT: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), DOY (day of year), EPOCH.

-- Extract individual date/time components
SELECT EXTRACT(YEAR FROM created_at) FROM events;
SELECT EXTRACT(MONTH FROM event_date) FROM calendar;
SELECT EXTRACT(DOW FROM event_date) FROM calendar;   -- Monday=1, Sunday=7 (ISO 8601)
SELECT EXTRACT(EPOCH FROM ts) FROM logs;              -- seconds since epoch

DATE_TRUNC Units

Supported units for DATE_TRUNC: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

-- Truncate to different precisions
SELECT DATE_TRUNC('month', created_at) FROM events;   -- first of month
SELECT DATE_TRUNC('hour', ts) FROM logs;               -- start of hour
SELECT DATE_TRUNC('year', event_date) FROM calendar;   -- Jan 1 of that year

DATE_DIFF

Compute the difference between two timestamps. Also available as DATEDIFF.

Supported units: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR.

-- Days between two timestamps
SELECT DATE_DIFF('day', start_ts, end_ts) FROM projects;

-- Months between dates
SELECT DATEDIFF('month', hire_date, CURRENT_TIMESTAMP) FROM employees;

Current Date/Time

-- Get current date and timestamp
SELECT CURRENT_DATE();
SELECT NOW();
SELECT CURRENT_TIMESTAMP();

Null-Handling Functions

Function	Description	Example
COALESCE(a, b, ...)	Return first non-null argument	COALESCE(NULL, 'default') → 'default'
NULLIF(a, b)	Return NULL if a = b, else a	NULLIF(0, 0) → NULL

-- Use COALESCE to provide default values
SELECT COALESCE(nickname, first_name, 'Unknown') FROM users;

-- Use NULLIF to turn sentinel values into NULL
SELECT NULLIF(status, 'N/A') FROM records;

Vector Similarity Functions

These functions operate on embedding columns (TD_F32 arrays) and compute similarity against a query vector specified as an ARRAY literal. See Vector Search for full details.

Function	Description
COSINE_SIMILARITY(col, ARRAY[...])	Cosine similarity between embedding column and query vector (1.0 = identical)
EUCLIDEAN_DISTANCE(col, ARRAY[...])	Euclidean (L2) distance between embedding column and query vector

-- Rank items by similarity to a query vector
SELECT name, COSINE_SIMILARITY(embedding, ARRAY[0.1, 0.2, 0.3]) AS sim
FROM items
ORDER BY sim DESC
LIMIT 10;

-- Find nearest neighbors by Euclidean distance
SELECT name, EUCLIDEAN_DISTANCE(embedding, ARRAY[0.1, 0.2, 0.3]) AS dist
FROM items
ORDER BY dist ASC
LIMIT 5;

Type Conversion

Use CAST or the :: shorthand to convert between types. See Data Types for supported conversions.

SELECT CAST(42 AS BIGINT);
SELECT CAST('2025-01-15' AS DATE);
SELECT CAST('12:30:00' AS TIME);
SELECT CAST('2025-06-15 09:30:00' AS TIMESTAMP);
SELECT 42::BIGINT;