Postgres functions

Get started with commonly used Postgres functions with Neon's function guides. For other functions that Postgres supports, visit the official Postgres Functions and Operators documentation. Aggregate ...

Get started with commonly-used Postgres functions with Neon's function guides. For other functions that Postgres supports, visit the official Postgres Functions and Operators documentation.

Aggregate functions

array_agg()

Aggregate elements into an array

avg()

Calculate the average of a set of values

count()

Count rows or non-null values in a result set

max()

Find the maximum value in a set of values

sum()

Calculate the sum of a set of values

Array functions

array_length()

Determine the length of an array

Date / Time functions

age()

Calculate the difference between timestamps or between a timestamp and the current date/time

current_timestamp

Get the current date and time

date_trunc()

Truncate date/time values to a specified precision

extract()

Extract date and time components from timestamps and intervals

now()

Get the current date and time

JSON functions

array_to_json()

Convert an SQL array to a JSON array

json()

Transform JSON data into relational views

json_agg()

Aggregate values into a JSON array

json_array_elements()

Expand a JSON array into a set of rows

jsonb_array_elements()

Expand a JSONB array into a set of rows

json_build_object()

Build a JSON object out of a variadic argument list

json_each()

Expand JSON into a record per key-value pair

json_exists()

Check for Values in JSON Data Using SQL/JSON Path Expressions

json_extract_path()

Extract a JSON sub-object at the specified path

json_extract_path_text()

Extract a JSON sub-object at the specified path as text

json_object()

Create a JSON object from key-value pairs

json_populate_record()

Cast a JSON object to a record

json_query()

Extract and Transform JSON Values with SQL/JSON Path Expressions

json_scalar()

Convert Text and Binary Data to JSON Values

json_serialize()

Convert JSON Values to Text or Binary Format

json_table()

Transform JSON data into relational views

json_to_record()

Convert a JSON object to a record

json_value()

Extract and Convert JSON Scalar Values

jsonb_each()

Expand JSONB into a record per key-value pair

jsonb_extract_path()

Extract a JSONB sub-object at the specified path

jsonb_extract_path_text()

Extract a JSONB sub-object at the specified path as text

jsonb_object()

Create a JSONB object from key-value pairs

jsonb_populate_record()

Cast a JSONB object to a record

jsonb_to_record()

Convert a JSONB object to a record

Mathematical functions

abs()

Calculate the absolute value of a number

random()

Generate a random number between 0 and 1

round()

Round numbers to a specified precision

String functions

concat()

Concatenate strings

lower()

Convert a string to lowercase

substring()

Extract a substring from a string

regexp_match()

Extract substrings matching a regular expression pattern

regexp_replace()

Replace substrings matching a regular expression pattern

trim()

Remove leading and trailing characters from a string

Window functions

dense_rank()

Return the rank of the current row without gaps

lag()

Access values from previous rows in a result set

lead()

Access values from subsequent rows in a result set

rank()

Assign ranks to rows within a result set