Reference · 122 entries

PostgreSQL Reference

The PostgreSQL 18 function reference: string, math, date/time, aggregate, JSON, array, UUID and window functions with syntax, the version that added each, and real query results.

PostgreSQL 18 Version history

Every example was run on PostgreSQL 18 against the sample shop database, with dates stored as date and prices as numeric(10,2), and the results are shown the way psql prints them (with NULL written out). Pages show the PostgreSQL version that added or changed a function, linked to the SQL version history. Examples can't run in the browser here; to try them, use psql or any PostgreSQL client.

String Functions

PostgreSQL string functions and operators: length(), substring(), position(), split_part(), concat(), format(), the || operator, ILIKE and regular expressions.

Name	Description
`LENGTH()`	Return the number of characters in a string. char_length() is the standard name; octet_length() counts bytes.
`LOWER()`	Convert a string to lowercase using the database's locale, so letters such as É and Ü work too.
`UPPER()`	Convert a string to uppercase using the database's locale, including accented letters such as é.
`INITCAP()`	Capitalize the first letter of each word and lowercase the rest, as in 'ada lovelace' to 'Ada Lovelace'.
`\|\|`	Join strings with the \|\| operator. If either side is NULL the result is NULL; use concat() to skip NULLs instead.
`CONCAT()`	Join any number of values into one string, treating NULL arguments as empty strings.
`CONCAT_WS()`	Join values with a separator between them, skipping NULLs. The first argument is the separator.
`FORMAT()`	Build a string from a template: %s inserts text, %I a quoted identifier and %L a quoted literal, for safe dynamic SQL.
`SUBSTRING()`	Extract part of a string by 1-based position and length, or the part matching a regular expression.
`LEFT()`	Return the first n characters of a string. A negative n returns all but the last \|n\| characters.
`RIGHT()`	Return the last n characters of a string. A negative n returns all but the first \|n\| characters.
`POSITION()`	Return the 1-based position of a substring inside a string, or 0 if it isn't found. strpos() takes the arguments in the other order.
`REPLACE()`	Replace every occurrence of a substring with another string. The search is case-sensitive.
`TRANSLATE()`	Replace single characters by position: each character in 'from' becomes the matching character in 'to', or is deleted.
`TRIM()`	Remove spaces, or characters you list, from the start, end or both ends of a string. btrim() is the function form.
`LTRIM()`	Remove spaces, or characters you list, from the start of a string only.
`RTRIM()`	Remove spaces, or any characters you list, from the end (right side) of a string only.
`LPAD()`	Pad a string on the left to a given length with spaces or another string. Longer strings are cut to that length.
`RPAD()`	Pad a string on the right to a given length with spaces or another string. Longer strings are cut to that length.
`REPEAT()`	Repeat a string a given number of times. A count of 0 or less gives an empty string.
`REVERSE()`	Return a string with its characters in reverse order. Since PostgreSQL 18 it also reverses bytea values.
`SPLIT_PART()`	Split a string on a delimiter and return the nth field (1-based). Negative n counts from the end.
`STRING_TO_ARRAY()`	Split a string on a delimiter into a text array. A third argument turns matching fields into NULL.
`STARTS_WITH()`	Return true if a string starts with a given prefix. The ^@ operator does the same and can use an index.
`MD5()`	Return the MD5 hash of a string as 32 hex characters. Fine for checksums, never for passwords.
`ILIKE`	Match a string against a LIKE pattern ignoring case: % matches any text and _ one character. ~~* is the operator form.
`REGEXP_REPLACE()`	Replace text matching a POSIX regular expression. Add the 'g' flag to replace every match, not just the first.
`REGEXP_MATCH()`	Return the captured groups of the first regular-expression match as a text array, or NULL if there's no match.
`REGEXP_SPLIT_TO_TABLE()`	Split a string on a regular expression and return one row per piece. regexp_split_to_array() returns an array.
`REGEXP_COUNT()`	Count how many times a regular expression matches in a string. Added in PostgreSQL 15.

All 31 string functions →

Math Functions

