The Art of PostgreSQL, 2nd Edition

New Chapter This second edition of the book includes a whole new chapter about PostgreSQL Extensions. Written by the author of the CREATE EXTENSION command! Second Edition The Art of PostgreSQL is the new title of "Mastering PostgreSQL in Application Development", acclaimed one of the very best resources around to learn SQL! Improved Book Design Because the first edition of the book has been so well received, the second edition is now available as a redesigned paperback printed format, making it a strong fit for your library! SQL is Code Learn how to deal with SQL in your developer workflow: versioning, testing, code reviewing, deployment. Best practice and tools are covered. Practical Examples Learn advanced SQL with practical examples and datasets that help you get the most of the book! Every query solves a practical use case and is given in context. Database Modeling The book covers (de-)normalisation with simple practical examples to dive into this seemingly complex topic, including Caching and Indexing Strategy. SQL For Developers This book is for developers, covering advanced SQL techniques for data processing. Learn how to get exactly the result set you need in your application's code! Write Efficient SQL Writing efficient SQL is easier than it looks, and begins with database modeling and writing clear code. The book teaches you how to write fast queries! Batteries Included The book comes with open data sets and instructions to get them so that you can run all the SQL queries at home, edit them and learn from them. Advanced psql setup is even included! Table of contents : I Preface About… About the Book About the Author Acknowledgements About the organisation of the books II Introduction Structured Query Language Some of the Code is Written in SQL A First Use Case Loading the Data Set Application Code and SQL A Word about SQL Injection PostgreSQL protocol: server-side prepared statements Back to Discovering SQL Computing Weekly Changes Software Architecture Why PostgreSQL? The PostgreSQL Documentation Getting Ready to read this Book III Writing Sql Queries Business Logic Every SQL query embeds some business logic Business Logic Applies to Use Cases Correctness Efficiency Stored Procedures - a Data Access API Procedural Code and Stored Procedures Where to Implement Business Logic? A Small Application Readme First Driven Development Loading the Dataset Chinook Database Music Catalog Albums by Artist Top-N Artists by Genre The SQL REPL - An Interactive Setup Intro to psql The psqlrc Setup Transactions and psql Behavior A Reporting Tool Discovering a Schema Interactive Query Editor SQL is Code SQL style guidelines Comments Unit Tests Regression Tests A Closer Look Indexing Strategy Indexing for Constraints Indexing for Queries Cost of Index Maintenance Choosing Queries to Optimize PostgreSQL Index Access Methods Advanced Indexing Adding Indexes An Interview with Yohann Gabory IV SQL Toolbox Get Some Data Structured Query Language Queries, DML, DDL, TCL, DCL Select, From, Where Anatomy of a Select Statement Projection (output): Select Data sources: From Understanding Joins Restrictions: Where Order By, Limit, No Offset Ordering with Order By kNN Ordering and GiST indexes Top-N sorts: Limit No Offset, and how to implement pagination Group By, Having, With, Union All Aggregates (aka Map/Reduce): Group By Aggregates Without a Group By Restrict Selected Groups: Having Grouping Sets Common Table Expressions: With Distinct On Result Sets Operations Understanding Nulls Three-Valued Logic Not Null Constraints Outer Joins Introducing Nulls Using Null in Applications Understanding Window Functions Windows and Frames Partitioning into Different Frames Available Window Functions When to Use Window Functions Understanding Relations and Joins Relations SQL Join Types An Interview with Markus Winand V Data Types Serialization and Deserialization Some Relational Theory Attribute Values, Data Domains and Data Types Consistency and Data Type Behavior PostgreSQL Data Types Boolean Character and Text Server Encoding and Client Encoding Numbers Floating Point Numbers Sequences and the Serial Pseudo Data Type Universally Unique Identifier: UUID Bytea and Bitstring Date/Time and Time Zones Time Intervals Date/Time Processing and Querying Network Address Types Ranges Denormalized Data Types Arrays Composite Types XML JSON Enum PostgreSQL Extensions An interview with Grégoire Hubert VI Data Modeling Object Relational Mapping Tooling for Database Modeling How to Write a Database Model Generating Random Data Modeling Example Normalization Data Structures and Algorithms Normal Forms Database Anomalies Modeling an Address Field Primary Keys Surrogate Keys Foreign Keys Constraints Not Null Constraints Check Constraints and Domains Exclusion Constraints Practical Use Case: Geonames Features Countries Administrative Zoning Geolocation Data Geolocation GiST Indexing A Sampling of Countries Modelization Anti-Patterns Entity Attribute Values Multiple Values per Column UUIDs Denormalization Premature Optimization Functional Dependency Trade-Offs Denormalization with PostgreSQL Materialized Views History Tables and Audit Trails Validity Period as a Range Pre-Computed Values Enumerated Types Multiple Values per Attribute The Spare Matrix Model Partitioning Other Denormalization Tools Denormalize wih Care Not Only SQL Schemaless Design in PostgreSQL Durability Trade-Offs Scaling Out An interview with alvaro Hernandez Tortosa VII Data Manipulation and Concurrency Control Another Small Application Insert, Update, Delete Insert Into Insert Into … Select Update Inserting Some Tweets Delete Tuples and Rows Deleting All the Rows: Truncate Delete but Keep a Few Rows Isolation and Locking Transactions and Isolation About SSI Concurrent Updates and Isolation Modeling for Concurrency Putting Concurrency to the Test Computing and Caching in SQL Views Materialized Views Triggers Transactional Event Driven Processing Trigger and Counters Anti-Pattern Fixing the Behavior Event Triggers Listen and Notify PostgreSQL Notifications PostgreSQL Event Publication System Notifications and Cache Maintenance Limitations of Listen and Notify Listen and Notify Support in Drivers Batch Update, MoMA Collection Updating the Data Concurrency Patterns On Conflict Do Nothing An Interview with Kris Jenkins VIII PostgreSQL Extensions What's a PostgreSQL Extension? Inside PostgreSQL Extensions Installing and Using PostgreSQL Extensions Finding PostgreSQL Extensions A Primer on Authoring PostgreSQL Extensions A Short List of Noteworthy Extensions Auditing Changes with hstore Introduction to hstore Comparing hstores Auditing Changes with a Trigger Testing the Audit Trigger From hstore Back to a Regular Record Last.fm Million Song Dataset Using Trigrams For Typos The pg_trgm PostgreSQL Extension Trigrams, Similarity and Searches Complete and Suggest Song Titles Trigram Indexing Denormalizing Tags with intarray Advanced Tag Indexing Searches User-Defined Tags Made Easy The Most Popular Pub Names A Pub Names Database Normalizing the Data Geolocating the Nearest Pub (k-NN search) Indexing kNN Search How far is the nearest pub? The earthdistance PostgreSQL contrib Pubs and Cities The Most Popular Pub Names by City Geolocation with PostgreSQL Geolocation Data Loading Finding an IP Address in the Ranges Geolocation Metadata Emergency Pub Counting Distinct Users with HyperLogLog HyperLogLog Installing postgresql-hll Counting Unique Tweet Visitors Lossy Unique Count with HLL Getting the Visits into Unique Counts Scheduling Estimates Computations Combining Unique Visitors An Interview with Craig Kerstiens IX Closing Thoughts X Index