Scalar functions

Last updated Oct 1, 2026 Scalar functions transform individual values and can be used in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses.

Core functions

arrow_cast

Casts an expression to a specific Arrow data type by string name.

SELECT arrow_cast(total_amount, 'Float32') AS amount_f32
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

arrow_typeof

Returns the Arrow data type name of an expression.

SELECT arrow_typeof(total_amount) AS amount_type,
       arrow_typeof(customer_id) AS id_type
FROM my_namespace.sales_data
LIMIT 1

coalesce

Returns the first non-NULL argument.

SELECT coalesce(department, region, 'unknown') AS first_val
FROM my_namespace.sales_data
LIMIT 5

get_field

Extracts a field from a struct by name.

SELECT get_field(named_struct('customer', customer_id, 'amount', total_amount), 'amount') AS amt
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

greatest

Returns the largest value from a list of arguments.

SELECT greatest(total_amount, unit_price, quantity) AS max_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

least

Returns the smallest value from a list of arguments.

SELECT least(total_amount, unit_price, quantity) AS min_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

named_struct

Creates a struct with named fields from key-value pairs.

SELECT named_struct('customer', customer_id, 'amount', total_amount) AS info
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

nullif

Returns NULL if both arguments are equal, otherwise returns the first argument.

SELECT nullif(department, 'Unknown') AS dept
FROM my_namespace.sales_data
LIMIT 5

nvl

Returns the second argument if the first is NULL. Alias: ifnull.

SELECT nvl(department, 'N/A') AS dept
FROM my_namespace.sales_data
LIMIT 5

nvl2

Returns the second argument if the first is not NULL, otherwise returns the third.

SELECT nvl2(department, 'has_dept', 'no_dept') AS dept_status
FROM my_namespace.sales_data
LIMIT 5

overlay

Replaces a substring at a given position.

SELECT customer_id,
       overlay(customer_id PLACING 'XX' FROM 1 FOR 2) AS masked
FROM my_namespace.sales_data
LIMIT 3

struct

Creates a struct with positional fields. Alias: row.

SELECT struct(customer_id, total_amount, region) AS info
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 1

Crypto functions

digest

Returns a hash of a string using a specified algorithm. Supported algorithms: md5, sha224, sha256, sha384, sha512.

SELECT customer_id, digest(customer_id, 'sha256') AS hash
FROM my_namespace.sales_data
LIMIT 1

md5

Returns the MD5 hash of a string.