PostgreSQL math functions: abs(), round(), ceil(), floor(), trunc(), mod(), div(), power(), sqrt(), log(), random(min, max), gcd() and width_bucket().

Name	Description
`ABS()`	Return the absolute value of a number, so abs(-7) is 7. The result has the same type as the input.
`ROUND()`	Round a number to a given number of decimal places (0 by default). numeric rounds halves away from zero.
`CEIL()`	Round a number up to the nearest integer: ceil(2.1) is 3 and ceil(-2.1) is -2. ceiling() is a synonym.
`FLOOR()`	Round a number down to the nearest integer: floor(2.9) is 2 and floor(-2.1) is -3.
`TRUNC()`	Truncate a number toward zero, optionally keeping some decimal places: trunc(2.789, 2) is 2.78.
`MOD()`	Return the remainder of dividing one number by another; x % y does the same. The sign follows the dividend.
`DIV()`	Return the integer quotient of y / x, truncated toward zero, for numeric values. Integer / integer already truncates.
`POWER()`	Raise a number to a power, so power(2, 10) is 1024. pow() is another name for the same function.
`SQRT()`	Return the square root of a number, so sqrt(16) is 4. Negative input raises an error; cbrt() gives the cube root.
`LN()`	Return the natural logarithm (base e) of a number. exp() is the inverse.
`LOG()`	Return the base-10 logarithm of a number, or the logarithm in another base with log(b, x). log10() is a synonym.
`RANDOM()`	Return a random number from 0 up to 1, or since PostgreSQL 17 a random integer or numeric in a range.
`GCD()`	Return the greatest common divisor of two integers, the largest number that divides both. lcm() gives the least common multiple.
`WIDTH_BUCKET()`	Return which of n equal-width buckets a value falls into between a low and high bound, for building histograms.

Conditional and Type Expressions

Handle NULLs, conditions and types in PostgreSQL with COALESCE, NULLIF, GREATEST, LEAST, CASE, CAST, :: and pg_typeof().

Name	Description
`COALESCE()`	Return the first argument that isn't NULL. The standard way to replace NULL with a default.
`NULLIF()`	Return NULL if two values are equal, otherwise the first one. Commonly used to avoid division by zero.
`GREATEST()`	Return the largest of a list of values, ignoring NULLs. least() returns the smallest.
`CASE`	Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END, usable anywhere an expression is.
`CAST and ::`	Convert a value to another type with CAST(x AS type) or the PostgreSQL shorthand x::type. Invalid input raises an error.
`PG_TYPEOF()`	Return the data type of a value as a name, such as integer, numeric or text. Useful for debugging casts.

Date and Time Functions

PostgreSQL date and time functions: now(), date_trunc(), EXTRACT, age(), to_char(), to_date(), make_interval(), date_bin(), AT TIME ZONE and generate_series().

Name	Description
`NOW()`	Return the current date and time with time zone, fixed at the start of the transaction. current_timestamp is the same.
`CURRENT_DATE`	Return today's date (in the session's time zone) as a date with no time part. It's written without parentheses.
`DATE_TRUNC()`	Truncate a timestamp or interval to a unit such as 'month', 'week' or 'hour', for grouping by period.
`EXTRACT()`	Get one field from a date, time or interval, such as the year, month, day of week or epoch seconds.
`AGE()`	Subtract dates and return a 'symbolic' interval in years, months and days. With one argument it counts from today.
`TO_CHAR()`	Format a date, timestamp or number as text with a pattern such as 'DD Mon YYYY' or 'FM999,990.00'.
`TO_DATE()`	Parse text into a date using a format pattern, such as to_date('09/03/2026', 'DD/MM/YYYY').
`TO_TIMESTAMP()`	Convert Unix epoch seconds to a timestamp with time zone, or parse text into one with a format pattern.
`MAKE_DATE()`	Build a date from year, month and day numbers. make_timestamp() and make_time() do the same for other types.
`MAKE_INTERVAL()`	Build an interval from named parts, such as make_interval(days => 10, hours => 2).
`DATE_BIN()`	Round a timestamp down into fixed-size buckets, such as 15-minute slots, counted from an origin.
`AT TIME ZONE`	Convert between time zones: timestamptz AT TIME ZONE 'Asia/Tokyo' shows local time there.
`GENERATE_SERIES()`	Generate a set of rows from a start to a stop value with a step: numbers, or timestamps with an interval step.

