PostgreSQL Functions
Understanding built-in functions in PostgreSQL
🔧 What are PostgreSQL Functions?
PostgreSQL functions are built-in operations that perform specific tasks on your data. They help you manipulate strings, calculate numbers, format dates, and aggregate results efficiently without writing complex code.
-- Simple function example
SELECT UPPER('hello world');
-- Returns: HELLO WORLD
SELECT LENGTH('PostgreSQL');
-- Returns: 10
Output:
UPPER: HELLO WORLD
LENGTH: 10
Types of PostgreSQL Functions
String Functions
Manipulate text and characters
SELECT CONCAT('Hello', ' ', 'World');Date Functions
Work with dates and times
SELECT CURRENT_DATE;Math Functions
Perform mathematical operations
SELECT ROUND(3.14159, 2);Aggregate Functions
Calculate summary statistics
SELECT AVG(salary) FROM employees;🔹 Basic Function Syntax
PostgreSQL functions follow a simple pattern. You call the function name followed by parentheses containing arguments. Functions can be used in SELECT statements, WHERE clauses, and other SQL operations to transform or analyze your data.
-- Basic syntax
SELECT function_name(argument1, argument2);
-- Example with table
SELECT
name,
UPPER(name) AS uppercase_name,
LENGTH(name) AS name_length
FROM users;
Example Output:
| name | uppercase_name | name_length |
|---|---|---|
| John | JOHN | 4 |
| Alice | ALICE | 5 |
🔹 Common Function Categories
PostgreSQL organizes functions into categories based on their purpose. Understanding these categories helps you quickly find the right function for your task and write more efficient queries.
🔸 Text Processing
-- Convert case
SELECT UPPER('hello'), LOWER('WORLD');
-- Trim whitespace
SELECT TRIM(' spaces ');
-- Substring
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 8);
🔸 Numeric Operations
-- Rounding
SELECT ROUND(15.7), CEIL(15.2), FLOOR(15.9);
-- Absolute value
SELECT ABS(-42);
-- Power and square root
SELECT POWER(2, 3), SQRT(16);
🔸 Date and Time
-- Current date and time
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;
-- Date arithmetic
SELECT CURRENT_DATE + INTERVAL '7 days';
-- Extract parts
SELECT EXTRACT(YEAR FROM NOW());
🔹 Combining Functions
Functions can be nested and combined to perform complex operations. This allows you to transform data in multiple steps within a single query, making your SQL more powerful and expressive.
-- Nested functions
SELECT UPPER(TRIM(' hello world '));
-- Returns: HELLO WORLD
-- Multiple functions in one query
SELECT
name,
UPPER(LEFT(name, 1)) || LOWER(SUBSTRING(name FROM 2)) AS formatted_name,
LENGTH(TRIM(name)) AS clean_length
FROM users;
Output:
Nested: HELLO WORLD
🔹 Function Best Practices
Following best practices ensures your queries are efficient, readable, and maintainable. Use functions wisely to balance performance with code clarity.
- Use appropriate functions: Choose the right function for your data type
- Avoid overuse in WHERE: Functions on columns can slow queries
- Test with sample data: Verify function behavior before production
- Combine wisely: Too many nested functions reduce readability
- Check NULL handling: Most functions return NULL if input is NULL