PostgreSQL Notes

What is PostgreSQL? Basic

A database stores data permanently and lets you query it quickly. PostgreSQL (often called "Postgres") is a relational database. Data lives in tables made of rows and columns, and tables are linked through keys.

Think of it like thisA database is an Excel workbook. Each table is a sheet, each column is a header with a fixed data type, and each row is one record. Unlike Excel, the rules (types, uniqueness, links between sheets) are enforced.

Anatomy of a table

users
id	name	email	city
1	Asha	asha@mail.com	Pune
2	Rahul	rahul@mail.com	Delhi
3	Meera	meera@mail.com	Pune

Primary key = unique ID for each row · highlighted = one row (record) · each header = a column (field)

Why Postgres?

Reliable (ACID)

Your data is safe even if the server crashes mid-write.

Feature-rich

JSONB, arrays, full-text search, window functions, extensions such as PostGIS and pgvector.

Free & open source

Used by Instagram, Reddit and Apple, and hosted by Neon, Supabase, RDS and others.

Kinds of SQL commands

Group	Purpose	Commands
DDL Definition	Define structure	`CREATE` `ALTER` `DROP` `TRUNCATE`
DML Manipulation	Change data	`INSERT` `UPDATE` `DELETE`
DQL Query	Read data	`SELECT`
DCL Control	Permissions	`GRANT` `REVOKE`
TCL Transaction	Group changes	`BEGIN` `COMMIT` `ROLLBACK`

Install & the psql shell Basic

Windows / macOS

Download the installer from postgresql.org. It includes pgAdmin, a GUI tool.

Docker (recommended)

One command, and it's easy to reset.

Cloud

Neon or Supabase give you a free hosted Postgres with a connection URL.

psql meta-commands

\llist databases
\c dbnameconnect
\dtlist tables
\d usersdescribe table
\dulist roles
\xexpanded output
\i file.sqlrun a file
\qquit

GUI tools

pgAdminofficial GUI
DBeaverfree, multi-DB
TablePlusfast & clean
VS CodePostgreSQL extension

Syntax rulesEnd every SQL statement with ;. Keywords are case-insensitive, but people usually write them in UPPERCASE. Use 'single quotes' for text and "double quotes" for identifiers. Comments start with --.

Data types Basic

Category	Type	Use for	Example
Numbers	`INT` / `BIGINT`	Whole numbers	42
	`SERIAL` / `GENERATED … AS IDENTITY`	Auto-increment IDs	1, 2, 3…
	`NUMERIC(10,2)`	Money and exact decimals	1999.99
	`REAL` / `DOUBLE PRECISION`	Scientific values (approximate)	3.14159
Text	`VARCHAR(n)`	Text with a max length	'Asha'
Text	`TEXT`	Any length	blog body
Boolean	`BOOLEAN`	true/false	TRUE
Date/time	`DATE`	Calendar date	'2026-10-06'
	`TIMESTAMPTZ`	Date + time with time zone (preferred)	NOW()
	`INTERVAL`	A duration	'7 days'
IDs	`UUID`	Globally unique IDs	gen_random_uuid()
Structured	`JSONB`	Flexible JSON documents	'{"color":"red"}'
Structured	`TEXT[]`	Arrays	'{a,b,c}'

MoneyNever store money in REAL/FLOAT, because 0.1 + 0.2 ≠ 0.3. Use NUMERIC(12,2), or an INT holding paise/cents.

Creating tables & constraints Basic

Constraints are rules the database enforces, so bad data can't get in, whichever app writes it.

PRIMARY KEY

Unique + not null. Identifies each row.

FOREIGN KEY

Must match a row in another table (REFERENCES).

NOT NULL

A value is required.

UNIQUE

No duplicates allowed in this column.

CHECK

A custom condition, e.g. price >= 0.

DEFAULT

The value used when none is given.

ON DELETE options

Option	When the parent user is deleted…
`CASCADE`	Their posts are deleted too
`SET NULL`	`posts.user_id` becomes NULL
`RESTRICT` / default	The delete is blocked while posts exist

Changing a table

CRUD: Insert, Select, Update, Delete Basic

CRUD ↔ SQL ↔ HTTP

Create

INSERT
POST

Read

SELECT
GET

Update

UPDATE
PATCH/PUT

Delete

DELETE
DELETE

Always use WHERE with UPDATE / DELETEDELETE FROM users; deletes every row. To be safe, run the matching SELECT … WHERE first, or wrap the change in BEGIN; … ROLLBACK; to test it.

Upsert (insert or update)

Filtering, sorting & limits Basic