Aggregate Functions

PostgreSQL aggregate functions: count(), sum(), avg(), min(), max(), string_agg(), array_agg(), bool_and(), percentile_cont(), mode() and the FILTER clause.

Name	Description
`COUNT()`	Count rows: count(*) counts every row, count(x) skips NULLs, and count(DISTINCT x) counts unique values. Returns bigint.
`SUM()`	Add up the non-NULL values in a group. Returns NULL, not 0, when there are no rows; wrap it in coalesce() if needed.
`AVG()`	Return the average of the non-NULL values in a group. Integer input gives a numeric result with full precision.
`MIN()`	Return the smallest value in a group. Works with numbers, text, dates and, since PostgreSQL 18, arrays.
`MAX()`	Return the largest value in a group. Works with numbers, text, dates and, since PostgreSQL 18, arrays.
`STRING_AGG()`	Join a group's non-NULL values into one string with a separator, optionally in a given ORDER BY order.
`ARRAY_AGG()`	Collect a group's values, including NULLs, into an array, optionally in a given order.
`BOOL_AND()`	Return true if every non-NULL value in a group is true. bool_or() returns true if at least one is.
`PERCENTILE_CONT()`	Return the value at a percentile, interpolating between rows: percentile_cont(0.5) is the median.
`MODE()`	Return the most frequent value in a group (the statistical mode). Ties go to the first value in sort order.
`ANY_VALUE()`	Return an arbitrary non-NULL value from a group. Useful for columns you know are the same within each group.
`FILTER`	Restrict which rows one aggregate sees with FILTER (WHERE …), so one query can count several conditions.

JSON Functions

PostgreSQL JSON functions and operators: ->, ->>, #>>, @>, jsonb_build_object(), jsonb_set(), jsonb_agg(), jsonb_path_query() and JSON_TABLE().

Name	Description
`-> and ->>`	Read JSON with -> (returns jsonb) or ->> (returns text) by object key or array index. Negative indexes count from the end.
`#> and #>>`	Read a nested JSON value by a path array: #> returns jsonb and #>> returns text, as in data #>> '{user,name}'.
`@> and ?`	Test JSON containment with @> (left contains right) and key existence with ?, both of which a GIN index can speed up.
`JSONB_BUILD_OBJECT()`	Build a JSON object from alternating keys and values. json_build_object() returns json instead of jsonb.
`JSONB_BUILD_ARRAY()`	Build a JSON array from the arguments, which may be of different types.
`TO_JSONB()`	Convert any SQL value, including a whole row, to jsonb. Rows become objects with column names as keys.
`JSONB_AGG()`	Aggregate a group's values or rows into a JSON array. json_agg() does the same but returns json.
`JSONB_OBJECT_AGG()`	Aggregate key/value pairs from a group into one JSON object, such as a name-to-price lookup.
`JSONB_SET()`	Return jsonb with the value at a path replaced or, by default, created. Combine with \|\| to merge objects.
`JSONB_INSERT()`	Insert a value into a JSON array at a path (before or after an element) or add a new object key.
`JSONB_STRIP_NULLS()`	Remove object fields whose value is JSON null, recursively. Since PostgreSQL 18 it can also drop nulls in arrays.
`JSONB_EACH()`	Expand a JSON object into one row per key, with key and value columns. jsonb_each_text() returns text values.
`JSONB_ARRAY_ELEMENTS()`	Expand a JSON array into one row per element. jsonb_array_elements_text() returns the elements as text.
`JSONB_ARRAY_LENGTH()`	Return the number of elements in a JSON array. Raises an error for objects and scalars.
`JSONB_TYPEOF()`	Return the type of the top-level JSON value: object, array, string, number, boolean or null.
`JSONB_PRETTY()`	Format a jsonb value as indented, multi-line text that is easy for people to read, such as in logs or reports.
`JSONB_PATH_QUERY()`	Return every JSON item matched by an SQL/JSON path expression, such as '$.items[*] ? (@.price > 10)'.
`JSON_TABLE()`	Turn JSON into a relational table in the FROM clause, with typed columns taken from SQL/JSON paths. Added in PostgreSQL 17.
`JSON_VALUE()`	Extract one scalar from JSON with an SQL/JSON path and return it as a chosen SQL type. Added in PostgreSQL 17.
`JSON_QUERY()`	Extract an object, array or other JSON fragment with an SQL/JSON path, optionally wrapping several items in an array.
`JSON_EXISTS()`	Return true if an SQL/JSON path finds anything in a JSON document. Added in PostgreSQL 17.

