A Beginner’s Guide to PostgreSQL’s Built-In Functions and Operators

Table of Contents

1. What Are Functions and Operators?
2. Core Operators in PostgreSQL
   • Arithmetic Operators
   • Comparison Operators
   • Logical Operators
   • String Operators
   • Date/Time Operators
3. Essential Built-In Functions
   • String Functions
   • Numeric Functions
   • Date/Time Functions
   • Aggregate Functions
   • Array & JSON Functions
4. Advanced Functions
   • Window Functions
   • Conditional Functions
5. Practical Examples: Putting It All Together
6. Best Practices for Using Functions and Operators
7. Conclusion
8. References

What Are Functions and Operators?

Before diving in, let’s clarify the basics:

• Operators are symbols or keywords that perform operations on one or more values (operands). For example, + (addition) or = (equality check).
• Functions are reusable blocks of code that take input (arguments) and return a value. They are called by name, e.g., SUM(amount) or UPPER(name).

PostgreSQL provides hundreds of operators and functions tailored to different data types (numbers, strings, dates, JSON, arrays, etc.). They are optimized for performance, so using them often leads to faster queries than writing custom logic.

Core Operators in PostgreSQL

Operators are the building blocks of SQL queries. Let’s explore the most common types:

Arithmetic Operators

Used to perform mathematical calculations on numeric values (integers, decimals, etc.).

Example with a table: Suppose we have a sales table with a column amount (sale value). To calculate the total after a 10% tax:

SELECT amount, amount * 1.1 AS total_with_tax  
FROM sales;  

Comparison Operators

Used to compare values and return TRUE, FALSE, or NULL (if one operand is NULL).

Example: Find sales of laptops or phones with amounts between $100 and $500:

SELECT *  
FROM sales  
WHERE product IN ('Laptop', 'Phone')  
  AND amount BETWEEN 100 AND 500;  

Logical Operators

Combine multiple conditions in WHERE clauses.

Example: Find sales where the product is a laptop and the amount is over $500, or the product is a phone with a rating ≥ 4.5:

SELECT *  
FROM sales  
WHERE (product = 'Laptop' AND amount > 500)  
   OR (product = 'Phone' AND rating >= 4.5);  

String Operators

Used to manipulate text data.

Example: Combine first and last names into a full name:

SELECT first_name || ' ' || last_name AS full_name  
FROM customers;  

Date/Time Operators

PostgreSQL has special operators for dates, times, and intervals (durations).

Example: Find sales from the last 30 days:

SELECT *  
FROM sales  
WHERE sale_date >= NOW() - INTERVAL '30 days';  

Essential Built-In Functions

Functions extend PostgreSQL’s capabilities beyond basic operators. Let’s explore the most useful ones for beginners.

String Functions

Clean, format, or extract information from text data.

Example: Clean and standardize product names (trim spaces, uppercase):

SELECT TRIM(UPPER(product)) AS cleaned_product  
FROM sales;  

Numeric Functions

Perform calculations on numbers (integers, decimals, etc.).

Example: Round sale amounts to 2 decimal places for currency:

SELECT amount, ROUND(amount, 2) AS rounded_amount  
FROM sales;  

Date/Time Functions

Work with dates, times, and timestamps.

Example: Format sale dates as “Month Day, Year”:

SELECT TO_CHAR(sale_date, 'FMMonth DD, YYYY') AS formatted_date  
FROM sales;  

Aggregate Functions

Calculate a single result from a set of rows (e.g., sum, average). Often used with GROUP BY.

Example: Calculate total, average, min, and max sales per product:

SELECT product,  
       COUNT(*) AS total_sales,  
       SUM(amount) AS total_revenue,  
       AVG(amount) AS avg_sale,  
       MIN(amount) AS min_sale,  
       MAX(amount) AS max_sale  
FROM sales  
GROUP BY product;  

Array & JSON Functions

PostgreSQL natively supports arrays and JSON, with functions to manipulate them.

Array Functions

JSON Functions

Advanced Functions

Once you’re comfortable with essentials, explore these advanced but powerful functions.

Window Functions

