Home>
Courses>

The Art of PostgreSQL

The Art of PostgreSQL

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.

Advanced

205 Lessons

65h

Certificate of Completion

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.

AI-POWERED

Explanations

AI-POWERED

Explanations

This course includes

186 Playgrounds
13 Quizzes

This course includes

186 Playgrounds
13 Quizzes
Course Overview
What You'll Learn
Course Content

Course Overview

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 ...Show More

What You'll Learn

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

What You'll Learn

An understanding of PostgreSQL and its applications in managing databases with CRUD operations

Show more

Course Content

1.

Part I: Preface

Get familiar with mastering SQL and PostgreSQL for efficient data handling and application integration.
Introduction to the Course
Course Overview
Architecture Required
2.

Introduction to PostgreSQL

Look at PostgreSQL's robust features, essential commands, and extensibility for modern applications.
PostgreSQL
Some Useful Commands
Quiz: Introduction to PostgreSQL
3.

Part II: Introduction to Structured Query Language

Examine SQL principles, security measures, data processing, window functions, and style guidelines.
Introduction to SQL
Utilizing SQL
The First Use Case
A Word About SQL Injection
PostgreSQL Protocol: Server-Side Prepared Statements
Back to Discovering SQL
Computing Weekly Changes
SQL Style Guidelines
Challenge: Compute Fortnightly Changes
Solution Review: Compute Fortnightly Changes
4.

Part III: Writing SQL Queries

Grasp the fundamentals of writing, debugging, and maintaining SQL queries in application code.
Introduction to Writing SQL Queries
5.

Business Logic

Recognize the importance of strategically placing business logic in PostgreSQL for efficiency.
Business Logic in the Database
Business Logic in Application Code
Correctness
Efficiency
Stored Procedures: A Data Access API
Quiz: SQL and Business Logic
Challenge: Implement Business Logic
Solution Review: Implement Business Logic
6.

A Small Application

5 Lessons

Focus on building a small PostgreSQL application, querying data, and handling SQL in Python.
7.

The SQL REPL: An Interactive Setup

8 Lessons

Master the steps to effectively use psql for interactive SQL testing and regression testing in PostgreSQL.
8.

Indexing in PostgreSQL

4 Lessons

Learn how to use effective indexing strategies and access methods to optimize PostgreSQL.
9.

Part IV: SQL Toolbox

1 Lesson

Get started with enhancing your SQL skills through practical examples and advanced PostgreSQL concepts.
10.

SQL Tools

4 Lessons

Break apart SQL sublanguages, SELECT statement anatomy, data sources, and advanced joins.
11.

Managing Output in SQL

9 Lessons

Grasp the fundamentals of optimizing query output, sorting, pagination, and aggregate functions in SQL.
12.

Understanding Nulls

6 Lessons

Take a closer look at handling null values in SQL, their implications, and practical applications.
13.

Window Functions

3 Lessons

Investigate PostgreSQL’s window functions, their frames, partitioning, and analytical capabilities.
14.

Relations and Joins

3 Lessons

Piece together the parts of relations and various join types in PostgreSQL.
15.

Part V: Data Types

1 Lesson

Get familiar with data types in PostgreSQL crucial for improving correctness and performance.
16.

Serialization and Deserialization

6 Lessons

Unpack the core of serialization, relational theory, data types, and handling date-specific queries in PostgreSQL.
17.

PostgreSQL Data Types 101

12 Lessons

Examine PostgreSQL's diverse data types, effective data handling, encoding, numeric precision, temporal management, and network analysis.
18.

Denormalized Data Types

9 Lessons

Grasp the fundamentals of PostgreSQL's denormalized data types and their practical applications.
19.

Part VI: Data Modeling

1 Lesson

Take a look at the importance of database schema modeling to simplify operations.
20.

Object Relational Mapping and Tooling for Database Modeling

5 Lessons

Simplify complex topics on database modeling, tooling, and schema refinement with PostgreSQL.
21.

Normalization

5 Lessons

Build on normalization principles to ensure data consistency, integrity, and optimal design.
22.

