PostgreSQL is one of the most popular open-source relational database management systems (RDBMS). It is widely used because of its reliability, performance, and support for advanced SQL features.
What is PostgreSQL?
PostgreSQL is an open-source database system used to store, organize, and retrieve structured data. It follows the Relational Database Management System (RDBMS) model, where data is stored in tables made up of rows and columns.
Many companies use PostgreSQL for web applications, business reporting, financial systems, and data analytics because it can efficiently handle both small and large datasets.
Creating a Database and Table
The first step is creating a database and a table.
Create Database
CREATE DATABASE company_db;
Create Table
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary NUMERIC,
city VARCHAR(50)
);
The SERIAL keyword automatically generates unique IDs for each employee record.
Basic SQL Queries
After creating a table, data can be inserted and retrieved using SQL queries.
Insert Data
INSERT INTO employees(employee_name, department, salary, city)
VALUES
('John','Sales',45000,'Chennai'),
('Priya','HR',50000,'Bangalore'),
('Rahul','IT',65000,'Hyderabad');
View All Records
SELECT * FROM employees;
The SELECT statement retrieves data from the table.
Filter Records
SELECT * FROM employees
WHERE department = 'IT';
The WHERE clause filters records based on a condition.
Common PostgreSQL Functions
PostgreSQL provides many built-in functions that help analyze data.
COUNT()
Returns the total number of rows.
SELECT COUNT() FROM employees;
**SUM()*
Calculates the total salary.
SELECT SUM(salary) FROM employees;
AVG()
Returns the average salary.
SELECT AVG(salary) FROM employees;
MAX()
Finds the highest salary.
SELECT MAX(salary) FROM employees;
MIN()
Finds the lowest salary.
SELECT MIN(salary) FROM employees;
These aggregate functions are frequently used in business reports and dashboards.
Sorting Data
The ORDER BY clause arranges records in ascending or descending order.
SELECT * FROM employees
ORDER BY salary DESC;
This query displays employees from the highest salary to the lowest.
Grouping Data
The GROUP BY clause groups similar records together.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
This query calculates the average salary for each department.
Why PostgreSQL is Important for Data Analysts
PostgreSQL is one of the most preferred databases in Data Analytics because it allows analysts to work efficiently with structured data.
Some advantages include:
Fast query execution
Open-source and free to use
Supports large datasets
Advanced SQL features
Easy integration with Power BI, Tableau, and Python
Strong security and reliability
These features make PostgreSQL a valuable tool for analyzing business data and generating reports.
My Git Lab Link:
https://gitlab.com/seenivasan.a2833/postgressql
Conclusion
PostgreSQL is an excellent database system for beginners and professionals. Learning how to create databases, write SQL queries, use aggregate functions, and filter data provides a strong foundation for Data Analysis. As your skills grow, you can explore advanced topics such as joins, window functions, indexes, stored procedures, and database optimization.
For aspiring Data Analysts, mastering PostgreSQL is an important step toward building a successful career in data analytics and business intelligence.
Top comments (0)