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