PostgreSQL - Useful Functions

PostgreSQL - Useful Functions and Operators

PostgreSQL provides a rich set of built-in functions and operators to perform operations on data. These range from simple aggregate calculations to complex string manipulations, array processing, and even querying JSON data.

Core Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single summary row. They are commonly used with the GROUP BY clause.

• PostgreSQL COUNT() − Counts the number of rows matching a specified condition. COUNT(*) counts all rows, while COUNT(column_name) counts rows where the column is not NULL.
• PostgreSQL MAX() − Returns the maximum value from a set of values in a column.
• PostgreSQL MIN() − Returns the minimum value from a set of values in a column.
• PostgreSQL AVG() − Calculates the average (mean) of a set of numeric values.
• PostgreSQL SUM() − Calculates the sum of a set of numeric values.

Advanced Aggregation and Array Functions

Modern applications often require more complex data aggregation.

• PostgreSQL STRING_AGG() − Concatenates input strings into a single string, with an optional separator. Very useful for creating comma-separated lists from grouped data.
• PostgreSQL ARRAY_AGG() − Aggregates input values, including nulls, into a PostgreSQL array. This is powerful for collecting related items into a single field.
• PostgreSQL JSONB_AGG() / JSON_AGG() − Aggregates values as a JSON array. JSONB is the preferred, more efficient binary format.

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows; they return a value for each row based on a ‘window’ of neighboring rows defined by the OVER() clause.

• Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK() are used to assign a unique rank to each row based on a specific ordering.
• Aggregate as Window Functions: You can use standard aggregates like SUM(), AVG(), and COUNT() as window functions to calculate running totals, moving averages, etc.

Example: Ranking Employees by Salary

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as department_salary_rank
FROM
    employees;

Common String and Numeric Functions

PostgreSQL has a comprehensive library of functions for data manipulation.

• String Functions: LOWER(), UPPER(), LENGTH(), SUBSTRING(), TRIM(), CONCAT().
• Numeric Functions: ROUND(), CEIL(), FLOOR(), ABS(), RANDOM().
• Date/Time Functions: NOW(), CURRENT_DATE, EXTRACT(), AGE().

Further Learning

This is just a small sample. The official PostgreSQL documentation on Functions and Operators is the definitive resource for exploring all available capabilities.