PostgreSQL is a powerful relational database management system. If you have worked with MySQL, learning PostgreSQL becomes easier because many common SQL commands work in a similar way.
These notes cover PostgreSQL setup, basic SQL, joins, pagination, subqueries, CTEs and important advanced concepts with simple examples that you can practice in pgAdmin.
Install PostgreSQL Server and pgAdmin. During installation, create a password and remember it because pgAdmin will ask for it when connecting to the PostgreSQL server.
After opening pgAdmin, go to:
Servers → PostgreSQL 18 → Databases → postgres
postgres is the default database.
Right-click Databases → Create → Database → Enter: practice_db → Save
Select practice_db, then open:
Tools → Query Tool
Run your first query:
SELECT 'Hello PostgreSQL';
Press F5 to execute it.
CREATE TABLE employees (
id INT,
name VARCHAR(50),
age INT
);
The new table appears under:
Schemas → public → Tables → employees
Fetch all records:
SELECT * FROM employees;
The table is empty, so only its column names will appear.
INSERT INTO employees(id, name, age)
VALUES
(1, 'Mohit', 30),
(2, 'Akshay', 29),
(3, 'Pankaj', 28),
(4, 'Neha', 25),
(5, 'Rahul', 20);
Check the records:
SELECT * FROM employees;
SELECT reads data. WHERE filters records using a condition.
SELECT name FROM employees; SELECT * FROM employees WHERE name = 'Mohit'; SELECT * FROM employees WHERE name != 'Rahul'; SELECT * FROM employees WHERE id = 3; SELECT * FROM employees WHERE age < 29;
SQL keywords are not case-sensitive, but text comparisons can be case-sensitive.
AND means every condition must be true.
SELECT * FROM employees WHERE age >= 26 AND age <= 31;
OR means at least one condition must be true.
SELECT * FROM employees WHERE name = 'Mohit' OR name = 'Akshay';
The following normally returns no records:
SELECT * FROM employees WHERE name = 'Mohit' AND name = 'Akshay';
This happens because the same name value cannot normally be both Mohit and Akshay.
IN is a shorter way to check several possible values.
SELECT *
FROM employees
WHERE name IN ('Pankaj', 'Rahul');
BETWEEN checks an inclusive range.
SELECT * FROM employees WHERE age BETWEEN 25 AND 29;
LIKE performs pattern matching.
'R%' → Starts with R '%a' → Ends with a '%an%' → Contains "an"
SELECT * FROM employees WHERE name LIKE 'A%';
ILIKE performs case-insensitive pattern matching.
SELECT * FROM employees WHERE name ILIKE '%OH%';
ORDER BY sorts results. Ascending order is the default.
SELECT * FROM employees ORDER BY age DESC;
Sort names from Z to A:
SELECT * FROM employees ORDER BY name DESC;
LIMIT controls the number of returned rows.
Find the youngest employee:
SELECT * FROM employees ORDER BY age LIMIT 1;
Among employees older than 26, sort youngest first:
SELECT * FROM employees WHERE age > 26 ORDER BY age;
Clause order:
SELECT → FROM → WHERE → ORDER BY → LIMIT
UPDATE changes existing data.
UPDATE employees
SET name = 'Neha Sharma',
age = 26
WHERE id = 4;
DELETE removes data.
DELETE FROM employees WHERE id = 5;
Important: Before UPDATE or DELETE, run SELECT with the same WHERE condition.
SELECT * FROM employees WHERE id = 5;
Constraints define rules for data.
PRIMARY KEY → Unique identifier and cannot be NULL NOT NULL → A value is required UNIQUE → Duplicate values are not allowed CHECK → Value must satisfy a condition DEFAULT → Used when no value is supplied
Example:
CREATE TABLE staff (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT CHECK(age >= 18),
city VARCHAR(50) DEFAULT 'Mumbai'
);
PostgreSQL can automatically generate IDs for us.
CREATE TABLE users (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
Now we don't need to provide an ID:
INSERT INTO users(name)
VALUES
('Mohit'),
('Rahul'),
('Neha');
PostgreSQL generates the IDs automatically. Gaps between generated IDs are normal.
Create our employee salary table:
CREATE TABLE emp (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary INT
);
Insert records:
INSERT INTO emp (name, department, salary)
VALUES
('Mohit', 'IT', 60000),
('Rahul', 'IT', 80000),
('Neha', 'HR', 50000),
('Priya', 'HR', 55000),
('Aman', 'Finance', 70000);
Common aggregate functions:
SELECT COUNT(*) FROM emp; SELECT SUM(salary) FROM emp; SELECT AVG(salary) FROM emp; SELECT MIN(salary) FROM emp; SELECT MAX(salary) FROM emp;
Name the result using AS:
SELECT MAX(salary) AS highest_salary FROM emp;
GROUP BY gives us one result per group.
SELECT department,
COUNT(*) AS total
FROM emp
GROUP BY department;
Average salary of each department:
SELECT department,
AVG(salary) AS average_salary
FROM emp
GROUP BY department;
Total salary by department, highest first:
SELECT department,
SUM(salary) AS total_salary
FROM emp
GROUP BY department
ORDER BY total_salary DESC;
A column in SELECT should normally either be inside an aggregate function or appear in GROUP BY.
Easy way to remember:
WHERE → Filters rows HAVING → Filters groups
Example:
SELECT department,
SUM(salary) AS total_salary
FROM emp
GROUP BY department
HAVING SUM(salary) > 100000;
Find departments having more than one employee:
SELECT department FROM emp GROUP BY department HAVING COUNT(*) > 1;
Clause order:
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
Real application data is commonly stored in different tables connected using IDs.
CREATE TABLE departments (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE workers (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT REFERENCES departments(id)
);
Add some data:
INSERT INTO departments(name)
VALUES ('IT'), ('HR'), ('Finance');
INSERT INTO workers(name, dept_id)
VALUES
('Mohit', 1),
('Rahul', 1),
('Neha', 2),
('Aman', NULL);
INNER JOIN returns only rows that have matches in both tables.
SELECT workers.name,
departments.name AS department
FROM workers
INNER JOIN departments
ON workers.dept_id = departments.id;
Aman is missing because his dept_id is NULL.
Finance is missing because currently no worker belongs to it.
Show only workers from IT:
SELECT workers.name,
departments.name AS department
FROM workers
INNER JOIN departments
ON workers.dept_id = departments.id
WHERE departments.name = 'IT';
Clause order with JOIN:
SELECT → FROM → JOIN ... ON → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
LEFT JOIN returns all rows from the left table plus matching rows from the right table.
SELECT workers.name,
departments.name AS department
FROM workers
LEFT JOIN departments
ON workers.dept_id = departments.id;
Aman will also appear. His department will be NULL.
Pagination is commonly used when the frontend displays records page by page.
OFFSET = (page_number - 1) × page_size
If page size is 10:
Page 1:
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 0;
Page 2:
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 10;
Page 3:
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;
For example, if the UI sends page 3 and pageSize 10:
OFFSET = (3 - 1) × 10 OFFSET = 20
Get the current timestamp:
SELECT NOW();
Get the timestamp from 30 days ago:
SELECT NOW() - INTERVAL '30 days';
Extract the current month:
SELECT EXTRACT(MONTH FROM NOW());
Format the date:
SELECT TO_CHAR(NOW(), 'DD-MM-YYYY');
Timestamps are commonly used in fields such as:
created_at updated_at ordered_at
A subquery is simply a query inside another query.
Find employees whose salary is greater than average:
SELECT *
FROM emp
WHERE salary > (
SELECT AVG(salary)
FROM emp
);
Scalar Subquery
Returns one value and is often used with =, >, <, >= and <=.
List Subquery
Returns multiple values and is commonly used with IN.
SELECT *
FROM workers
WHERE dept_id IN (
SELECT id
FROM departments
WHERE name IN ('IT', 'HR')
);
A CTE is a named temporary result that exists for the current SQL statement.
It is created using WITH:
WITH high_salary AS (
SELECT *
FROM emp
WHERE salary > 60000
)
SELECT *
FROM high_salary;
CTEs are useful when a query contains several steps.
WITH high_salary AS (
SELECT *
FROM emp
WHERE salary > 50000
),
very_high_salary AS (
SELECT *
FROM high_salary
WHERE salary > 75000
)
SELECT *
FROM very_high_salary;
Backup:
Right-click Database → Backup → Select format → Choose file → Backup
Restore:
Create target database → Right-click target database → Restore → Select backup → Restore
Common formats:
Plain → Readable SQL script Custom → PostgreSQL archive format
Advanced PostgreSQL Concepts
The following examples are intentionally simple. First understand what each concept does, then explore more advanced uses later.
CASE works like if/else logic inside SQL.
Let's divide employees into salary categories.
SELECT name,
salary,
CASE
WHEN salary >= 70000 THEN 'High'
WHEN salary >= 55000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM emp;
Easy definition:
CASE allows us to apply conditions and return different values.
An index can help PostgreSQL find data faster, especially when a table becomes large.
Suppose we frequently search employees by name:
SELECT * FROM employees WHERE name = 'Mohit';
Create an index:
CREATE INDEX idx_employees_name ON employees(name);
You can think of it like the index at the back of a book. Instead of searching every page, you first find where the required topic is located.
Indexes require additional storage and can add work to INSERT, UPDATE and DELETE operations, so don't create an index for every column blindly.
Easy definition:
An index is an additional data structure that can help PostgreSQL find records faster.
A transaction groups multiple operations together.
A common example is transferring money between two accounts.
Create a simple accounts table:
CREATE TABLE accounts (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL,
balance NUMERIC(12, 2) NOT NULL
);
Start the transaction:
BEGIN;
Transfer the money:
UPDATE accounts SET balance = balance - 1000 WHERE name = 'Mohit'; UPDATE accounts SET balance = balance + 1000 WHERE name = 'Rahul';
If everything is correct:
COMMIT;
If something goes wrong before COMMIT:
ROLLBACK;
Easy definition:
A transaction lets related database operations be treated as one unit of work.
A view can be thought of as a saved query.
Suppose we frequently need IT employees:
SELECT name, salary FROM emp WHERE department = 'IT';
Create a view:
CREATE VIEW it_employees AS SELECT name, salary FROM emp WHERE department = 'IT';
Now use:
SELECT * FROM it_employees;
Easy definition:
A view gives a reusable name to a query.
A PostgreSQL function contains reusable database logic and can return a result.
Simple addition function:
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE SQL
AS $$
SELECT a + b;
$$;
Call it:
SELECT add_numbers(10, 20);
Result:
30
Easy definition:
A function accepts input, performs reusable logic and returns a result.
A stored procedure contains reusable database operations.
For beginners, an easy difference to remember is that procedures are executed using CALL.
CREATE PROCEDURE add_employee(
emp_name VARCHAR,
emp_department VARCHAR,
emp_salary INT
)
LANGUAGE SQL
AS $$
INSERT INTO emp(name, department, salary)
VALUES(emp_name, emp_department, emp_salary);
$$;
Call the procedure:
CALL add_employee('Ravi', 'IT', 65000);
Check the table:
SELECT * FROM emp;
Basic difference:
Function:
SELECT add_numbers(10, 20);
Procedure:
CALL add_employee('Ravi', 'IT', 65000);
A trigger automatically executes database logic when an event such as INSERT, UPDATE or DELETE happens.
A simple example is automatically updating updated_at whenever a product changes.
CREATE TABLE products (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50),
updated_at TIMESTAMP DEFAULT NOW()
);
Create the trigger function:
CREATE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$;
Create the trigger:
CREATE TRIGGER products_updated BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION update_timestamp();
Insert a product:
INSERT INTO products(name)
VALUES ('Laptop');
Update it:
UPDATE products SET name = 'Gaming Laptop' WHERE id = 1;
PostgreSQL automatically changes updated_at.
Easy definition:
A trigger automatically runs database logic when a specified event occurs.
JSONB allows PostgreSQL to store JSON-style data.
For example, a user might have preferences such as:
{
"theme": "dark",
"language": "English"
}
Create the table:
CREATE TABLE user_profiles (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50),
preferences JSONB
);
Insert JSON data:
INSERT INTO user_profiles(name, preferences)
VALUES (
'Mohit',
'{"theme":"dark", "language":"English"}'
);
Fetch the complete record:
SELECT * FROM user_profiles;
Get only the theme:
SELECT preferences ->> 'theme' FROM user_profiles;
Result:
dark
Easy definition:
JSONB allows us to store and query JSON-style data inside PostgreSQL.
When tables become larger, some queries may become slow. EXPLAIN ANALYZE helps us understand how PostgreSQL executes a query.
Normal query:
SELECT * FROM emp WHERE salary > 60000;
Analyze it:
EXPLAIN ANALYZE SELECT * FROM emp WHERE salary > 60000;
You may see operations such as:
Seq Scan Index Scan Sort Hash Join Nested Loop
At the beginning, there is no need to memorize every part of an execution plan.
First understand this process:
Query ↓ EXPLAIN ANALYZE ↓ Execution Plan ↓ Find expensive operations ↓ Improve query or indexes
Important: EXPLAIN ANALYZE actually runs the statement, so be careful when using it with INSERT, UPDATE, DELETE or other statements that change data.
Quick PostgreSQL Revision
SELECT → Read data INSERT → Add data UPDATE → Change data DELETE → Remove data WHERE → Filter rows AND → All conditions must be true OR → At least one condition must be true IN → Match one of several values BETWEEN → Match a range LIKE → Pattern matching ILIKE → Case-insensitive pattern matching ORDER BY → Sort rows LIMIT → Limit returned rows GROUP BY → Group rows HAVING → Filter groups JOIN → Combine related tables Subquery → Query inside another query CTE → Named temporary query result CASE → Conditional logic INDEX → Helps find data faster TRANSACTION → Groups related operations VIEW → Saved query FUNCTION → Reusable logic returning a result PROCEDURE → Reusable operation called using CALL TRIGGER → Automatically executes logic JSONB → Stores JSON-style data EXPLAIN ANALYZE → Executes and analyzes a query
Final Notes
These are my PostgreSQL learning notes covering the journey from basic SQL operations to some important advanced PostgreSQL concepts.
The easiest way to learn PostgreSQL is to practice instead of only reading the syntax. Create small tables in pgAdmin, insert some test data, write queries yourself, make mistakes, understand the errors and then gradually move to advanced concepts.
Once you are comfortable with SELECT, WHERE, GROUP BY, JOINs, subqueries and CTEs, concepts such as indexes, transactions, functions, procedures, triggers and JSONB become much easier to understand.
Happy Learning!
Comments
Post a Comment