Practical Use Case: Geonames

9 Lessons

Get familiar with normalizing and querying GeoNames data in PostgreSQL effectively.
23.

Modelization Anti-Patterns

4 Lessons

Unpack the core of PostgreSQL modelization anti-patterns, including EAV models, multiple values per column, and UUIDs.
24.

Denormalization

10 Lessons

Examine denormalization to optimize database performance, manage data efficiently, and address practical coding challenges.
25.

Not Only SQL

5 Lessons

Grasp the fundamentals of PostgreSQL's flexibility in handling NoSQL features, durability, and scaling out.
26.

Part VII: Data Manipulation and Concurrency Control

1 Lesson

Dig deeper into data manipulation, transaction processing, and ensuring data integrity in PostgreSQL.
27.

Another Small Application

6 Lessons

Tackle data manipulation and concurrency control, featuring PostgreSQL’s efficiency in handling inserts, updates, and deletes.
28.

Isolation and Locking

5 Lessons

Build on PostgreSQL's isolation and locking for effective concurrency management in transactions.
29.

Computing and Caching in SQL

6 Lessons

Step through computing and caching techniques in SQL, including views and materialized views.
30.

Triggers

4 Lessons

Get started with PostgreSQL triggers, their transactional processing, and addressing concurrency issues.
31.

Listen and Notify

4 Lessons

Examine PostgreSQL's listen and notify system for asynchronous communication between servers and clients.
32.

Batch Update, MoMA Collection

4 Lessons

Apply your skills to load, update, and manage the MoMA Collection in PostgreSQL.
33.

Part VIII: PostgreSQL Extensions

1 Lesson

Explore PostgreSQL's extensible data types and user-defined extensions, rooted in its original design.
34.

What’s a PostgreSQL Extension?

3 Lessons

Investigate how PostgreSQL extensions enhance functionality through SQL objects and efficient installation.
35.

Auditing Changes with hstore

5 Lessons

Master hstore for auditing changes with triggers, testing, and reverting to regular records.
36.

Million Song Dataset

2 Lessons

Learn how to use the Million Song Dataset for importing, querying, and exploring music data.
37.

Using Trigrams for Typos

6 Lessons

Get started with improving fuzzy searches, typo correction, and efficient querying using trigrams in PostgreSQL.
38.

Denormalizing Tags with intarray

3 Lessons

Work your way through efficient tag indexing and advanced searches using PostgreSQL's intarray extension.
39.

The Most Popular Pub Names

5 Lessons

Apply your skills to load, geolocate, query, and analyze pub name data in PostgreSQL.
40.

Geolocation with PostgreSQL

6 Lessons

Solve problems in geolocation data processing and querying IP addresses with PostgreSQL.
41.

Counting Distinct Users with HyperLogLog

6 Lessons

Simplify complex topics like HyperLogLog for estimating unique counts with minimal storage.
42.

Appendix

3 Lessons

Build on setting up PostgreSQL, utilizing extensions, and optimizing with noteworthy tools.

Course Author

Trusted by 1.4 million developers working at companies

Anthony Walker

@_webarchitect_

Evan Dunbar

ML Engineer

Carlos Matias La Borde

Software Developer

Souvik Kundu

Front-end Developer

Vinay Krishnaiah

Software Developer

Eric Downs

Musician/Entrepeneur

Kenan Eyvazov

DevOps Engineer

Souvik Kundu

Front-end Developer

Eric Downs

Musician/Entrepeneur

Anthony Walker

@_webarchitect_

Evan Dunbar

ML Engineer

Hands-on Learning Powered by AI

See how Educative uses AI to make your learning more immersive than ever before.

Instant Code Feedback

Evaluate and debug your code with the click of a button. Get real-time feedback on test cases, including time and space complexity of your solutions.

AI-Powered Mock Interviews

Adaptive Learning

Explain with AI

AI Code Mentor

FOR TEAMS

Interested in this course for your business or team?

Unlock this course (and 1,000+ more) for your entire org with DevPath