Window functions perform calculations across a set of table rows related to the current row (without grouping them into a single result). Common use cases: ranking, row numbering, running totals.

Example: Rank products by total sales (descending):

SELECT product,  
       SUM(amount) AS total_revenue,  
       RANK() OVER (ORDER BY SUM(amount) DESC) AS revenue_rank  
FROM sales  
GROUP BY product;  

Conditional Functions

Handle logic based on conditions (e.g., NULL values, custom rules).

Example: Categorize sales and handle missing emails:

SELECT customer_name,  
       COALESCE(customer_email, '[email protected]') AS email,  
       CASE  
           WHEN amount > 1000 THEN 'Premium'  
           WHEN amount > 500 THEN 'Standard'  
           ELSE 'Basic'  
       END AS sale_tier  
FROM sales;  

Practical Examples: Putting It All Together

Let’s apply what we’ve learned with a realistic scenario. Suppose we have a sales table with:

Example 1: Clean and Analyze Customer Data

-- Clean customer names (trim, uppercase) and handle missing emails  
SELECT TRIM(UPPER(customer_name)) AS cleaned_name,  
       COALESCE(customer_email, '[email protected]') AS email,  
       COUNT(*) AS total_purchases,  
       SUM(amount) AS total_spent  
FROM sales  
GROUP BY cleaned_name, email  
ORDER BY total_spent DESC  
LIMIT 10; -- Top 10 customers  

Example 2: Monthly Sales Report

-- Aggregate sales by month/year, format dates, calculate metrics  
SELECT TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,  
       COUNT(*) AS total_sales,  
       SUM(amount) AS total_revenue,  
       AVG(amount) AS avg_sale,  
       MAX(amount) AS largest_sale,  
       -- Categorize monthly revenue  
       CASE  
           WHEN SUM(amount) > 10000 THEN 'Excellent'  
           WHEN SUM(amount) > 5000 THEN 'Good'  
           ELSE 'Low'  
       END AS revenue_tier  
FROM sales  
GROUP BY sale_month  
ORDER BY sale_month;  

Example 3: Rank Products by Monthly Sales

-- Use window functions to rank products within each month  
WITH monthly_sales AS (  
    SELECT TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,  
           product,  
           SUM(amount) AS revenue  
    FROM sales  
    GROUP BY sale_month, product  
)  
SELECT sale_month,  
       product,  
       revenue,  
       RANK() OVER (PARTITION BY sale_month ORDER BY revenue DESC) AS monthly_rank  
FROM monthly_sales  
ORDER BY sale_month, monthly_rank;  

Best Practices for Using Functions and Operators

To get the most out of PostgreSQL’s built-in tools:

1. Prioritize Built-In Functions: They are optimized for performance (e.g., SUM() is faster than looping through rows in a custom script).
2. Mind Data Types: Ensure functions match the data type of your columns (e.g., TO_CHAR() for dates, not numbers).
3. Handle NULLs Carefully: Use COALESCE(), NULLIF(), or IS NULL to avoid unexpected results (e.g., NULL + 5 returns NULL).
4. Test Edge Cases: For example, AVG() ignores NULLs, but COUNT(*) includes all rows (even with NULLs).
5. Use GROUP BY with Aggregates: Always include non-aggregated columns in GROUP BY to avoid errors (e.g., SELECT product, SUM(amount) requires GROUP BY product).

Conclusion

PostgreSQL’s built-in functions and operators are indispensable tools for anyone working with data. From simple arithmetic to complex window functions, they streamline query writing, improve performance, and unlock powerful analysis capabilities.

As a beginner, focus on mastering the essentials first—arithmetic/comparison operators, string/numeric functions, and aggregates. Once comfortable, explore advanced topics like window functions and JSON manipulation. The key is practice: experiment with your own data, write queries, and refer to the official documentation for deeper dives.

Happy querying!

References

• PostgreSQL Official Documentation: Functions and Operators
• PostgreSQL Aggregate Functions
• PostgreSQL Window Functions
• PostgreSQL String Functions Cheat Sheet
• PostgreSQL Date/Time Functions