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

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

Course Content

Part I: Preface

3 Lessons

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

Introduction to the Course

Course Overview

Architecture Required

Introduction to PostgreSQL

3 Lessons

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

PostgreSQL

Some Useful Commands

Quiz: Introduction to PostgreSQL

Part II: Introduction to Structured Query Language

10 Lessons

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

Part III: Writing SQL Queries

1 Lessons

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

Introduction to Writing SQL Queries

Business Logic

8 Lessons

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

A Small Application

5 Lessons

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

Introduction to a Small Application

Loading and Querying the Database

Python Application for Chinook Database

Challenge: Top-N Factbook Entries

Solution Review: Top-N Factbook Entries

The SQL REPL: An Interactive Setup

8 Lessons

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

Introduction to SQL REPL

Getting Started with psql

Query Rewriting and Validating

Challenge: Testing in PostgreSQL

Solution Review: Testing in PostgreSQL

Regression Testing

Challenge: Regression Testing in PostgreSQL

Solution Review: Regression Testing in PostgreSQL

Indexing in PostgreSQL

4 Lessons

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

Indexing Strategy

Index Access Methods

Advanced Indexing

Quiz: Loading Database, REPL, Testing, and Indexing

Part IV: SQL Toolbox

1 Lessons

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

Introduction to SQL Toolbox

10.

SQL Tools

4 Lessons

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

Sub-languages of SQL

Anatomy of a Select Statement

Processing Function and Data Sources

Joins and Restrictions

11.

Managing Output in SQL

9 Lessons

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

Ordering with the Order By

Top-N Sorts: Limit and Pagination

Aggregates

Restrict Selected Groups: Having

Grouping Sets

Common Table Expressions

Result Sets Operations

Challenge: Managing the Output in SQL

Solution Review: Managing the Output in SQL

12.

Understanding Nulls

6 Lessons

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

Three-Valued Logic

Null in Applications

Outer Joins Introduce Nulls

Quiz: Queries, Output of Queries, and Nulls in SQL

Challenge: Understanding Nulls

Solution Review: Understanding Nulls

13.

Window Functions

3 Lessons

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

Windows and Frames

Partitioning into Different Frames

Using Window Functions

14.

Relations and Joins

3 Lessons

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

Understanding Relations

SQL Join Types

Quiz: Window Functions, Relations, and Joins

15.

Part V: Data Types

1 Lessons

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

Introduction to Data Types

16.

Serialization and Deserialization

6 Lessons

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

Serialization in SQL

Some Relational Theory

Attribute Values, Data Domains, and Data Types

Consistency and Data Type Behavior

Challenge: Generate Specific Numbers

Solution Review: Generate Specific Numbers

17.

PostgreSQL Data Types 101

12 Lessons

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

Introduction to PostgreSQL Data Types

Boolean

Character and Text

Server Encoding and Client Encoding

Numbers

Sequences and the Serial Pseudo Data Type

UUID, Bytea, and Bitstring

Date/Time and Time Zones

Time Intervals

Date/Time Processing and Querying

Network Address Types

Ranges

18.

Denormalized Data Types

9 Lessons

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

Introduction to Denormalized Data Types

XML

Challenge: JSON Data Type

Solution Review: JSON Data Type

19.

Part VI: Data Modeling

1 Lessons

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

Introduction to Data Modeling

20.

Object Relational Mapping and Tooling for Database Modeling

5 Lessons

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

Object Relational Mapping

Tooling for Database Modeling

Writing a Database Model

Modeling Example

Quiz: Data Types and Data Modeling

21.

Normalization

5 Lessons

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

Data Structures and Algorithms

Normal Forms and Anomalies

Normalization Example

Primary and Surrogate Keys

Constraints

22.

Practical Use Case: Geonames

9 Lessons

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

Loading the GeoNames

Features

Countries

Administrative Zoning

Geolocation

Indexing and Lookup

A Sampling of Countries

Challenge: Create a Histogram

Solution Review: Create a Histogram

23.

Modelization Anti-Patterns

4 Lessons

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

Entity Attribute Values

Multiple Values per Column

UUIDs

Quiz: Normalization, GeoNames Database, and Anti-Patterns

24.

Denormalization

10 Lessons

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

Introduction to Denormalization

Premature Optimization and Functional Dependency Trade-Offs

Denormalization with PostgreSQL

Materialized Views

History Tables and Audit Trails

More About Denormalization

Partitioning

Denormalize with Care

Challenge: Generating Series

Solution Review: Generating Series

25.

Not Only SQL

5 Lessons

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

Introduction to Not Only SQL

Schemaless Design in PostgreSQL

Durability Trade-Offs

Scaling Out

Quiz: Denormalization and NOSQL

26.

Part VII: Data Manipulation and Concurrency Control

1 Lessons

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

Introduction to Data Manipulation

27.

Another Small Application

6 Lessons

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

Introduction to Data Manipulation and Concurrency Control

Data Manipulation and Concurrency Control: Insert

Data Manipulation and Concurrency Control: Update

Data Manipulation and Concurrency Control: Delete

Challenge: Data Manipulation

Solution Review: Data Manipulation

28.

Isolation and Locking

5 Lessons

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

Introduction to Isolation and Locking

Transactions and Isolation

Concurrent Updates and Isolation

Modeling for Concurrency

Putting Concurrency to the Test

29.

Computing and Caching in SQL

6 Lessons

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

Introduction to Computing and Caching in SQL

Views

Materialized Views

Quiz: Data Manipulation, Concurrency, Isolation, and Caching

Challenge: Creating a View

