Mastering Essential PostgreSQL Functions with Practical Examples
PostgreSQL, a powerful open-source relational database, offers a rich set of built-in functions that make data manipulation and analysis easy and efficient. Whether you are a developer, data analyst, or database administrator, understanding these functions is crucial for writing clean and performant SQL queries.
Aggregate Functions-These functions perform calculations on a set of rows and return a single value.
COUNT(), SUM(), AVG(), MAX(), MIN()
--Count Total Users-
SELECT COUNT(*) FROM Users;
--Calculate Total Sales-
SELECT SUM(amount) from Sales;
--Get Average orders values-
SELECT AVG(amount) from Orders;
--Get Maximum Salary
SELECT MAX(Salary) from Employee;
--Get Minimum Age
SELECT MIN(Age) from Employee;
String Functions-These help in string manipulation and analysis.
LOWER(), UPPER(), CONCAT(), SUBSTRING(), LENGTH()
--Convert name into lower case-
SELECT LOWER(name) FROM Employee;
--Concatenate first and last name-
SELECT CONCAT (first_name,' ', last_name) AS full_name FROM Users;
--Get first 3 characters of product code
SELECT SUBSTRING(product_code FROM 1 FOR 3) FROM products;
--Measure Length of string
SELECT LENGTH(detail) FROM items;
Date/Time Functions-Essential for working with timestamps and dates.
NOW(), CURRENT_DATE, AGE(), EXTRACT()
--Get current Timestamp
SELECT NOW();
--Get Current Date
SELECT CURRENT_DATE;
--Calculate age from birth date
SELECT AGE(birth_date) FROM Users;
--Extract years from timestamp
SELECT EXTRACT (YEAR FROM order_date) FROM orders;
Conditional Expressions-Handle logic-based queries using CASE.
CASE WHEN THEN ELSE END
--Categories Salary Level
SELECT name,
CASE
WHEN Salary>100000 Then 'High'
WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'
ELSE 'Low'
END AS 'Salary_band'
FROM Employees;
Window Functions-Perform calculations across a set of rows related to the current row.
ROW_NUMBER(), RANK(), PARTITION BY
--Rank employees by salary within each department
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
Mathematical Functions-Ideal for numerical computations.
ROUND(), CEIL(), FLOOR(), RANDOM()
--Round average score to 2 decimals
SELECT ROUND (AVG(Score),2) FROM exams;
--Generate a random number between 0 and 1
SELECT RANDOM();
Type Conversion Functions- Convert data types as needed.
CAST(), ::
--Convert string into integer
SELECT '123' :: INTEGER;
--Cast using function
SELECT CAST ('2025-01-01' AS Date);
Views-A view is a virtual table based on the result of a SQL query. It simplifies complex queries and enhances security by restricting access to specific data.
--Create a View for active customer
CREATE VIEW active_customer AS
SELECT id, name, email
from customer
WHERE status='active';
Use the View
SELECT * FROM active_customer;
--Modify or Drop View
DROP VIEW IF EXIST active customer;
Stored Procedures-Stored procedures encapsulate SQL logic and can perform transactions. They are called using CALL.
--Create a Stored Procedure
CREATE PROCEDURE update_salary (emp_id INT, salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE employee
SET salary=new_salary
where id= emp_id;
END;
$$;
CALL update_salary(101, 75000);
Practice basic queries on sites like SQLZoo, LeetCode, or Mode
