PostgreSQL Functions Reference

PostgreSQL provides a robust set of built-in functions designed for complex string manipulation, advanced pattern matching, precise date/time arithmetic, JSON/JSONB processing, and mathematical computations.

Below is an operational reference guide covering PostgreSQL's most frequently used functions, including PL/pgSQL-specific behaviors and ANSI SQL equivalents.

1. String & Text Functions

PostgreSQL features extensive text-handling capabilities, supported by its native text data type and regular expression engine.

Function	Description & Example	Notes & Key Behavior
`string_agg()`	Concatenates row values into a single delimited string. `SELECT string_agg(email, ', ' ORDER BY created_at) FROM users;`	Accepts an optional `ORDER BY` clause inside the function call.
`concat()` / `concat_ws()`	Concatenates arguments. `concat_ws` uses the first argument as a separator. `SELECT concat_ws('-', '2026', '09', '27');`	Automatically converts non-text types and ignores `NULL` values.
`format()`	Formats arguments according to a format string (similar to C `sprintf`). `SELECT format('Hello %I, welcome to %s!', 'user_table', 'PostgreSQL');`	Uses `%s` for values, `%I` for safely escaping SQL identifiers, and `%L` for literals.
`substring()` / `substr()`	Extracts a portion of a string based on 1-based indexing or regex matching. `SELECT substring('PostgreSQL' FROM 1 FOR 8);` — Output: `'Postgres'`	Supports standard length extraction as well as POSIX regular expressions.
`length()` / `char_length()`	Returns the number of characters in a string. `SELECT char_length('PostgreSQL');` — Output: `10`	Counts characters, not bytes. Use `octet_length()` for byte count.
`split_part()`	Splits a string on a delimiter and returns the n-th field. `SELECT split_part('john.doe@example.com', '@', 2);` — Output: `'example.com'`	Uses a 1-based field index.
`regexp_replace()`	Replaces substrings matching a POSIX regular expression. `SELECT regexp_replace('Alpha 123 Beta', '\d+', '456');`	Accepts a global flag (`'g'`) to replace all occurrences.

2. Date & Time Functions

PostgreSQL offers comprehensive date/time handling with full support for microsecond precision and time zones.

Function	Description & Example	Notes & Key Behavior
`now()` / `transaction_timestamp()`	Returns the current date and time at the start of the current transaction. `SELECT now();`	Returns `TIMESTAMP WITH TIME ZONE`. Constant within a single transaction.
`clock_timestamp()`	Returns the actual current time at the moment the statement executes. `SELECT clock_timestamp();`	Changes during statement execution, unlike `now()`.
`date_trunc()`	Truncates a timestamp to a specified precision level, such as `'month'` or `'day'`. `SELECT date_trunc('month', now());`	Useful for grouping data by year, month, week, or hour.
`age()`	Calculates the elapsed time between two timestamps or from the current date. `SELECT age(TIMESTAMP '2000-01-01');`	Returns an `interval` formatted as years, months, and days.
`extract()` / `date_part()`	Retrieves subfields such as `year`, `month`, `dow`, and `epoch` from a date/time. `SELECT extract(dow FROM now());`	`dow` returns `0` for Sunday through `6` for Saturday.
`to_char()`	Formats a timestamp or number into a formatted text string. `SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');`	Pattern strings are case-sensitive, such as `YYYY` versus `yyyy`.
Interval Math	Supports direct addition and subtraction using the `INTERVAL` type. `SELECT now() + INTERVAL '7 days 2 hours';`	Useful for dynamic date calculations.

3. JSON & JSONB Functions

PostgreSQL provides native document-database capabilities via json and jsonb.

Function / Operator	Description & Example	Notes & Key Behavior
`->`	Extracts a JSON object field by key name or an array element by index. `SELECT '{"a": {"b": "foo"}}'::jsonb -> 'a';`	Returns a JSON/JSONB value.
`->>`	Extracts a JSON object field or array element as text. `SELECT '{"name": "Alice"}'::jsonb ->> 'name';` — Output: `'Alice'`	Returns a plain `text` value.
`#>` / `#>>`	Extracts a nested object at a specified path as JSON or text. `SELECT '{"a": {"b": "bar"}}'::jsonb #>> '{a, b}';` — Output: `'bar'`	The path is passed as a text array, such as `'{key1, key2}'`.
`jsonb_build_object()`	Builds a JSONB object from an alternating list of keys and values. `SELECT jsonb_build_object('id', 1, 'name', 'Item');`	Useful for constructing structured API payloads directly in SQL.
`jsonb_agg()`	Aggregates rows into a single JSONB array. `SELECT jsonb_agg(u) FROM users u;`	Converts entire row structures into JSON objects inside an array.
`jsonb_set()`	Returns an updated JSONB object with a value inserted or modified at a path. `SELECT jsonb_set('{"a": 1}'::jsonb, '{a}', '2');`	The target path must exist unless the create option is enabled.
`jsonb_array_elements()`	Expands a JSONB array into a set of JSONB values, one row per element. `SELECT * FROM jsonb_array_elements('[1, 2, 3]'::jsonb);`	Commonly used in `FROM` clauses to unnest JSON arrays.

4. Conditional & NULL Handling Functions

Function	Description & Example	Notes & Key Behavior
`coalesce()`	Evaluates arguments in order and returns the first non-null value. `SELECT coalesce(phone, mobile, 'N/A') FROM contacts;`	Standard ANSI SQL function.
`nullif()`	Returns `NULL` if two arguments are equal; otherwise returns the first argument. `SELECT nullif(total, 0);`	Useful for preventing divide-by-zero errors.
`greatest()` / `least()`	Selects the maximum or minimum value from a list of scalar expressions. `SELECT greatest(10, 25, 5);` — Output: `25`	Ignores `NULL` values unless all arguments evaluate to `NULL`.

5. Type Conversion Functions (`CAST` & `::`)

In addition to standard ANSI CAST(), PostgreSQL supports the shorthand :: type-casting operator:

-- Standard ANSI SQL CAST
SELECT CAST('123' AS INTEGER);

-- PostgreSQL shorthand operator
SELECT '123'::INTEGER;
SELECT '2026-09-27'::DATE;
SELECT '{"key": "val"}'::JSONB;

6. Array Functions

PostgreSQL supports native array data types (INTEGER[], TEXT[], etc.) and array-processing functions:

Function / Operator	Description & Example	Notes & Key Behavior
`ARRAY[...]`	Constructs an array from scalar values or subquery results. `SELECT ARRAY[1, 2, 3, 4];`	Constructs a standard PostgreSQL array.
`array_append()`	Appends an element to the end of an array. `SELECT array_append(ARRAY[1, 2], 3);` — Output: `{1, 2, 3}`	The concatenation operator `	` can also be used.
`array_to_string()`	Concatenates array elements into a delimited text string. `SELECT array_to_string(ARRAY['a', 'b', 'c'], '-');`	The second argument defines the delimiter.
`unnest()`	Expands an array into a set of rows, one row per array element. `SELECT unnest(ARRAY['Red', 'Green', 'Blue']);`	Converts array entries into individual rows for joins or filtering.