Solution Review: Creating a View

30.

Triggers

4 Lessons

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

Introduction to Triggers

Transactional Event-Driven Processing

Trigger and Counters Anti-Pattern

Fixing the Behavior

31.

Listen and Notify

4 Lessons

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

Introduction to Listen and Notify

PostgreSQL Event Publication System

Notifications and Cache Maintenance

Limitations and Support of Listen and Notify

32.

Batch Update, MoMA Collection

4 Lessons

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

Loading the Data

Updating the Data

Concurrency Patterns and Conflict

Quiz: Triggers, Notifications Feature, and Batch Update

33.

Part VIII: PostgreSQL Extensions

1 Lessons

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

Introduction to PostgreSQL Extensions

34.

What’s a PostgreSQL Extension?

3 Lessons

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

PostgreSQL Extensions

Inside PostgreSQL Extensions

Installing and Using PostgreSQL Extensions

35.

Auditing Changes with hstore

5 Lessons

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

Introduction to hstore

Auditing Changes with a Trigger

Testing the Audit Trigger

From hstore Back to a Regular Record

Quiz: Extensions and Auditing Changes

36.

Million Song Dataset

2 Lessons

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

Loading the Data

Discover the Data Model

37.

Using Trigrams for Typos

6 Lessons

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

The pg_trgm Extension

Trigrams, Similarity, and Searches

Complete and Suggest Song Titles

Trigram Indexing

Challenge: Find the Words Similarity Score

Solution Review: Find the Words Similarity Score

38.

Denormalizing Tags with intarray

3 Lessons

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

Advanced Tag Indexing

Searches and User-Defined Tags

Quiz: Trigrams and Denormalizing Tags

39.

The Most Popular Pub Names

5 Lessons

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

Loading the Data

Geolocating the Nearest Pub

Finding the Nearest Pub

Pubs and Cities

The Most Popular Pub Names

40.

Geolocation with PostgreSQL

6 Lessons

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

Geolocation Data Loading

Finding an IP Address in the Ranges

Geolocation Metadata

Emergency Pub

Challenge: Search for the Nearest Circuits

Solution Review: Search for the Nearest Circuits

41.

Counting Distinct Users with HyperLogLog

6 Lessons

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

Introduction to HyperLogLog

Counting Unique Tweet Visitors

Lossy Unique Count with hll

Getting the Visits into Unique Counts

Scheduling Estimates Computations

Quiz: Geolocation and HyperLogLog

42.

Appendix

3 Lessons

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

PostgreSQL: Setup on the Local Machine

The PostgreSQL extension: How to Find It

A Short List of Noteworthy Extensions

Course Author

Dimitri Fontaine

Trusted by 2.5 million developers working at companies

"These are high-quality courses. Trust me. I own around 10 and the price is worth it for the content quality. EducativeInc came at the right time in my career. I'm understanding topics better than with any book or online video tutorial I've done. Truly made for developers. Thanks"

Anthony Walker

@_webarchitect_

"Just finished my first full #ML course: Machine learning for Software Engineers from Educative, Inc. ... Highly recommend!"

Evan Dunbar

ML Engineer

"You guys are the gold standard of crash-courses... Narrow enough that it doesn't need years of study or a full blown book to get the gist, but broad enough that an afternoon of Googling doesn't cut it."

Carlos Matias La Borde

Software Developer

"I spend my days and nights on Educative. It is indispensable. It is such a unique and reader-friendly site"

Souvik Kundu

Front-end Developer

"Your courses are simply awesome, the depth they go into and the breadth of coverage is so good that I don't have to refer to 10 different websites looking for interview topics and content."

Vinay Krishnaiah

Software Developer

"I've tried probably 5-7 different sites and Educative is easily the best. It perfectly blends explanation with interactivity"

Eric Downs

Musician/Entrepeneur

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

Put your skills to the test in a simulated interview setting. Receive personalized feedback based on your performance. Available in Premium & Premium Plus plans.

Adaptive Learning

At various checkpoints throughout Educative courses, you will be prompted to take a quick assessment. Receive a condensed curriculum tailored to your strengths and skill gaps.

Explain with AI

Select any text within any Educative course, and get an instant explanation — without ever leaving your browser.

AI Code Mentor

AI Code Mentor helps you quickly identify errors in your code, learn from your mistakes, and nudge you in the right direction — just like a 1:1 tutor!

Recommended before starting this course

Course

An Introductory Guide to SQL

Gain insights into SQL by exploring database creation, data manipulation, and advanced queries. Learn about practical applications and practice for SQL interviews to build confidence and readiness.

beginner

13 hours

Course

An Introductory Guide to SQL

beginner

13 hour

Related Courses and Skill Paths

Course

SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Discover common SQL antipatterns, their pitfalls, and solutions. Gain insights into logical, physical, query, and application antipatterns. Learn about effective, risk-free database practices.

beginner

6 hours 45 mins

Course

Simple Anomaly Detection using SQL

Gain insights into using SQL for anomaly detection. Delve into applying mean, standard deviation, and z-score to identify and investigate data anomalies, enhancing your analytical skills.

beginner

1 hour

Course

MongoDB: The Complete Guide

Gain insights into MongoDB's core concepts, including queries, CRUD operations, aggregation, and more. Discover how to build applications with hands-on experience and interactive widgets.

intermediate

3 hours

Course

SQL Antipatterns: Avoiding the Pitfalls of Database Programming

beginner

6 hour 45 min

Course

Simple Anomaly Detection using SQL

beginner

1 hour

Course

MongoDB: The Complete Guide