Scalar functions
Numeric, text, date/time, conversion, encoding and general scalar functions.
Scalar functions return one value for each input row. Function names are case-insensitive; overload selection depends on argument types.
Numeric and mathematical
| Functions | Purpose |
|---|---|
abs, sign, signbit | Magnitude and sign |
ceil, ceiling, floor, round, round_even, roundbankers, trunc, even | Rounding and truncation |
sqrt, cbrt, pow, power, exp, ln, log, log2, log10 | Powers and logarithms |
sin, cos, tan, cot, asin, acos, atan, atan2 | Trigonometry |
sinh, cosh, tanh, asinh, acosh, atanh | Hyperbolic functions |
degrees, radians, pi | Angle conversion and constants |
gcd, greatest_common_divisor, lcm, least_common_multiple, factorial, gamma, lgamma | Integer and special functions |
bit_count, isfinite, isinf, isnan, nextafter | Representation and boundary helpers |
greatest, least | Largest or smallest non-NULL candidate |
random | Pseudorandom value |
Arithmetic operators include +, -, *, /, integer division //, remainder %, exponentiation **, and bitwise &, |, xor, ~, <<, >> for compatible types.
Text
| Functions | Purpose |
|---|---|
length, char_length, octet_length, bit_length | String size |
lower, upper, casefold, strip_accents | Case and normalization |
trim, ltrim, rtrim, lpad, rpad | Whitespace, character trimming and padding |
substring, substr, left, right | Extract a section |
concat, concat_ws, format, printf | Build formatted text |
replace, translate, reverse, repeat | Transform text |
contains, starts_with, ends_with, prefix, suffix | Search and prefix/suffix tests |
strpos, instr, position, split_part, string_split | Locate or split text |
levenshtein, damerau_levenshtein, jaccard, jaro_similarity, jaro_winkler_similarity | Similarity and distance |
Pattern operators include LIKE, ILIKE, SIMILAR TO, GLOB and their negated forms. Escape user-supplied wildcard characters before constructing a pattern.
Regular expressions
regexp_escape, regexp_extract, regexp_extract_all, regexp_full_match, regexp_matches, regexp_replace and regexp_split_to_array support RE2-compatible patterns.
SELECT regexp_extract(path, '/orders/([0-9]+)', 1) AS order_id,
regexp_replace(lower(email), '\\s+', '', 'g') AS email
FROM requests;Date and time
| Functions and forms | Purpose |
|---|---|
current_date, current_time, current_timestamp, now, today | Current transaction time |
date_part, datepart, extract | Read a date/time field |
date_trunc, time_bucket | Truncate or bucket time |
date_add, date_diff, date_sub | Date/time arithmetic |
make_date, make_time, make_timestamp, make_timestamptz | Construct temporal values |
epoch, epoch_ms, epoch_us, epoch_ns | Epoch conversion |
strftime, strptime, try_strptime | Format and parse |
last_day, dayname, days_in_month, monthname, julian | Calendar helpers |
isfinite, isinf | Infinite temporal value tests |
Use AT TIME ZONE or timezone(zone, value) for zone conversion. Store instants as TIMESTAMPTZ; use TIMESTAMP for local civil time whose zone is intentionally external.
Date-part names include year, quarter, month, week, isoyear, isodow, day, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch, era, century, decade, millennium and supported time-zone parts. Matching convenience functions such as year(value), weekofyear(value) and weekday(value) are available where the input type provides that part.
Interval constructors include to_years, to_quarters, to_months, to_weeks, to_days, to_hours, to_minutes, to_seconds, to_milliseconds and to_microseconds.
Conversion and NULL handling
CAST, TRY_CAST, cast_to_type, typeof, coalesce, ifnull, nullif, constant_or_null, error and searched or simple CASE expressions cover conversion and conditional selection.
Binary, encoding and hashing
base64, from_base64, hex, from_hex, encode, decode, url_encode, url_decode, md5, md5_number, sha1, sha256 and hash operate on text or binary values according to their overload.
Cryptographic hashes are useful for integrity and deterministic identifiers. Do not use fast non-cryptographic hash as a password or security token hash.
Identifiers and utility
uuid, gen_random_uuid, uuid_extract_version, uuid_extract_timestamp, current_catalog, current_database, current_schema, current_schemas, version, alias and stats expose identifiers or session/query utility values. Availability of administrative utility functions depends on workspace permissions.
Enum values
enum_code, enum_first, enum_last, enum_range and enum_range_boundary inspect the declared ordering of an enum. Compare enum values directly when both operands share a compatible enum type; cast to text only when lexical comparison is actually intended.