PostgreSQL Functions

Introduction

PostgreSQL provides a rich set of built-in functions for data manipulation, calculation, and transformation. Functions can be used in SELECT statements, WHERE clauses, and other SQL contexts.

Function Categories

1. Aggregate Functions - Operate on sets of rows
2. String Functions - Text manipulation
3. Numeric Functions - Mathematical operations
4. Date/Time Functions - Date and time operations
5. Conditional Functions - Conditional logic
6. Window Functions - Calculations across row sets
7. JSON Functions - JSON data manipulation
8. Array Functions - Array operations
9. Type Conversion Functions - Data type conversions

Aggregate Functions

Aggregate functions compute a single result from a set of input values.

Common Aggregate Functions

Function	Description	Example
COUNT()	Count rows	COUNT(*)
SUM()	Sum values	SUM(amount)
AVG()	Average value	AVG(price)
MIN()	Minimum value	MIN(age)
MAX()	Maximum value	MAX(salary)

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product VARCHAR(100),
    quantity INTEGER,
    price NUMERIC(10,2),
    sale_date DATE
);

INSERT INTO sales (product, quantity, price, sale_date) VALUES
    ('Laptop', 5, 999.99, '2024-01-15'),
    ('Mouse', 20, 29.99, '2024-01-16'),
    ('Keyboard', 15, 79.99, '2024-01-17'),
    ('Monitor', 8, 299.99, '2024-01-18');

-- Aggregate examples
SELECT 
    COUNT(*) AS total_sales,
    SUM(quantity) AS total_quantity,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    SUM(quantity * price) AS total_revenue
FROM sales;

Advanced Aggregate Functions

-- STRING_AGG: Concatenate strings
SELECT STRING_AGG(product, ', ') AS products FROM sales;

-- ARRAY_AGG: Aggregate into array
SELECT ARRAY_AGG(product) AS product_array FROM sales;

-- COUNT DISTINCT: Count unique values
SELECT COUNT(DISTINCT product) AS unique_products FROM sales;

-- Aggregate with GROUP BY
SELECT 
    DATE_TRUNC('month', sale_date) AS month,
    COUNT(*) AS sales_count,
    SUM(quantity * price) AS monthly_revenue
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

String Functions

String functions manipulate text data.

Common String Functions

Function	Description	Example
LENGTH()	String length	LENGTH('hello') → 5
UPPER()	Convert to uppercase	UPPER('hello') → 'HELLO'
LOWER()	Convert to lowercase	LOWER('HELLO') → 'hello'
TRIM()	Remove whitespace	TRIM(' text ') → 'text'
SUBSTRING()	Extract substring	SUBSTRING('hello', 1, 3) → 'hel'
CONCAT()	Concatenate strings	CONCAT('a', 'b') → 'ab'
REPLACE()	Replace substring	REPLACE('hello', 'l', 'r') → 'herro'

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(255)
);

INSERT INTO users (first_name, last_name, email) VALUES
    ('John', 'Doe', 'john.doe@example.com'),
    ('Jane', 'Smith', 'JANE.SMITH@EXAMPLE.COM'),
    ('  Bob  ', 'Johnson', 'bob@example.com');

-- String function examples
SELECT 
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(first_name) AS upper_name,
    LOWER(email) AS lower_email,
    LENGTH(first_name) AS name_length,
    TRIM(first_name) AS trimmed_name,
    SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username,
    REPLACE(email, '@example.com', '@newdomain.com') AS new_email
FROM users;

Advanced String Functions

-- LEFT and RIGHT: Extract from start/end
SELECT 
    LEFT('PostgreSQL', 4) AS left_part,    -- 'Post'
    RIGHT('PostgreSQL', 3) AS right_part;  -- 'SQL'

-- LPAD and RPAD: Pad strings
SELECT 
    LPAD('42', 5, '0') AS padded_left,     -- '00042'
    RPAD('42', 5, '0') AS padded_right;    -- '42000'

-- SPLIT_PART: Split string
SELECT SPLIT_PART('one,two,three', ',', 2) AS second_part;  -- 'two'

-- REGEXP_REPLACE: Regular expression replace
SELECT REGEXP_REPLACE('Hello 123 World', '\d+', 'XXX') AS replaced;  -- 'Hello XXX World'

-- INITCAP: Capitalize first letter of each word
SELECT INITCAP('hello world') AS capitalized;  -- 'Hello World'

Numeric Functions

Numeric functions perform mathematical operations.

Common Numeric Functions