Logical order a SELECT is executed in

FROM / JOIN

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

LIMIT

That's why you can't use a SELECT alias inside WHERE: WHERE runs before SELECT.

Operator	Meaning
`= <> != < > <= >=`	Comparison
`AND OR NOT`	Combine conditions (use brackets!)
`LIKE 'a%'` / `ILIKE`	Pattern (case-sensitive / insensitive)
`IN (…)`, `BETWEEN a AND b`	List, range
`IS NULL`, `COALESCE(x, 'default')`	Handle missing values

Which query finds users with no phone number?

Aggregates & GROUP BY Intermediate

Aggregate functions turn many rows into one value: COUNT, SUM, AVG, MIN and MAX. GROUP BY makes one group per distinct value, and the aggregate runs once per group.

GROUP BY city → COUNT(*)

name	city
Asha	Pune
Rahul	Delhi
Meera	Pune
Vikram	Pune
Neha	Delhi

GROUP BY city

city	count
Pune	3
Delhi	2

WHERE

Filters individual rows
Runs before GROUP BY
Can't use aggregates

VS

HAVING

Filters groups
Runs after GROUP BY
Uses aggregates: HAVING SUM(x) > 100

Relationships & keys Intermediate

ER diagram — a small shop

One-to-One

user ↔ profile. FK + UNIQUE on the child.

One-to-Many

user → orders. The FK goes on the "many" side (orders.user_id).

Many-to-Many

orders ↔ products. Needs a junction table with two FKs.

JOINs Intermediate

A JOIN combines rows from two tables using a matching column, usually FK = PK. A = users (left), B = orders (right).

Which rows come back?

INNER JOINonly matches in both

LEFT JOINall of A + matches (else NULL)

RIGHT JOINall of B + matches

FULL JOINeverything from both

Example: users LEFT JOIN orders

users
id	name
1	Asha
2	Rahul
3	Meera

+

orders
id	user_id	total
10	1	500
11	1	300
12	2	900

LEFT JOIN

result
name	order	total
Asha	10	500
Asha	11	300
Rahul	12	900
Meera	NULL	NULL

Meera has no orders. A LEFT JOIN keeps her with NULLs; an INNER JOIN would drop her.

You need ALL products, including ones never ordered. Which join from products to order_items?

Subqueries & CTEs Intermediate

A subquery is a query inside another query. A CTE (WITH) gives a subquery a name, so complex SQL reads top-to-bottom like steps.

Normalization (table design) Intermediate

Normalization means storing each fact in one place, so updates can't leave copies that disagree.

Splitting a bad table

❌ orders (repeated data)
id	customer	cust_email	products
1	Asha	asha@m.com	Mouse, Pad
2	Asha	asha@m.com	Keyboard

normalize

✓ users
id	name	email
1	Asha	asha@m.com

✓ orders
id	user_id
1	1
2	1

Form	Rule	Fix
1NF	One value per cell, no repeating groups	"Mouse, Pad" → separate rows in `order_items`
2NF	Every column depends on the whole key	Product name moves to `products`, not `order_items`
3NF	No column depends on another non-key column	customer_email moves to `users`

Denormalize on purposeSometimes you copy data for speed or history. For example, order_items.unit_price stores the price at purchase time. That's a deliberate choice.

Indexes Advanced

Book indexTo find "JOIN" in a 900-page book, you check the index at the back and go straight to page 412. You don't read every page. A database index works the same way for a column.

B-tree index on users.email

A lookup takes a few hops (O(log n)) instead of scanning millions of rows.

Index when…

Column is used in WHERE / JOIN / ORDER BY
Foreign keys
High selectivity (many distinct values)

Avoid when…

Tiny tables
Write-heavy tables (each index slows INSERT/UPDATE)
Low selectivity (e.g. a boolean)

EXPLAIN ANALYZE & tuning Advanced

Before index

After CREATE INDEX

Plan node	Meaning
`Seq Scan`	Reads the whole table. Fine for small tables, a red flag for big ones.
`Index Scan` / `Index Only Scan`	Uses an index (best)
`Bitmap Heap Scan`	An index finds many rows, which are then fetched in batches
`Nested Loop` / `Hash Join` / `Merge Join`	Join strategies

Index killersWHERE LOWER(email) = … doesn't use a plain index on email; create an expression index instead. A leading wildcard like LIKE '%abc' can't use a B-tree. Run ANALYZE after big imports so the planner has fresh statistics.

Transactions & ACID Advanced

Atomic

All or nothing

Consistent

Constraints always hold

Isolated

Concurrent transactions don't interfere

