PostgreSQL Querying: Advanced Techniques

Extend your SQL skills with advanced PostgreSQL techniques for real-world data problems.

Description

Go beyond SQL basics and learn advanced PostgreSQL querying patterns used in production. You will compare multiple solution strategies, apply window functions, table expressions, and grouping techniques, and choose the best approach for correctness, readability, and performance.

Subjects

1. Refresher
2. Table Expressions
3. Set Operators
4. Window Ranking, Offset and Aggregate Functions
5. Pivoting and Grouping Sets
6. Programming with PL/pgSQL

In this module we make sure everyone remembers the basics of querying so we can continue at the same level. Goals:

• Standard elements of a SELECT query
• Joins
• Grouping and Aggregating
• Subqueries

Table expressions are SQL elements that are not a physical table but can be used as if they were one. Goals:

• Views
• Set-returning Functions
• Derived Tables
• Common Table Expressions (CTE)
• LATERAL joins
• When to pick which option?

Set operators allow you to vertically combine and compare two similar result sets. This contrasts joins, where you combine data horizontally. Goals:

• UNION (ALL)
• EXCEPT / INTERSECT

Window functions are ideal for analytics scenarios like running totals and moving averages. They also allow you to look at previous or following values in your result set. Goals:

• Window Functions
• OVER
• Frame
• PARTITION BY

Pivoting is often needed to exchange data between different systems. Grouping Sets can help when offering analytic data: in a single query you can cover multiple different aggregations at once. Goals:

• Pivoting with conditional aggregation
• Crosstab (tablefunc)
• GROUPING SETS
• GROUP BY CUBE / ROLLUP

You can use SQL for querying, but you can also program in PL/pgSQL. Why is this useful in a database, what does the syntax look like, and how do functions and procedures fit in? Goals:

• DO blocks
• Variables
• Functions and Procedures
• Control Flow
• Exceptions and error handling
• Transactions