Functions

Master PostgreSQL's powerful built-in functions for data manipulation, analysis, and transformation.

Overview

PostgreSQL provides a rich set of built-in functions that enable sophisticated data operations without leaving the database. This reference covers essential functions organized by category.

Function Categories

Aggregate Functions

Perform calculations across multiple rows to return a single result.

• count(): Count rows or non-null values

• sum(): Calculate total of numeric values

• avg(): Compute average of numeric values

• max(): Find maximum value

• min(): Find minimum value

• array_agg(): Aggregate values into an array

JSON Functions

Work with JSON and JSONB data types for document-oriented operations.

JSON Construction

• json_build_object(): Create JSON objects from key-value pairs

• json_object(): Construct JSON objects

• json_agg(): Aggregate rows into JSON array

JSON Querying

• json_extract_path(): Extract nested JSON values

• json_extract_path_text(): Extract nested JSON as text

• json_exists(): Check if JSON path exists

• json_query(): Query JSON with SQL/JSON path

• json_value(): Extract scalar JSON value

• json_scalar(): Work with JSON scalar values

JSON Manipulation

• json_array_elements(): Expand JSON array to rows

• json_each(): Expand JSON object to key-value pairs

• json_populate_record(): Populate record from JSON

• json_to_record(): Convert JSON to record

• json_table(): Extract JSON data as table

• json_serialize(): Serialize JSON data

JSONB Functions

High-performance binary JSON functions (jsonb_* variants):

• jsonb_array_elements(): Expand JSONB array

• jsonb_each(): Expand JSONB object

• jsonb_extract_path(): Extract from JSONB path

• jsonb_extract_path_text(): Extract JSONB as text

• jsonb_object(): Create JSONB object

• jsonb_populate_record(): Populate from JSONB

• jsonb_to_record(): Convert JSONB to record

String Functions

Manipulate and transform text data.

• concat(): Concatenate strings

• lower(): Convert to lowercase

• upper(): Convert to uppercase

• trim(): Remove leading/trailing spaces

• substring(): Extract portion of string

• regexp_match(): Match regular expression

• regexp_replace(): Replace using regex

Date & Time Functions

Work with temporal data and perform date calculations.

• now(): Current date and time

• current_timestamp: Current timestamp

• date_trunc(): Truncate to specified precision

• extract(): Extract date/time component

• age(): Calculate interval between dates

Array Functions

Operations on PostgreSQL array types.

• array_agg(): Aggregate values into array

• array_length(): Get array length

• array_to_json(): Convert array to JSON

• unnest(): Expand array to rows

Mathematical Functions

Perform mathematical calculations and operations.

• abs(): Absolute value

• round(): Round to nearest integer or decimal places

• random(): Generate random number

• ceil(): Round up

• floor(): Round down

Window Functions

Perform calculations across sets of rows related to the current row.

• rank(): Rank with gaps

• dense_rank(): Rank without gaps

• row_number(): Sequential row numbering

• lag(): Access previous row value

• lead(): Access next row value

• first_value(): First value in window

• last_value(): Last value in window

Type Conversion

Convert between different data types.

• CAST(): Standard type conversion

• ::: PostgreSQL type cast operator

• to_char(): Convert to character string

• to_date(): Convert to date

• to_timestamp(): Convert to timestamp

Using Functions

In SELECT Statements

SELECT
    count(*) as total_users,
    avg(age) as average_age,
    json_agg(email) as all_emails
FROM users;

In WHERE Clauses

SELECT * FROM orders
WHERE date_trunc('month', created_at) = date_trunc('month', now());

In GROUP BY

SELECT
    date_trunc('day', created_at) as day,
    count(*) as daily_count
FROM events
GROUP BY day;

Nested Functions

SELECT
    lower(trim(email)) as normalized_email
FROM users;

Best Practices

1. Use appropriate function: Choose the right function for your data type

2. Consider performance: Some functions can't use indexes

3. Handle NULL values: Many functions return NULL when input is NULL

4. Prefer JSONB over JSON: JSONB offers better performance for most operations

5. Use window functions: More efficient than self-joins for ranking/analytics

Getting Started

1. Start with the Functions Introduction

2. Explore JSON functions for document operations

3. Master window functions for advanced analytics

4. Learn date functions for time-based queries

Common Patterns

• Data aggregation: Use aggregate functions with GROUP BY

• JSON APIs: Extract and transform JSON data

• Time-series analysis: Combine date functions with window functions

• Text processing: Chain string functions for data cleaning

• Ranking and scoring: Apply window functions for ordered results

Dive into the function documentation to write more powerful and efficient SQL queries.