Durable

Committed data survives a crash

Isolation level	Prevents	Note
`READ COMMITTED`	Dirty reads	Postgres default
`REPEATABLE READ`	+ non-repeatable reads	A consistent snapshot for the whole transaction
`SERIALIZABLE`	+ serialization anomalies	Safest. Be ready to retry on error 40001.

MVCCPostgres uses Multi-Version Concurrency Control: readers never block writers, and writers never block readers. Each transaction sees a snapshot. VACUUM (usually run by autovacuum) cleans up old row versions.

Window functions Advanced

Like GROUP BY, but rows are not collapsed. Each row keeps its detail and also gets a value calculated over a "window" of related rows.

RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

name	dept	salary	rank
Asha	Eng	150k	1
Rahul	Eng	120k	2
Dev	Eng	120k	2
Meera	Sales	90k	1
Neha	Sales	70k	2

Ranking restarts in each partition (dept). Every row is still there.

Views, functions & triggers Advanced

View

A saved query you can use like a table. It always shows live data.

Materialized view

Stores the result. Very fast to read, but you must REFRESH it.

Trigger

Runs a function automatically on INSERT, UPDATE or DELETE.

JSONB, arrays & full-text search Advanced

JSONB stores JSON in a binary format that can be indexed. Use it for flexible attributes (product specs, settings). Keep core fields in normal columns.

Roles, security & backups Advanced

Interview questions

DELETE removes the rows that match a WHERE clause, fires triggers and can be rolled back. TRUNCATE quickly removes all rows and keeps the table. DROP removes the table itself.

WHERE filters rows before grouping. HAVING filters groups after aggregation, so it can use COUNT, SUM and the other aggregates.

An index is a separate data structure (a B-tree by default) that speeds up lookups. It costs disk space, and it slows down writes because every INSERT and UPDATE must also update the index.

For tied values: ROW_NUMBER gives 1,2,3. RANK gives 1,2,2,4 (it leaves a gap). DENSE_RANK gives 1,2,2,3 (no gap).

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; You can also use DENSE_RANK() = 2 in a subquery.

Practice exercises

Design a schemaLibrary system: books, authors (many-to-many), members and loans.
Write queriesTop 5 customers by spend, products never ordered, monthly revenue growth using LAG.
OptimizeInsert 1M rows with generate_series, compare EXPLAIN ANALYZE before and after adding indexes.

Cheat sheet

Structure

CREATE TABLEnew table
ALTER TABLE … ADD COLUMNchange
REFERENCES t(id)foreign key
CREATE INDEXspeed up

Data

INSERT … RETURNINGcreate
ON CONFLICT DO UPDATEupsert
UPDATE … WHEREupdate
DELETE … WHEREdelete

Query

JOIN … ONcombine
GROUP BY / HAVINGaggregate
WITH x AS (…)CTE
OVER (PARTITION BY)window

Ops

EXPLAIN ANALYZEprofile
BEGIN / COMMITtransaction
pg_dumpbackup
VACUUM ANALYZEmaintenance

What is PostgreSQL? Basic

Why Postgres?

Reliable (ACID)

Feature-rich

Free & open source

Kinds of SQL commands

Install & the psql shell Basic

Windows / macOS

Docker (recommended)

Cloud

psql meta-commands

GUI tools

Data types Basic

Creating tables & constraints Basic

PRIMARY KEY

FOREIGN KEY

NOT NULL

UNIQUE

CHECK

DEFAULT

ON DELETE options

Changing a table

CRUD: Insert, Select, Update, Delete Basic

Create

Read

Update

Delete

Upsert (insert or update)

Filtering, sorting & limits Basic

Aggregates & GROUP BY Intermediate

WHERE

HAVING

Relationships & keys Intermediate

One-to-One

One-to-Many

Many-to-Many

JOINs Intermediate

Subqueries & CTEs Intermediate

Normalization (table design) Intermediate

Indexes Advanced

Index when…

Avoid when…

EXPLAIN ANALYZE & tuning Advanced

Transactions & ACID Advanced

Atomic

Consistent

Isolated

Durable

Window functions Advanced

Views, functions & triggers Advanced

View

Materialized view

Trigger

JSONB, arrays & full-text search Advanced

Roles, security & backups Advanced

Interview questions

DELETE vs TRUNCATE vs DROP?

WHERE vs HAVING?

What is an index, and what does it cost?

RANK vs DENSE_RANK vs ROW_NUMBER?

Find the 2nd highest salary.

Practice exercises

Cheat sheet

Structure

Data

Query

Ops

PostgreSQL notes