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 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_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

• jsonb_each()

  Expand JSONB into a record per key-value pair

• json_extract_path()

  Extract a JSON sub-object at the specified path

• jsonb_extract_path()

  Extract a JSONB sub-object at the specified path

• json_extract_path_text()

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

• jsonb_extract_path_text()

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

• json_object()

  Create a JSON object from key-value pairs

• jsonb_object()

  Create a JSONB object from key-value pairs

• json_populate_record()

  Cast a JSON object to a record

• jsonb_populate_record()

  Cast a JSONB object to a record

• json_to_record()

  Convert a JSON object to a record

• jsonb_to_record()

  Convert a JSONB object to a record

• JSON_TABLE()

  Transform JSON data into relational views

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