Array Functions

PostgreSQL array functions and operators: array_length(), cardinality(), unnest(), array_append(), array_position(), array_to_string(), ANY, array_sort().

Name	Description
`ARRAY_LENGTH()`	Return the length of one dimension of an array (use 1 for ordinary arrays). Empty arrays give NULL, not 0.
`CARDINALITY()`	Return the total number of elements in an array across all dimensions, and 0 for an empty array.
`UNNEST()`	Expand an array into a set of rows, one per element. With several arrays it zips them into columns.
`ARRAY_APPEND()`	Add an element to the end of an array. array_prepend() adds to the start, and \|\| joins arrays or elements.
`ARRAY_CAT()`	Concatenate two arrays of the same element type into one new array. The \|\| operator does the same thing.
`ARRAY_POSITION()`	Return the 1-based position of the first occurrence of a value in an array, or NULL if it isn't there.
`ARRAY_REMOVE()`	Remove every element equal to a given value from an array. array_replace() swaps them for another value.
`ARRAY_TO_STRING()`	Join array elements into a string with a separator, optionally writing NULLs as a given text.
`ANY and ALL`	Compare a value with every element of an array: x = ANY(array) is true if any element matches. ALL needs every element to.
`ARRAY_SORT()`	Return a copy of an array with its elements sorted, ascending by default. Added in PostgreSQL 18.
`ARRAY_REVERSE()`	Return a copy of an array with its elements in reverse order. Added in PostgreSQL 18.

UUID Functions

Generate and inspect UUIDs in PostgreSQL: gen_random_uuid() and uuidv4() for random IDs, uuidv7() for time-ordered IDs, and uuid_extract_timestamp().

Name	Description
`GEN_RANDOM_UUID()`	Generate a random version 4 UUID. Since PostgreSQL 18, uuidv4() is another name for it.
`UUIDV7()`	Generate a version 7 UUID, which starts with a timestamp so new IDs sort in creation order. Added in PostgreSQL 18.
`UUID_EXTRACT_TIMESTAMP()`	Return the creation time stored inside a version 1 or 7 UUID, or NULL for other versions. Added in PostgreSQL 17.

Window Functions

PostgreSQL window functions: row_number(), rank(), dense_rank(), ntile(), lag(), lead(), first_value(), last_value() and nth_value(), with OVER and frames.

Name	Description
`ROW_NUMBER()`	Number the rows of each window partition 1, 2, 3, … in ORDER BY order. Ties get different numbers.
`RANK()`	Rank rows by ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4).
`DENSE_RANK()`	Rank rows by ORDER BY, giving ties the same rank with no gaps after them (1, 2, 2, 3).
`PERCENT_RANK()`	Return a row's relative rank from 0 to 1: (rank - 1) / (rows in partition - 1).
`CUME_DIST()`	Return the share of rows ordered at or before the current row (including ties), from just above 0 to 1.
`NTILE()`	Split the rows of a window into N groups of nearly equal size and return each row's group number.
`LAG()`	Return a value from a previous row in the window, or a default when there isn't one. Ideal for comparing with the last period.
`LEAD()`	Return a value from a following row in the window, or a default when there isn't one.
`FIRST_VALUE()`	Return a value from the first row of the window frame, such as each department's highest salary.
`LAST_VALUE()`	Return a value from the last row of the window frame. Widen the frame or it returns the current row.
`NTH_VALUE()`	Return a value from the nth row of the window frame, or NULL if the frame has fewer rows.