SELECT customer_id, md5(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha224

Returns the SHA-224 hash of a string.

SELECT sha224(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha256

Returns the SHA-256 hash of a string.

SELECT customer_id, sha256(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha384

Returns the SHA-384 hash of a string.

SELECT sha384(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

sha512

Returns the SHA-512 hash of a string.

SELECT sha512(customer_id) AS hash
FROM my_namespace.sales_data
LIMIT 1

Datetime functions

current_date

Returns today's date. Alias: today.

SELECT current_date() AS today_date
FROM my_namespace.sales_data
LIMIT 1

current_time

Returns the current time. Precision is quantized to 10ms boundaries.

SELECT current_time() AS now_time
FROM my_namespace.sales_data
LIMIT 1

date_bin

Bins a timestamp into fixed-size intervals aligned to an origin.

SELECT date_bin(INTERVAL '1 hour', timestamp, '2025-01-01T00:00:00Z') AS hour_bin,
       COUNT(*) AS cnt
FROM my_namespace.sales_data
GROUP BY date_bin(INTERVAL '1 hour', timestamp, '2025-01-01T00:00:00Z')
ORDER BY hour_bin
LIMIT 5

date_part

Extracts a component from a timestamp. Alias: datepart.

Supported fields: year, month, day, hour, minute, second, millisecond, microsecond, week, dow, doy, quarter, epoch.

SELECT date_part('hour', timestamp) AS hr,
       date_part('minute', timestamp) AS mn
FROM my_namespace.sales_data
LIMIT 1

date_trunc

Truncates a timestamp to a specified unit. Alias: datetrunc.

Supported units: year, month, week, day, hour, minute, second.

SELECT date_trunc('day', timestamp) AS day_trunc, COUNT(*) AS cnt
FROM my_namespace.sales_data
GROUP BY date_trunc('day', timestamp)
ORDER BY day_trunc
LIMIT 5

from_unixtime

Converts a Unix epoch (seconds) to a timestamp.

SELECT from_unixtime(1770000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

make_date

Constructs a date from year, month, and day components.

SELECT make_date(2026, 3, 1) AS d
FROM my_namespace.sales_data
LIMIT 1

make_time

Constructs a time from hour, minute, and second components.

SELECT make_time(14, 30, 0) AS t
FROM my_namespace.sales_data
LIMIT 1

now

Returns the current timestamp. Aliases: current_timestamp.

Precision is quantized to 10ms boundaries.

SELECT now() AS current_ts
FROM my_namespace.sales_data
LIMIT 1

to_char

Formats a timestamp as a string using strftime format. Alias: date_format.

SELECT to_char(timestamp, '%Y-%m-%d %H:%M') AS formatted
FROM my_namespace.sales_data
LIMIT 1

to_date

Parses a date from a string using a format pattern.

SELECT to_date('2026-03-01', '%Y-%m-%d') AS d
FROM my_namespace.sales_data
LIMIT 1

to_local_time

Strips timezone information from a timestamp.

SELECT to_local_time(timestamp) AS local_ts
FROM my_namespace.sales_data
LIMIT 1

to_time

Parses a time from a string using a format pattern.

SELECT to_time('14:30:00', '%H:%M:%S') AS t
FROM my_namespace.sales_data
LIMIT 1

to_timestamp

Parses a timestamp from a string using a format pattern.

SELECT to_timestamp('2026-03-01 12:00:00', '%Y-%m-%d %H:%M:%S') AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_micros

Converts microseconds since Unix epoch to a timestamp.

SELECT to_timestamp_micros(1770000000000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_millis

Converts milliseconds since Unix epoch to a timestamp.

SELECT to_timestamp_millis(1770000000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_nanos

Converts nanoseconds since Unix epoch to a timestamp. Large values may overflow.

SELECT to_timestamp_nanos(1770000000000000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_timestamp_seconds

Converts seconds since Unix epoch to a timestamp.

SELECT to_timestamp_seconds(1770000000) AS ts
FROM my_namespace.sales_data
LIMIT 1

to_unixtime

Converts a timestamp to a Unix epoch (seconds).

SELECT to_unixtime(timestamp) AS epoch
FROM my_namespace.sales_data
LIMIT 1

Encoding functions

decode

Decodes a string to binary data. Supported encoding: base64.

SELECT decode('aGVsbG8=', 'base64') AS raw
FROM my_namespace.sales_data
LIMIT 1

encode

Encodes binary data to a string. Supported encoding: base64.

SELECT encode(CAST('hello' AS BYTEA), 'base64') AS b64
FROM my_namespace.sales_data
LIMIT 1

JSON functions

json_as_text

Returns any JSON value as unquoted text.

SELECT json_as_text(doc, 'description') AS description
FROM my_namespace.sales_data
LIMIT 5

json_contains

Returns true if the specified key path exists in the JSON.

SELECT customer_id, json_contains(doc, 'email') AS has_email
FROM my_namespace.sales_data
LIMIT 5

json_get

Extracts a value by key path. Returns a union type — use the typed variants (json_get_str, json_get_int, etc.) for predictable results.

SELECT json_get(doc, 'name') AS name
FROM my_namespace.sales_data
LIMIT 5

json_get_array

Returns a JSON array as a list of strings.

SELECT json_get_array(doc, 'tags') AS tags
FROM my_namespace.sales_data
LIMIT 5

json_get_bool

Returns a boolean value from a JSON column by key path.

SELECT json_get_bool(doc, 'active') AS is_active
FROM my_namespace.sales_data
LIMIT 5

json_get_float

Returns a float value from a JSON column by key path.

SELECT json_get_float(doc, 'price') AS price
FROM my_namespace.sales_data
LIMIT 5

json_get_int

Returns an integer value from a JSON column by key path.

SELECT json_get_int(doc, 'age') AS age
FROM my_namespace.sales_data
LIMIT 5

json_get_json

Returns nested JSON as a raw JSON string.

SELECT json_get_json(doc, 'metadata') AS metadata
FROM my_namespace.sales_data
LIMIT 5

json_get_str

Returns a string value from a JSON column by key path.

SELECT json_get_str(doc, 'name') AS name
FROM my_namespace.sales_data
LIMIT 5

json_length

Returns the length of a JSON array or object.

SELECT json_length(doc, 'items') AS item_count
FROM my_namespace.sales_data
LIMIT 5

Math functions

abs

Returns the absolute value of a number.

SELECT abs(total_amount - 500) AS distance_from_500
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

cbrt

Returns the cube root of a number.

SELECT cbrt(CAST(quantity AS DOUBLE)) AS cbrt_qty
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
LIMIT 5

ceil

Returns the smallest integer greater than or equal to a number.

SELECT ceil(total_amount) AS rounded_up
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

degrees

Converts radians to degrees.

SELECT degrees(pi()) AS full_circle
FROM my_namespace.sales_data
LIMIT 1

exp

Returns e raised to the given power.

SELECT exp(total_amount / 1000.0) AS exp_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

factorial

Returns the factorial of a non-negative integer.

SELECT factorial(5) AS fact5
FROM my_namespace.sales_data
LIMIT 1

floor

Returns the largest integer less than or equal to a number.

SELECT floor(total_amount) AS rounded_down
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

gcd

Returns the greatest common divisor of two integers.

SELECT gcd(12, 8) AS gcd_val
FROM my_namespace.sales_data
LIMIT 1

Hyperbolic functions

sinh, cosh, tanh, asinh, acosh, atanh

SELECT sinh(1.0) AS sh, cosh(1.0) AS ch, tanh(1.0) AS th
FROM my_namespace.sales_data
LIMIT 1

isnan

Returns true if the value is NaN.

SELECT isnan(0.0 / 0.0) AS is_nan
FROM my_namespace.sales_data
LIMIT 1

iszero

Returns true if the value is zero.

SELECT iszero(0.0) AS is_zero
FROM my_namespace.sales_data
LIMIT 1

lcm

Returns the least common multiple of two integers.

SELECT lcm(4, 6) AS lcm_val
FROM my_namespace.sales_data
LIMIT 1

ln