Functions

ScramDB provides 72 scalar functions, 10 aggregate functions, and 16 window functions.

Scalar Functions

String Functions (22)

Function	Syntax	Description
`LENGTH`	`LENGTH(str)`	Number of characters
`UPPER`	`UPPER(str)`	Convert to uppercase
`LOWER`	`LOWER(str)`	Convert to lowercase
`SUBSTRING`	`SUBSTRING(str FROM start FOR len)`	Extract substring
`TRIM`	`TRIM(str)`	Remove leading/trailing whitespace
`LTRIM`	`LTRIM(str)`	Remove leading whitespace
`RTRIM`	`RTRIM(str)`	Remove trailing whitespace
`BTRIM`	`BTRIM(str, chars)`	Remove specified characters from both ends
`CONCAT`	`CONCAT(str1, str2, ...)`	Concatenate strings
`CONCAT_WS`	`CONCAT_WS(sep, str1, str2, ...)`	Concatenate with separator
`REPLACE`	`REPLACE(str, from, to)`	Replace all occurrences
`POSITION`	`POSITION(substr IN str)`	Find substring position (1-based)
`LEFT`	`LEFT(str, n)`	First n characters
`RIGHT`	`RIGHT(str, n)`	Last n characters
`LPAD`	`LPAD(str, len, fill)`	Pad left to length
`RPAD`	`RPAD(str, len, fill)`	Pad right to length
`REPEAT`	`REPEAT(str, n)`	Repeat string n times
`REVERSE`	`REVERSE(str)`	Reverse string
`INITCAP`	`INITCAP(str)`	Capitalize first letter of each word
`SPLIT_PART`	`SPLIT_PART(str, delim, n)`	Extract nth field
`STARTS_WITH`	`STARTS_WITH(str, prefix)`	Check prefix
`ENDS_WITH`	`ENDS_WITH(str, suffix)`	Check suffix
`ASCII`	`ASCII(str)`	ASCII code of first character
`CHR`	`CHR(code)`	Character from ASCII code
`MD5`	`MD5(str)`	MD5 hash as hex string

Math Functions (27)

Function	Syntax	Description
`ABS`	`ABS(x)`	Absolute value
`CEIL` / `CEILING`	`CEIL(x)`	Round up to nearest integer
`FLOOR`	`FLOOR(x)`	Round down to nearest integer
`ROUND`	`ROUND(x, d)`	Round to d decimal places
`TRUNC`	`TRUNC(x, d)`	Truncate to d decimal places
`SQRT`	`SQRT(x)`	Square root
`CBRT`	`CBRT(x)`	Cube root
`POWER`	`POWER(x, y)`	x raised to power y
`EXP`	`EXP(x)`	e^x
`LN`	`LN(x)`	Natural logarithm
`LOG`	`LOG(base, x)`	Logarithm with base
`LOG10`	`LOG10(x)`	Base-10 logarithm
`MOD`	`MOD(x, y)`	Modulo (remainder)
`SIGN`	`SIGN(x)`	Sign (-1, 0, or 1)
`PI`	`PI()`	π constant
`DEGREES`	`DEGREES(radians)`	Radians to degrees
`RADIANS`	`RADIANS(degrees)`	Degrees to radians
`GCD`	`GCD(a, b)`	Greatest common divisor
`LCM`	`LCM(a, b)`	Least common multiple
`SIN`	`SIN(x)`	Sine
`COS`	`COS(x)`	Cosine
`TAN`	`TAN(x)`	Tangent
`ASIN`	`ASIN(x)`	Arc sine
`ACOS`	`ACOS(x)`	Arc cosine
`ATAN`	`ATAN(x)`	Arc tangent
`ATAN2`	`ATAN2(y, x)`	Two-argument arc tangent
`GREATEST`	`GREATEST(a, b, ...)`	Largest value
`LEAST`	`LEAST(a, b, ...)`	Smallest value

Date/Time Functions (16)

Function	Syntax	Description
`EXTRACT`	`EXTRACT(field FROM source)`	Extract date/time field
`DATE_PART`	`DATE_PART('field', source)`	Same as EXTRACT (function form)
`DATE_TRUNC`	`DATE_TRUNC('field', source)`	Truncate to specified precision
`NOW`	`NOW()`	Current timestamp
`CURRENT_DATE`	`CURRENT_DATE`	Current date
`CURRENT_TIMESTAMP`	`CURRENT_TIMESTAMP`	Current timestamp
`AGE`	`AGE(ts1, ts2)`	Interval between timestamps
`TO_CHAR`	`TO_CHAR(value, format)`	Format to string
`TO_DATE`	`TO_DATE(str, format)`	Parse date from string
`TO_TIMESTAMP`	`TO_TIMESTAMP(str, format)`	Parse timestamp from string
`MAKE_DATE`	`MAKE_DATE(year, month, day)`	Construct date
`MAKE_TIMESTAMP`	`MAKE_TIMESTAMP(y,m,d,h,m,s)`	Construct timestamp

EXTRACT fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, EPOCH, DOW (day of week), DOY (day of year), QUARTER, WEEK

Aggregate Functions

Function	Syntax	Description
`COUNT`	`COUNT(*)` or `COUNT(expr)`	Count rows
`COUNT(DISTINCT)`	`COUNT(DISTINCT expr)`	Count distinct values
`SUM`	`SUM(expr)`	Sum of values
`AVG`	`AVG(expr)`	Average
`MIN`	`MIN(expr)`	Minimum value
`MAX`	`MAX(expr)`	Maximum value
`STDDEV` / `STDDEV_SAMP`	`STDDEV(expr)`	Sample standard deviation
`STDDEV_POP`	`STDDEV_POP(expr)`	Population standard deviation
`VARIANCE` / `VAR_SAMP`	`VARIANCE(expr)`	Sample variance
`VAR_POP`	`VAR_POP(expr)`	Population variance
`STRING_AGG`	`STRING_AGG(expr, delimiter)`	Concatenate with delimiter
`BOOL_AND` / `EVERY`	`BOOL_AND(expr)`	True if all true
`BOOL_OR`	`BOOL_OR(expr)`	True if any true

All aggregate functions support FILTER (WHERE ...) clause:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count
FROM users;

Window Functions

Window functions operate over a partition of rows defined by OVER().

Ranking Functions

Function	Description
`ROW_NUMBER()`	Unique sequential number per partition
`RANK()`	Rank with gaps for ties
`DENSE_RANK()`	Rank without gaps for ties
`NTILE(n)`	Distribute rows into n buckets
`PERCENT_RANK()`	Relative rank (0 to 1)
`CUME_DIST()`	Cumulative distribution

Value Functions

Function	Description
`LAG(expr, offset, default)`	Value from preceding row
`LEAD(expr, offset, default)`	Value from following row
`FIRST_VALUE(expr)`	First value in window frame
`LAST_VALUE(expr)`	Last value in window frame
`NTH_VALUE(expr, n)`	Nth value in window frame

Aggregate Windows

All aggregate functions (SUM, COUNT, AVG, MIN, MAX) can be used as window functions:

SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_total,
    AVG(amount) OVER (PARTITION BY category ORDER BY date) AS running_avg
FROM sales;

Frame Clauses

-- Supported frame specifications
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW      -- default
ROWS BETWEEN N PRECEDING AND N FOLLOWING
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Scalar Functions​

String Functions (22)​

Math Functions (27)​

Date/Time Functions (16)​

Aggregate Functions​

Window Functions​

Ranking Functions​

Value Functions​

Aggregate Windows​

Frame Clauses​