Function	Description	Example
ABS()	Absolute value	ABS(-5) → 5
ROUND()	Round number	ROUND(3.7) → 4
CEIL()	Round up	CEIL(3.2) → 4
FLOOR()	Round down	FLOOR(3.8) → 3
POWER()	Exponentiation	POWER(2, 3) → 8
SQRT()	Square root	SQRT(16) → 4
MOD()	Modulo	MOD(10, 3) → 1

CREATE TABLE measurements (
    id SERIAL PRIMARY KEY,
    value NUMERIC(10,4)
);

INSERT INTO measurements (value) VALUES 
    (3.14159), (-5.678), (10.5), (99.999);

-- Numeric function examples
SELECT 
    value,
    ABS(value) AS absolute,
    ROUND(value, 2) AS rounded,
    CEIL(value) AS ceiling,
    FLOOR(value) AS floor,
    POWER(value, 2) AS squared,
    SQRT(ABS(value)) AS square_root,
    MOD(value::INTEGER, 3) AS modulo
FROM measurements;

-- Mathematical operations
SELECT 
    PI() AS pi_value,
    POWER(2, 10) AS two_to_ten,
    SQRT(144) AS sqrt_144,
    EXP(1) AS e_value,
    LN(10) AS natural_log,
    LOG(100) AS log_base_10;

Date/Time Functions

Date and time functions manipulate temporal data.

Common Date/Time Functions

Function	Description	Example
NOW()	Current timestamp	NOW()
CURRENT_DATE	Current date	CURRENT_DATE
CURRENT_TIME	Current time	CURRENT_TIME
AGE()	Calculate age	AGE(timestamp)
EXTRACT()	Extract part	EXTRACT(YEAR FROM date)
DATE_TRUNC()	Truncate to unit	DATE_TRUNC('month', date)

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO events (event_name, event_date) VALUES
    ('Conference', '2024-06-15 09:00:00'),
    ('Workshop', '2024-07-20 14:30:00'),
    ('Webinar', '2024-08-10 10:00:00');

-- Date/Time function examples
SELECT 
    event_name,
    event_date,
    NOW() AS current_time,
    CURRENT_DATE AS today,
    AGE(event_date, NOW()) AS time_until_event,
    EXTRACT(YEAR FROM event_date) AS year,
    EXTRACT(MONTH FROM event_date) AS month,
    EXTRACT(DAY FROM event_date) AS day,
    DATE_TRUNC('month', event_date) AS month_start,
    TO_CHAR(event_date, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM events;

-- Date arithmetic
SELECT 
    CURRENT_DATE AS today,
    CURRENT_DATE + INTERVAL '7 days' AS next_week,
    CURRENT_DATE - INTERVAL '1 month' AS last_month,
    CURRENT_DATE + INTERVAL '1 year' AS next_year;

Advanced Date/Time Functions

-- DATE_PART: Extract specific part
SELECT 
    DATE_PART('year', NOW()) AS year,
    DATE_PART('quarter', NOW()) AS quarter,
    DATE_PART('week', NOW()) AS week,
    DATE_PART('dow', NOW()) AS day_of_week;  -- 0=Sunday

-- MAKE_DATE and MAKE_TIMESTAMP
SELECT 
    MAKE_DATE(2024, 6, 15) AS created_date,
    MAKE_TIMESTAMP(2024, 6, 15, 10, 30, 0) AS created_timestamp;

-- JUSTIFY_DAYS and JUSTIFY_HOURS: Normalize intervals
SELECT 
    JUSTIFY_DAYS(INTERVAL '35 days') AS normalized_days,    -- '1 mon 5 days'
    JUSTIFY_HOURS(INTERVAL '27 hours') AS normalized_hours; -- '1 day 03:00:00'

Conditional Functions

Conditional functions provide if-then-else logic.

CASE Expression

SELECT 
    product,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_category
FROM sales;

COALESCE Function

Returns the first non-NULL value.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    discount_price NUMERIC(10,2),
    regular_price NUMERIC(10,2)
);

INSERT INTO products (name, discount_price, regular_price) VALUES
    ('Product A', NULL, 99.99),
    ('Product B', 79.99, 99.99),
    ('Product C', NULL, 149.99);

-- COALESCE example
SELECT 
    name,
    COALESCE(discount_price, regular_price) AS final_price,
    COALESCE(discount_price, regular_price, 0) AS price_with_default
FROM products;

NULLIF Function

Returns NULL if two values are equal.

SELECT 
    NULLIF(10, 10) AS result1,  -- NULL
    NULLIF(10, 5) AS result2;   -- 10

-- Avoid division by zero
SELECT 
    quantity,
    total_amount / NULLIF(quantity, 0) AS price_per_unit
