Skip to main content

PostgreSQL

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.


1. Install PostgreSQL and 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.


2. Create a New 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.


3. Create a Table
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.


4. Insert Data
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;

5. SELECT and WHERE

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.


6. AND and OR

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.


7. IN, BETWEEN, LIKE and ILIKE

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%';

8. ORDER BY and LIMIT

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

9. UPDATE and DELETE

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;

10. Constraints

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'
);

11. Auto-Generated IDs

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.


12. Aggregate Functions

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;

13. GROUP BY

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.


14. HAVING

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

15. JOINs

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);

16. INNER JOIN

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

17. LEFT JOIN

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.


18. Pagination

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

19. Date and Time

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

20. Subqueries

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')
);

21. CTE - Common Table Expression

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;

22. Backup and Restore

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.


23. CASE

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.


24. Indexes

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.


25. Transactions

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.


26. Views

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.


27. Functions

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.


28. Stored Procedures

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);

29. Triggers

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.


30. JSONB

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.


31. EXPLAIN ANALYZE and Query Optimization

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

Popular posts from this blog

Prompt Engineering for Web Developers: Work Smarter with AI 🚀

   AI is changing the way web applications are built. From generating components and writing tests to planning projects and reviewing code, developers can now use natural language prompts to accelerate development. This approach, often called Vibe Coding, allows developers to describe what they want and let AI generate the solution. The secret to getting quality results is Prompt Engineering : the practice of writing better prompts. 📌 The RTCF Framework A simple framework for creating effective prompts is RTCF : Role → Who should AI act as? Task → What needs to be done? Context → Why is it needed? Format → How should the output look? ❌ Weak Prompt Make me a learning app. ✅ Strong Prompt Role: Senior React Developer Task: Build a React Learning Planner Context: Users need to track learning resources through stages Format: React, Tailwind CSS, drag-and-drop, modern UI, no TypeScript Providing clear instructions dramatically improves AI o...

💰 Managing Finances

Most people focus on earning more, but real wealth is built through smart investing, risk management, and disciplined financial habits. Here's a simple framework to help secure your financial future. 🎯 1. Set Financial Goals First Invest with a purpose: Emergency Fund Home Purchase Child Education Retirement Financial Independence ⚠️ 2. Control Lifestyle Inflation ✅ Good Debt Home Loan Business Loan Education Loan ❌ Bad Debt Credit Card Loan Personal Loan Consumer EMIs Rule: Keep total EMIs below 40% of monthly income . 🛡️ 3. Create an Emergency Fund Maintain 6 months of household expenses in: Fixed Deposits (FD) or Sweep-In Account Arbitrage Mutual Fund (tax-efficient for higher tax brackets) Post Office Deposits This should be your first financial priority. ❤️ 4. Protect Before You Invest Term Insurance Coverage: 10-15× Annual Income/CTC Health Insurance Keep a personal family floater policy. Do not rely only on employer...

Prompt Engineering with OpenAI ChatGPT

Prompt Engineering: A Simple Guide to Better Results Many people use AI with vague prompts and hope for the best. Something like: "Write a postmortem update about yesterday's outage." often produces inconsistent results. Instead of playing Prompt Roulette , use a structured approach. A Better Workflow 1. Contract – Define what success looks like. Ask: What must be included? 2. Scaffold – Use a reusable template. For example: Summary Impact Timeline Known Facts 3. Levers – Change only one thing at a time: Add more detail Shorten the response Change the tone Adjust the audience 4. Loop – Review and improve: Generate → Review → Find one issue → Improve the prompt → Run again. Think of a Prompt as a Contract A strong prompt usually contains five parts: Instructions – What should AI do? Context – Facts and background information. Constraints – Rules and limitations. Examples – Optional style or format sa...