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
Course Overview
What You'll Learn
Course Content
Recommendations

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
PostgreSQL is a popular and widely used open-source relational database management system. It has gained significant market shar...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
An understanding of PostgreSQL and its applications in managing databases with CRUD operations

Show more

Course Content

1.

Part I: Preface

3 Lessons

Get familiar with mastering SQL and PostgreSQL for efficient data handling and application integration.

2.

Introduction to PostgreSQL

3 Lessons

Look at PostgreSQL's robust features, essential commands, and extensibility for modern applications.

4.

Part III: Writing SQL Queries

1 Lessons

Grasp the fundamentals of writing, debugging, and maintaining SQL queries in application code.

6.

A Small Application

5 Lessons

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

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 Lessons

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.

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 Lessons

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.

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 Lessons

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.

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 Lessons

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

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 Lessons

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 2.6 million developers working at companies

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

Free Resources

FOR TEAMS

Interested in this course for your business or team?

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