Postgres Playground

psql basics

Never seen Postgres from the command line before? Start here! We’ve loaded a sample database in for you and you’re a superuser.

Learn SQL

Learn about the basics of SQL

Joins in Postgres

Learn about inner and outer joins in this tutorial

Indexing (B-Tree Indexes)

Learn how to create a b-tree index in Postgres. No yardwork required!

Transactions

Learn why and how to use transactions.

Basics of PostGIS

Test some sample spatial queries and functions with PostGIS!

Creating Tables

Learn the basics of Postgres table creation, with primary keys, foreign keys, and data types.

Row Level Security

Learn how to use Postgres’ Row Level Security functionality. It’s a great tool for managing key-based partitioning in a multi-tenant world.

Partitioning

Learn how to create partitions with native Postgres and pg_partman with an IoT sample dataset.

Window Functions for Data Analysis

Walk through sample Window functions with and without CTEs for running totals, rolling averages, first and last values, lag and lead, ranking, and ntiles.

Basics of JSON

Learn JSON interactions including manipulating, querying, saving, and optimizing a simple object structure.

High level performance analysis

A quick introduction to some of the most important Postgres performance metrics. Including cache hit ratio, index hit, and bloat.

Postgres Contraints

This tutorial shows you how to create foreign keys, work with cascading deletes, not-null constraints, check constraints, and exclusion constraints. Also, examples for time-based check constraints and box based exclusion constraints.

Query performance analytics

A tutorial for how to find your least performant queries and a lot more query performance information

Casting Data Types

Learn about casting data types with in-depth examples for time intervals and date and time formats.

CTEs and Window Functions

Learn how to query data with US Birth data.

Materialized Views

Create a basic materialized view using a sample ecommerce data set.

Percentage Calculations

Learn how to do percentage calculations in Postgres in one pass.

Working with Time in Postgres

A primer on working with time in Postgres. Covers data types, query formats, intervals, overlaps, indexing, and roll ups.

Strings to Arrays

Learn about the unnest(array) function and how to break about data from a csv in a csv.

Custom data types: DOMAINS

Learn about user-defined datatypes, domain types, and how to create constraints that validate values.

Custom data types : user defined types

Hands on examples for composite types, enumerated types, and range types.

Summaries with Aggregate Filters and Windows

Aggregate filtering with window functions to strip out just the information you want

Using FILTER vs CTEs and CASE WHEN

When aggregating based on status, try using FILTER instead of CASE statements.

SQL Tricks for More Effective CRUD

A quick tutorial of some simple and not-so-simple CRUD - create, read, update, and delete.

LATERAL JOIN

“This is a perfect scenario for a lateral join!” Have you ever heard that? If you were wondering “what’s a lateral join?” This will help. It’s pretty simple, but the combination of it with other SQL capabilities is quite powerful.

PostgreSQL - Just for Kids

Intro to SQL tutorial for kids. This is a hands on tutorial using an existing Postgres database running in a web browser.

Advent of Code - Day 1

We’re publishing a few days to do Advent of Code challenges in SQL. Day 1 makes use of sequences and sum over ranges.

Advent of Code - Day 2

SQL Solutions for Day 2 of Advent of Code. This one has some functions to find out who’s the winner of each battle and summing the wins.

Advent of Code - Day 3

SQL Solutions for Day 3 of Advent of Code. This one includes functions for length(), left(), right(), regexp_replace(), COLLATE commands, window function lag(), and more.

Advent of Code - Day 4

SQL Solutions for Day 4 of Advent of Code. This one includes the function split_part(), the int4range data type, and range operators.

Advent of Code - Day 5

SQL Solutions for Day 5 of Advent of Code. This one includes the function split_part(), the int4range data type, and range operators.

Advent of Code - Day 6

SQL Solutions for Day 6 of Advent of Code. This one includes regexp_split_to_table, strpos, lag() functions and COUNT(DISTINCT). Also, a great example of using a DO function with RAISE NOTICE.

Advent of Code - Day 7

SQL Solutions for Day 7 of Advent of Code. This one includes recursive queries, text arrays, and tons of Postgres functions.

Advent of Code - Day 8

SQL Solutions for Day 8 of Advent of Code. This one includes plpgsql, row_number window functions, sequences, and regexp_split_to_table.

Advent of Code - Day 9

SQL Solutions for Day 9 of Advent of Code. This one uses a custom function and colored ASCII art.

Advent of Code - Day 10

SQL Solutions for Day 10 of Advent of Code. This one uses sequences and the OVER() function.

Advent of Code - Day 19

SQL Solutions for Day 19 of Advent of Code. Featuring regep_split_to_array and recursive functions. These functions even have terminal animations!

Advent of Code - Day 17

SQL Solutions for Day 17 of Advent of Code. This one uses jsonb and arrays to keep track of points.

Advent of Code - Day 16

SQL Solutions for Day 16 of Advent of Code. This one calls a plpgsql function recursively.

Advent of Code - Day 12

Day 12 of AOC we have a SQL tutorial for the Hill Climbing Algorithm. This exercise uses parsing, an IDENTITY column, functions, and text arrays.

Advent of Code - Day 11

Day 11 of AOC we have new hands on SQL for playing Monkey in the Middle. This one uses sequences, string functions, and window functions.

Advent of Code - Day 13

Day 13 of AOC. This exercise has sequencing, regexp_replace(), lag(), and overlay().

Advent of Code - Day 15

Day 15 of AOC. This one has examples for CTEs, sequences, int4range, range_agg(), regexp_substr() / regexp_match(), and UPSERT.

Advent of Code - Day 14

Day 14 of AOC. Join in for exercises on lag() functions, sequences, string_to_table(), split_part(), and generate_series().

Postgres Functions for Rolling Up Data by Date

Sample code and tutorial for using Postgres intervals, date_trunc, rollup, cube and formatting output with to_char.

Postgres Users and Roles

Learn about creating Postgres user roles, role groups, and login and password details for users.

psql Echo Commands

Learn about -E, -echo-hidden, -e, -echo-queries in the Postgres command line interface, psql

Working with Money in Postgres

A primer for working with money in Postgres including what data type to choose, storing currency, and some sample functions.

Advent of Code - Day 18

Day 18 of AOC we have new hands on SQL for the Boiling Boulders puzzle. This one uses sequences, a recursive CTE, and the plpgsql language.

Tags and Postgres Arrays, a Purrrfect Combination

Review some of the ways to store tags in a database from basic relational models to text arrays. This tutorial has data models, performance tests, sample queries, and guidance on choosing the best path.