Built-in Functions

Built-in Functions

Comprehensive reference of all built-in functions, organized by category.

String Functions

UPPER, LOWER, LENGTH, CONCAT, CONCAT_WS, LEFT, RIGHT, TRIM, BTRIM, LTRIM, RTRIM, LPAD, RPAD, REPEAT, REPLACE, REVERSE, INITCAP, ASCII, CHR, STRPOS, SPLIT_PART, TRANSLATE, QUOTE_IDENT, QUOTE_LITERAL, QUOTE_NULLABLE, SUBSTRING, OVERLAY, POSITION, FORMAT

Mathematical Functions

ABS, CEIL/CEILING, FLOOR, ROUND, TRUNC, SQRT, CBRT, POWER, EXP, LN, LOG, MOD, SIGN, PI, DEGREES, RADIANS, SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, RANDOM, DIV, WIDTH_BUCKET, HASHTEXT

Date/Time Functions

DATE_PART / EXTRACT, DATE_TRUNC, DATE, AGE, TO_CHAR, NOW, CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, CLOCK_TIMESTAMP, TO_TIMESTAMP, MAKE_DATE, MAKE_TIME, MAKE_TIMESTAMP, TIMEZONE

Aggregate Functions

COUNT, SUM, AVG, MIN, MAX, STRING_AGG, ARRAY_AGG, BOOL_AND, BOOL_OR, EVERY, JSON_AGG, JSONB_AGG

Window Functions

ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE

All aggregate functions can also be used as window functions.

JSON/JSONB Functions

JSONB_BUILD_OBJECT, JSONB_BUILD_ARRAY, JSONB_TYPEOF, JSONB_ARRAY_LENGTH, JSONB_EXISTS, JSONB_EXISTS_ANY, JSONB_EXISTS_ALL, JSONB_OBJECT_KEYS, JSONB_EXTRACT_PATH, JSONB_EXTRACT_PATH_TEXT, JSONB_PRETTY, JSONB_SET, JSONB_ARRAY_ELEMENTS, JSONB_ARRAY_ELEMENTS_TEXT, JSONB_EACH, JSONB_EACH_TEXT, TO_JSON, TO_JSONB, ROW_TO_JSON, JSON_BUILD_OBJECT, JSON_BUILD_ARRAY

JSON Operators

Operator	Description	Example
->	Get JSON element by key/index	data->'name'
->>	Get JSON element as text	data->>'name'
#>	Get element by path	data#>'{addr,city}'
#>>	Get element by path as text	data#>>'{addr,city}'
#-	Delete key/path	data #- '{addr}'
@>	Contains	data @> '{"a":1}'
<@	Contained by	'{"a":1}' <@ data
?	Key exists	data ? 'email'
?|	Any key exists	data ?| array['a','b']
?&	All keys exist	data ?& array['a','b']

Array Functions

ARRAY_LENGTH, ARRAY_UPPER, ARRAY_LOWER, CARDINALITY, ARRAY_POSITION, ARRAY_CAT, ARRAY_APPEND, ARRAY_PREPEND, ARRAY_REMOVE, ARRAY_TO_STRING, STRING_TO_ARRAY, UNNEST

Regular Expression Functions

REGEXP_REPLACE, REGEXP_MATCHES, REGEXP_SPLIT_TO_ARRAY

Regular Expression Operators

Operator	Description
~	Matches regex (case-sensitive)
~*	Matches regex (case-insensitive)
!~	Does not match (case-sensitive)
!~*	Does not match (case-insensitive)

Full-Text Search

TO_TSVECTOR, TO_TSQUERY, PLAINTO_TSQUERY, PHRASETO_TSQUERY, WEBSEARCH_TO_TSQUERY, TS_RANK, TS_RANK_CD, TS_HEADLINE, SETWEIGHT

Operator: @@ (text search match)

UUID Functions

GEN_RANDOM_UUID, UUID_GENERATE_V4, UUIDV7

Encoding & Hashing

ENCODE, DECODE, MD5, SHA256, DIGEST

Vector Functions (pgvector-compatible)

L2_DISTANCE, INNER_PRODUCT, COSINE_DISTANCE, VECTOR_DIMS, VECTOR_NORM

Operators: <-> (L2 distance), <=> (cosine distance), <#> (negative inner product)

Embedding Functions

EMBEDDING(text [, model, dimensions]), EMBED_TEXT(model, text [, json_options])

Generate vector embeddings from text in SQL. Requires CREATE EXTENSION embedding. The server ships with a pre-configured embedding model — no API keys needed for default use. See Vector Search for end-to-end examples.

Sequence Functions

NEXTVAL, CURRVAL, SETVAL, LASTVAL, PG_GET_SERIAL_SEQUENCE

Conditional Expressions

COALESCE, NULLIF, GREATEST, LEAST, CASE WHEN ... THEN ... ELSE ... END

System / Compatibility Functions

PG_TYPEOF, VERSION, CURRENT_USER, CURRENT_DATABASE, CURRENT_SCHEMA, FORMAT_TYPE, PG_GET_EXPR, HAS_TABLE_PRIVILEGE, HAS_SCHEMA_PRIVILEGE, HAS_DATABASE_PRIVILEGE, PG_TOTAL_RELATION_SIZE, PG_RELATION_SIZE, PG_TABLE_SIZE, PG_SIZE_PRETTY, PG_ENCODING_TO_CHAR, PG_COLUMN_SIZE, OBJ_DESCRIPTION, COL_DESCRIPTION, PG_BACKEND_PID, GENERATE_SERIES

Background SQL

PG_BACKGROUND_LAUNCH, PG_BACKGROUND_RESULT

Launch SQL statements asynchronously in background sessions.