Functions

Built-in functions let you clean, convert, and compute values inside the query, so that work does not have to move into your application. You can trim and normalize text, round numbers, bucket timestamps, extract JSON fields, generate UUIDs, and compare vectors in the same statement that reads or writes the rows. Many functions use a name you already know from PostgreSQL or MySQL, such as now(), date_trunc, and date_format.

A scalar function works anywhere a value is accepted: in the select list, in a WHERE filter, as a GROUP BY key, in a column default, and in a check constraint. Functions also nest:

SELECT upper(trim(name)) AS display_name
FROM robots
WHERE abs(year - 2000) <= 5;

Function names are not case-sensitive.

To add application-specific scalar functions written in C#, see C# extensions. The same extension system also supports table functions, aggregates, transactional row triggers, and stored procedures.

Categories

• Session Functions report the current database, authenticated user, role, and superuser status.
• String Functions inspect, normalize, slice, replace, and combine text.
• Bytes Functions encode, decode, inspect, and modify binary values.
• Math Functions round, truncate, compare, and calculate with numeric values.
• Hash Functions calculate hexadecimal and binary digests, HMACs, and CRC-32 checksums.
• Date/Time Functions construct, inspect, calculate, format, and parse dates and timestamps.
• Set-returning Functions produce rows in a FROM clause, including numeric and temporal series.
• JSON Functions validate JSON text and inspect or extract values with paths.
• JSONB functions build, inspect, change, expand, and aggregate JSONB documents with the PostgreSQL function names.
• Regex Functions match, extract, replace, count, and split text with regular expressions.
• Conversion Functions convert scalar values with functions or SQL CAST syntax.
• Null Functions select fallback values for nullable expressions.
• UUID Functions generate native version 4 and version 7 UUID values.
• Object ID Functions generate and convert native OID values.
• Array Functions inspect array length and test element membership.
• Sequence Functions allocate, inspect, and set values from named counters.
• Vector Functions inspect vector dimensions and compare vectors with distance and similarity metrics.

Window functions rank rows, compare neighboring values, and calculate totals while keeping individual rows. They use OVER (...) and are allowed in the select list and final ORDER BY, rather than wherever a scalar value is accepted.

Nulls and volatile functions

Nulls propagate: most functions return NULL when any argument is NULL. Null Functions covers the exceptions, such as coalesce and nullif, and each category page marks any others.

Some functions are volatile. Their result can change from one call, statement, or transaction to the next:

• Per call: gen_id(), gen_uuid_v4(), gen_uuid_v7(), random(), gen_random_bytes(n), clock_timestamp(), and sysdate().
• Per statement: statement_timestamp().
• Per transaction: now(), current_timestamp(), transaction_timestamp(), current_date(), unix_timestamp(), and unix_timestamp_ms().
• Sequence functions: nextval, currval, lastval, and setval.

Volatile functions such as gen_id() and now() work as column defaults. A per-call function is unsafe in a predicate that must stay stable across a scan.

The session functions vary by caller, not by row. See that page for how they interact with the result cache, DEFAULT, and CHECK.

Most scalar functions evaluate their arguments before the call. IF and CASE evaluate only the selected result. Conditional result types can be checked before rows are read; see conditional type rules.