FROM sales;

GREATEST and LEAST

SELECT 
    GREATEST(10, 20, 5, 30) AS max_value,  -- 30
    LEAST(10, 20, 5, 30) AS min_value;     -- 5

Window Functions

Window functions perform calculations across a set of rows related to the current row.

Common Window Functions

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10,2)
);

INSERT INTO employees (name, department, salary) VALUES
    ('Alice', 'Sales', 60000),
    ('Bob', 'Sales', 55000),
    ('Charlie', 'IT', 70000),
    ('David', 'IT', 75000),
    ('Eve', 'HR', 50000);

-- ROW_NUMBER: Assign unique row numbers
SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- RANK and DENSE_RANK
SELECT 
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- PARTITION BY: Window per group
SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

Advanced Window Functions

-- LAG and LEAD: Access previous/next rows
SELECT 
    name,
    salary,
    LAG(salary) OVER (ORDER BY salary) AS previous_salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary,
    salary - LAG(salary) OVER (ORDER BY salary) AS salary_diff
FROM employees;

-- FIRST_VALUE and LAST_VALUE
SELECT 
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS lowest_in_dept,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept
FROM employees;

-- NTILE: Divide into buckets
SELECT 
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

JSON Functions

PostgreSQL provides extensive JSON manipulation functions.

JSON Query Functions

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
    ('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB", "price": 999}'),
    ('Phone', '{"brand": "Apple", "model": "iPhone 14", "storage": "256GB", "price": 899}'),
    ('Tablet', '{"brand": "Samsung", "screen": "10.5 inch", "storage": "128GB", "price": 499}');

-- Extract JSON values
SELECT 
    name,
    attributes->>'brand' AS brand,
    attributes->>'storage' AS storage,
    (attributes->>'price')::NUMERIC AS price
FROM products;

-- JSON path queries
SELECT 
    name,
    attributes->'brand' AS brand_json,      -- Returns JSON
    attributes->>'brand' AS brand_text      -- Returns text
FROM products;

-- Check if key exists
SELECT name FROM products WHERE attributes ? 'model';

-- Contains operator
SELECT name FROM products WHERE attributes @> '{"brand": "Apple"}';

JSON Manipulation Functions

-- JSONB_SET: Update JSON value
UPDATE products 
SET attributes = JSONB_SET(attributes, '{price}', '899')
WHERE name = 'Laptop';

-- JSONB_INSERT: Insert new key
UPDATE products 
SET attributes = JSONB_INSERT(attributes, '{warranty}', '"2 years"')
WHERE name = 'Laptop';

-- Concatenate JSON
UPDATE products 
SET attributes = attributes || '{"color": "black"}'
WHERE name = 'Phone';

-- Remove key
UPDATE products 
SET attributes = attributes - 'model'
WHERE name = 'Phone';

Array Functions

PostgreSQL provides functions for working with arrays.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],
    prices NUMERIC[]
);

INSERT INTO products (name, tags, prices) VALUES
    ('Laptop', ARRAY['electronics', 'computers', 'portable'], ARRAY[999, 1299, 1599]),
    ('Book', ARRAY['fiction', 'bestseller'], ARRAY[9.99, 12.99]);

-- Array functions
SELECT 
    name,
    tags,
    array_length(tags, 1) AS tag_count,
    tags[1] AS first_tag,
    array_append(tags, 'new') AS with_new_tag,
    array_prepend('featured', tags) AS with_featured,
    array_cat(tags, ARRAY['sale']) AS concatenated,
    'electronics' = ANY(tags) AS has_electronics
FROM products;

-- UNNEST: Convert array to rows
SELECT 
    name,
    UNNEST(tags) AS tag
FROM products;

-- Array aggregation
SELECT 
    ARRAY_AGG(name) AS all_products,
    ARRAY_AGG(name ORDER BY name) AS sorted_products
FROM products;

Type Conversion Functions

Convert between different data types.

-- CAST and :: operator
SELECT 
    CAST('123' AS INTEGER) AS int_value,
    '123'::INTEGER AS int_value2,
    CAST('2024-01-01' AS DATE) AS date_value,
    '2024-01-01'::DATE AS date_value2;

-- TO_CHAR: Format output
SELECT 
    TO_CHAR(NOW(), 'YYYY-MM-DD') AS formatted_date,
    TO_CHAR(NOW(), 'HH24:MI:SS') AS formatted_time,
    TO_CHAR(1234.56, '9,999.99') AS formatted_number;

-- TO_NUMBER: Parse number
SELECT 
    TO_NUMBER('1,234.56', '9,999.99') AS parsed_number;

