Back To Course Home

The Art of PostgreSQL

0% completed

Part I: Preface

Introduction to the Course Course Overview Architecture Required

Introduction to PostgreSQL

Some Useful Commands

Quiz: Introduction to PostgreSQL

Part II: Introduction to Structured Query Language

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

Introduction to Writing SQL Queries

Business Logic

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

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

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

Indexing Strategy Index Access Methods Advanced Indexing Quiz: Loading Database, REPL, Testing, and Indexing

Part IV: SQL Toolbox

Introduction to SQL Toolbox

SQL Tools

Sub-languages of SQL Anatomy of a Select Statement Processing Function and Data Sources Joins and Restrictions

Managing Output 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

Understanding Nulls

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

Window Functions

Windows and Frames Partitioning into Different Frames Using Window Functions

Relations and Joins

Understanding Relations SQL Join Types Quiz: Window Functions, Relations, and Joins

Part V: Data Types

Introduction to Data Types

Serialization and Deserialization

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

PostgreSQL Data Types 101

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

Denormalized Data Types

Introduction to Denormalized Data Types Arrays GIN Indexing Composite Types XML JSON Enum Challenge: JSON Data Type Solution Review: JSON Data Type

Part VI: Data Modeling

Introduction to Data Modeling

Object Relational Mapping and Tooling for Database Modeling

Object Relational Mapping Tooling for Database Modeling Writing a Database Model Modeling Example Quiz: Data Types and Data Modeling

Normalization

Data Structures and Algorithms Normal Forms and Anomalies Normalization Example Primary and Surrogate Keys Constraints

Practical Use Case: Geonames

Loading the GeoNames Features Countries Administrative Zoning Geolocation Indexing and Lookup A Sampling of Countries Challenge: Create a Histogram Solution Review: Create a Histogram

Modelization Anti-Patterns

Entity Attribute Values Multiple Values per Column UUIDs Quiz: Normalization, GeoNames Database, and Anti-Patterns

Denormalization

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

Not Only SQL

Introduction to Not Only SQL Schemaless Design in PostgreSQL Durability Trade-Offs Scaling Out Quiz: Denormalization and NOSQL

Part VII: Data Manipulation and Concurrency Control

Introduction to Data Manipulation

Another Small Application

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

Isolation and Locking

Introduction to Isolation and Locking Transactions and Isolation Concurrent Updates and Isolation Modeling for Concurrency Putting Concurrency to the Test

Computing and Caching in SQL

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

Triggers

Introduction to Triggers Transactional Event-Driven Processing Trigger and Counters Anti-Pattern Fixing the Behavior

Listen and Notify

Introduction to Listen and Notify PostgreSQL Event Publication System Notifications and Cache Maintenance Limitations and Support of Listen and Notify

Batch Update, MoMA Collection

Loading the Data Updating the Data Concurrency Patterns and Conflict Quiz: Triggers, Notifications Feature, and Batch Update

Part VIII: PostgreSQL Extensions

Introduction to PostgreSQL Extensions

What’s a PostgreSQL Extension?

PostgreSQL Extensions Inside PostgreSQL Extensions Installing and Using PostgreSQL Extensions

Auditing Changes with hstore

Introduction to hstore Auditing Changes with a Trigger Testing the Audit Trigger From hstore Back to a Regular Record Quiz: Extensions and Auditing Changes

Million Song Dataset

Loading the Data Discover the Data Model

Using Trigrams for Typos

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

Denormalizing Tags with intarray

Advanced Tag Indexing Searches and User-Defined Tags Quiz: Trigrams and Denormalizing Tags

The Most Popular Pub Names

Loading the Data Geolocating the Nearest Pub Finding the Nearest Pub Pubs and Cities The Most Popular Pub Names

Geolocation with PostgreSQL

Geolocation Data Loading

Finding an IP Address in the Ranges

Geolocation Metadata

Challenge: Search for the Nearest Circuits

Solution Review: Search for the Nearest Circuits

Counting Distinct Users with HyperLogLog

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

Appendix

PostgreSQL: Setup on the Local Machine The PostgreSQL extension: How to Find It A Short List of Noteworthy Extensions

Emergency Pub

Learn how to find a pub near geolocation.

We'll cover the following

Geolocation of an IP address
Use case: Find the nearest pub
Try it yourself

What if you want to make an application to help people find the nearest pub from where they are currently? Now that you know their location from the IP address they’re using in their browser, it should be easy enough, right?

We have downloaded a list of pubs from the UK; we’re going to use an IP address that should be in the UK too. Let’s assume www.ox.ac.uk has the address 129.67.242.154.

Geolocation of an IP address

Knowing that we can search the geolocation of this IP address:

Get hands-on with 1200+ tech skills courses.