PostgreSQL Querying: Advanced Techniques

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

Refresher

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

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

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 Ranking, Offset and Aggregate Functions

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 and Grouping Sets

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

Programming in PL/pgSQL

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