-- TO_DATE and TO_TIMESTAMP
SELECT 
    TO_DATE('2024-01-15', 'YYYY-MM-DD') AS parsed_date,
    TO_TIMESTAMP('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') AS parsed_timestamp;

System Information Functions

Get information about the database system.

-- Database information
SELECT 
    VERSION() AS postgres_version,
    CURRENT_DATABASE() AS current_db,
    CURRENT_SCHEMA() AS current_schema,
    CURRENT_USER AS current_user,
    SESSION_USER AS session_user;

-- Connection information
SELECT 
    INET_CLIENT_ADDR() AS client_ip,
    INET_CLIENT_PORT() AS client_port,
    INET_SERVER_ADDR() AS server_ip,
    INET_SERVER_PORT() AS server_port;

-- Database size
SELECT 
    PG_DATABASE_SIZE(CURRENT_DATABASE()) AS db_size_bytes,
    PG_SIZE_PRETTY(PG_DATABASE_SIZE(CURRENT_DATABASE())) AS db_size_pretty;

User-Defined Functions

Create custom functions in PostgreSQL.

Basic Function

-- Create a simple function
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Use the function
SELECT add_numbers(5, 3);  -- Returns 8

Function with Default Parameters

CREATE OR REPLACE FUNCTION calculate_discount(
    price NUMERIC,
    discount_percent NUMERIC DEFAULT 10
)
RETURNS NUMERIC AS $$
BEGIN
    RETURN price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

-- Use with and without default
SELECT calculate_discount(100);      -- Returns 90
SELECT calculate_discount(100, 20);  -- Returns 80

Function Returning Table

CREATE OR REPLACE FUNCTION get_high_salary_employees(min_salary NUMERIC)
RETURNS TABLE(
    employee_name VARCHAR,
    employee_salary NUMERIC,
    department_name VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT name, salary, department
    FROM employees
    WHERE salary >= min_salary
    ORDER BY salary DESC;
END;
$$ LANGUAGE plpgsql;

-- Use the function
SELECT * FROM get_high_salary_employees(60000);

Function with Control Flow

CREATE OR REPLACE FUNCTION get_grade(score INTEGER)
RETURNS VARCHAR AS $$
BEGIN
    IF score >= 90 THEN
        RETURN 'A';
    ELSIF score >= 80 THEN
        RETURN 'B';
    ELSIF score >= 70 THEN
        RETURN 'C';
    ELSIF score >= 60 THEN
        RETURN 'D';
    ELSE
        RETURN 'F';
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT get_grade(85);  -- Returns 'B'

Best Practices

1. Use appropriate functions

   • Choose the right function for your data type
   • Use built-in functions instead of custom logic when possible

2. Performance considerations

   • Avoid using functions in WHERE clauses on large tables (prevents index usage)
   • Use window functions instead of subqueries when possible

3. NULL handling

   • Always consider NULL values in your functions
   • Use COALESCE or NULLIF when appropriate

4. Type safety

   • Use explicit type casting to avoid errors
   • Validate input parameters in user-defined functions

5. Documentation

   • Comment complex functions
   • Use descriptive function and parameter names

Common Function Patterns

Pattern 1: Data Cleaning

-- Clean and standardize email addresses
SELECT 
    LOWER(TRIM(email)) AS clean_email,
    REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS clean_phone
FROM users;

Pattern 2: Data Aggregation

-- Calculate running totals
SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Pattern 3: Data Transformation

-- Convert JSON to columns
SELECT 
    id,
    attributes->>'brand' AS brand,
    (attributes->>'price')::NUMERIC AS price,
    ARRAY_LENGTH(STRING_TO_ARRAY(attributes->>'tags', ','), 1) AS tag_count
FROM products;

Summary

PostgreSQL functions provide powerful data manipulation capabilities:

• Aggregate functions: COUNT, SUM, AVG, MIN, MAX
• String functions: CONCAT, UPPER, LOWER, SUBSTRING, TRIM
• Numeric functions: ROUND, CEIL, FLOOR, ABS, POWER
• Date/Time functions: NOW, EXTRACT, DATE_TRUNC, AGE
• Conditional functions: CASE, COALESCE, NULLIF
• Window functions: ROW_NUMBER, RANK, LAG, LEAD
• JSON functions: ->, ->>, @>, JSONB_SET
• Array functions: ARRAY_AGG, UNNEST, array_length
• User-defined functions: Custom logic with PL/pgSQL

Understanding and effectively using these functions is essential for efficient data processing and analysis in PostgreSQL.