PostgreSQL: Up and Running, 4th Edition

Chapter 7. SQL: The PostgreSQL Way

A Note for Early Release Readers

With Early Release ebooks, you get books in their earliest form—the authors’ raw and unedited content as they write—so you can take advantage of these technologies long before the official release of these titles.

This will be the 7th chapter of the final book. Please note that the GitHub repo will be made active later on.

If you’d like to be actively involved in reviewing and commenting on this draft, please reach out to the editor at [e-mail].

PostgreSQL surpasses other database products in ISO SQL compliance. It cements its lead by adding constructs that range from convenient syntax shorthands to avant-garde features that break the bounds of traditional SQL. In this chapter, we’ll cover some SQL tidbits not often found in other databases. For this chapter, you should have a working knowledge of SQL; otherwise, you may not appreciate the labor-saving amuse-bouche that PostgreSQL brings to the table.

In our many years of writing SQL, we have come to appreciate the little things that make better use of our typing. Only PostgreSQL offers some of the gems we present in this section. Often this means that the construction is not ISO-compliant. If thy God demands strict observance to the ISO SQL standards, abstain from some of the short-cuts that we’ll be showing.

DISTINCT ON

One of our favorites is DISTINCT ON. It behaves like DISTINCT, but with two enhancements: you can specify which columns to consider ...