PostgreSQL Study Notes

These are my personal study notes from learning PostgreSQL and using relational databases in projects. They collect the core ideas, SQL patterns, and practical checks I want to be able to revisit when designing, querying, and maintaining a database.

The examples use PostgreSQL 18 and build on one small order-management database, so tables and queries connect across chapters. SQL is the main focus. The application chapter uses JavaScript with Node.js and the pg package.

Core topics

1. PostgreSQL foundations
2. Databases, schemas, and tables
3. Selecting, filtering, and sorting
4. Expressions, functions, and dates
5. Joins and relationships
6. Aggregation and grouping
7. Subqueries, CTEs, and set operations
8. Inserts, updates, deletes, and transactions
9. Keys, constraints, and data integrity
10. Indexes and query plans
11. Views and generated data
12. JSON and JSONB
13. PL/pgSQL functions and triggers
14. Roles, privileges, and maintenance
15. Node.js and PostgreSQL
16. Capstone: order management database

Quick reference

• All SQL and Node.js code samples
• Complete chapter review questions and answers

How I use these notes

I keep the chapters focused on the concepts I need most often. Each one explains the purpose of a feature, shows SQL that can be adapted to a real schema, and points out mistakes that are easy to make. The examples use named tables and consistent columns so I can follow how a relational design grows from a few rows into a working application database.

The last chapters gather the examples and review questions in one place. I update these notes as I test queries and use the ideas in my own work.

Reference

• PostgreSQL 18 documentation
• PostgreSQL SQL language reference
• PostgreSQL release notes
• node-postgres documentation

Links

• Portfolio: https://www.ashishranjan.net
• GitHub: https://github.com/a2rp
• CodePen: https://codepen.io/ash1198
• LinkedIn: https://www.linkedin.com/in/aashishranjan
• Facebook: https://www.facebook.com/theash.ashish/
• YouTube: https://www.youtube.com/@ashishranjan-ashz?sub_confirmation=1
• Email: mailto:ash.ranjan09@gmail.com

Support

• Support: https://a2rp-donation-page.netlify.app/
• Buy Me a Coffee: https://buymeacoffee.com/ashishranjan
• Patreon: https://www.patreon.com/ashishranjan