Gain insights into PostgreSQL's robust features and high performance. Learn about SQL integration, data types, and extensions with hands-on code examples for practical application.
4.8
205 Lessons
65h
Updated 1 week ago
Join 2.9 million developers at
Join 2.9 million developers at
LEARNING OBJECTIVES
- An understanding of PostgreSQL and its applications in managing databases with CRUD operations
- Familiarity with the core components of PostgreSQL, such as SQL, REPL, Window functions, and data modeling
- Hands-on experience in using SQL queries with PostgreSQL and its extensions
- The ability to write and integrate SQL queries into your workflow, enabling efficient data retrieval and manipulation
Learning Roadmap
1.
Part I: Preface
Part I: Preface
Get familiar with mastering SQL and PostgreSQL for efficient data handling and application integration.
2.
Introduction to PostgreSQL
Introduction to PostgreSQL
Look at PostgreSQL's robust features, essential commands, and extensibility for modern applications.
3.
Part II: Introduction to Structured Query Language
Part II: Introduction to Structured Query Language
10 Lessons
10 Lessons
Examine SQL principles, security measures, data processing, window functions, and style guidelines.
5.
Business Logic
Business Logic
8 Lessons
8 Lessons
Recognize the importance of strategically placing business logic in PostgreSQL for efficiency.
6.
A Small Application
A Small Application
5 Lessons
5 Lessons
Focus on building a small PostgreSQL application, querying data, and handling SQL in Python.
7.
The SQL REPL: An Interactive Setup
The SQL REPL: An Interactive Setup
8 Lessons
8 Lessons
Master the steps to effectively use psql for interactive SQL testing and regression testing in PostgreSQL.
8.
Indexing in PostgreSQL
Indexing in PostgreSQL
4 Lessons
4 Lessons
Learn how to use effective indexing strategies and access methods to optimize PostgreSQL.
10.
SQL Tools
SQL Tools
4 Lessons
4 Lessons
Break apart SQL sublanguages, SELECT statement anatomy, data sources, and advanced joins.
11.
Managing Output in SQL
Managing Output in SQL
9 Lessons
9 Lessons
Grasp the fundamentals of optimizing query output, sorting, pagination, and aggregate functions in SQL.
12.
Understanding Nulls
Understanding Nulls
6 Lessons
6 Lessons
Take a closer look at handling null values in SQL, their implications, and practical applications.
13.
Window Functions
Window Functions
3 Lessons
3 Lessons
Investigate PostgreSQL’s window functions, their frames, partitioning, and analytical capabilities.
14.
Relations and Joins
Relations and Joins
3 Lessons
3 Lessons
Piece together the parts of relations and various join types in PostgreSQL.
16.
Serialization and Deserialization
Serialization and Deserialization
6 Lessons
6 Lessons
Unpack the core of serialization, relational theory, data types, and handling date-specific queries in PostgreSQL.
17.
PostgreSQL Data Types 101
PostgreSQL Data Types 101
12 Lessons
12 Lessons
Examine PostgreSQL's diverse data types, effective data handling, encoding, numeric precision, temporal management, and network analysis.
18.
Denormalized Data Types
Denormalized Data Types
9 Lessons
9 Lessons
Grasp the fundamentals of PostgreSQL's denormalized data types and their practical applications.
20.
Object Relational Mapping and Tooling for Database Modeling
Object Relational Mapping and Tooling for Database Modeling
5 Lessons
5 Lessons
Simplify complex topics on database modeling, tooling, and schema refinement with PostgreSQL.
21.
Normalization
Normalization
5 Lessons
5 Lessons
Build on normalization principles to ensure data consistency, integrity, and optimal design.
22.
Practical Use Case: Geonames
Practical Use Case: Geonames
9 Lessons
9 Lessons
Get familiar with normalizing and querying GeoNames data in PostgreSQL effectively.
23.
Modelization Anti-Patterns
Modelization Anti-Patterns
4 Lessons
4 Lessons
Unpack the core of PostgreSQL modelization anti-patterns, including EAV models, multiple values per column, and UUIDs.
24.
Denormalization
Denormalization
10 Lessons
10 Lessons
Examine denormalization to optimize database performance, manage data efficiently, and address practical coding challenges.
25.
Not Only SQL
Not Only SQL
5 Lessons
5 Lessons
Grasp the fundamentals of PostgreSQL's flexibility in handling NoSQL features, durability, and scaling out.
27.
Another Small Application
Another Small Application
6 Lessons
6 Lessons
Tackle data manipulation and concurrency control, featuring PostgreSQL’s efficiency in handling inserts, updates, and deletes.
28.
Isolation and Locking
Isolation and Locking
5 Lessons
5 Lessons
Build on PostgreSQL's isolation and locking for effective concurrency management in transactions.
29.
Computing and Caching in SQL
Computing and Caching in SQL
6 Lessons
6 Lessons
Step through computing and caching techniques in SQL, including views and materialized views.
30.
Triggers
Triggers
4 Lessons
4 Lessons
Get started with PostgreSQL triggers, their transactional processing, and addressing concurrency issues.
31.
Listen and Notify
Listen and Notify
4 Lessons
4 Lessons
Examine PostgreSQL's listen and notify system for asynchronous communication between servers and clients.
32.
Batch Update, MoMA Collection
Batch Update, MoMA Collection
4 Lessons
4 Lessons
Apply your skills to load, update, and manage the MoMA Collection in PostgreSQL.
34.
What’s a PostgreSQL Extension?
What’s a PostgreSQL Extension?
3 Lessons
3 Lessons
Investigate how PostgreSQL extensions enhance functionality through SQL objects and efficient installation.
35.
Auditing Changes with hstore
Auditing Changes with hstore
5 Lessons
5 Lessons
Master hstore for auditing changes with triggers, testing, and reverting to regular records.
36.
Million Song Dataset
Million Song Dataset
2 Lessons
2 Lessons
Learn how to use the Million Song Dataset for importing, querying, and exploring music data.
37.
Using Trigrams for Typos
Using Trigrams for Typos
6 Lessons
6 Lessons
Get started with improving fuzzy searches, typo correction, and efficient querying using trigrams in PostgreSQL.
38.
Denormalizing Tags with intarray
Denormalizing Tags with intarray
3 Lessons
3 Lessons
Work your way through efficient tag indexing and advanced searches using PostgreSQL's intarray extension.
39.
The Most Popular Pub Names
The Most Popular Pub Names
5 Lessons
5 Lessons
Apply your skills to load, geolocate, query, and analyze pub name data in PostgreSQL.
40.
Geolocation with PostgreSQL
Geolocation with PostgreSQL
6 Lessons
6 Lessons
Solve problems in geolocation data processing and querying IP addresses with PostgreSQL.
41.
Counting Distinct Users with HyperLogLog
Counting Distinct Users with HyperLogLog
6 Lessons
6 Lessons
Simplify complex topics like HyperLogLog for estimating unique counts with minimal storage.
42.
Appendix
Appendix
3 Lessons
3 Lessons
Build on setting up PostgreSQL, utilizing extensions, and optimizing with noteworthy tools.
Certificate of Completion
Showcase your accomplishment by sharing your certificate of completion.
Complete more lessons to unlock your certificate
Developed by MAANG Engineers
ABOUT THIS COURSE
PostgreSQL is a popular and widely used open-source relational database management system. It has gained significant market share in recent years due to its robust features, high performance, and ease of use. Many companies, from small startups to large enterprises, are adopting PostgreSQL as their primary database system, making it a sought-after skill for developers.
You will learn PostgreSQL with numerous code examples using different approaches. This course will enable you to seamlessly integrate SQL into your applications. Each new SQL concept is presented with straightforward queries to simplify your learning and ensures that you remain focused on the new concepts. In addition, the course refers to the PostgreSQL documentation—one of the best reference manuals available—whenever necessary.
By the end of this course, you will discover details of the additional data types and extensions available with PostgreSQL. This course makes it a valuable resource for developers looking to expand their skill set.
ABOUT THE AUTHOR
Dimitri Fontaine
Every Educative resource is designed by our team of ex-MAANG software engineers and PhD computer science educators — subject matter experts who've shipped production code at scale and taught the theory behind it. No videos, no fluff.
Trusted by 2.9 million developers working at companies
A
Anthony Walker
@_webarchitect_
E
Evan Dunbar
ML Engineer
S
Software Developer
Carlos Matias La Borde
S
Souvik Kundu
Front-end Developer
V
Vinay Krishnaiah
Software Developer
Built for 10x Developers
No Passive Learning
Learn by building with project-based lessons and in-browser code editor
Personalized Roadmaps
The platform adapts to your strengths & skills gaps as you go
Future-proof Your Career
Get hands-on with in-demand skills
AI Code Mentor
Write better code with AI feedback, smart debugging, and "Ask AI"
MAANG+ Interview Prep
AI Mock Interviews simulate every technical loop at top companies
Free Resources
