Book Image

Learning PostgreSQL 10 - Second Edition

Book Image

Learning PostgreSQL 10 - Second Edition

Overview of this book

PostgreSQL is one of the most popular open source databases in the world, supporting the most advanced features included in SQL standards. This book will familiarize you with the latest features released in PostgreSQL 10. We’ll start with a thorough introduction to PostgreSQL and the new features introduced in PostgreSQL 10. We’ll cover the Data Definition Language (DDL) with an emphasis on PostgreSQL, and the common DDL commands supported by ANSI SQL. You’ll learn to create tables, define integrity constraints, build indexes, and set up views and other schema objects. Moving on, we’ll cover the concepts of Data Manipulation Language (DML) and PostgreSQL server-side programming capabilities using PL/pgSQL. We’ll also explore the NoSQL capabilities of PostgreSQL and connect to your PostgreSQL database to manipulate data objects. By the end of this book, you’ll have a thorough understanding of the basics of PostgreSQL 10 and will have the necessary skills to build efficient database solutions.

Related Content you might be interested in

Current Title:

Small book image
Learning PostgreSQL 10 - Second Edition
Dec, 2017 | 488 pages
Small book image
Learn PostgreSQL
Enrico Pirozzi | Luca Ferrari
Oct, 2020 | 650 pages
Small book image
PostgreSQL 14 Administration Cookbook
Gianni Ciolli | Simon Riggs
Mar, 2022 | 608 pages
Small book image
PostgreSQL 16 Administration Cookbook
Vibhor Kumar | Dimitrios Angelakos | Gianni Ciolli | Boris Mejias | Simon Riggs
Dec, 2023 | 636 pages
Table of Contents (23 chapters)
Title Page
Title Page
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Customer Feedback
Customer Feedback
Preface
Preface
Free Chapter
1
Relational Databases
Relational Databases
Database management systems
Relational and object relational databases
Relational algebra
Data modeling
Summary
2
PostgreSQL in Action
PostgreSQL in Action
An overview of PostgreSQL
PostgreSQL capabilities
Installing PostgreSQL
Summary
3
PostgreSQL Basic Building Blocks
PostgreSQL Basic Building Blocks
Database coding
PostgreSQL objects hierarchy
PostgreSQL database components
The car web portal database
Summary
4
PostgreSQL Advanced Building Blocks
PostgreSQL Advanced Building Blocks
Views
View synopsis
View categories
Updatable views
Indexes
Functions
User-defined data types
Triggers and rule systems
Summary
5
SQL Language
SQL Language
SQL fundamentals
Querying data with SELECT statements
Changing the data in the database
Summary
6
Advanced Query Writing
Advanced Query Writing
Common table expressions
Window functions
Advanced SQL techniques
Summary
7
Server-Side Programming with PL/pgSQL
Server-Side Programming with PL/pgSQL
SQL language and PL/pgSQL – a comparison
PostgreSQL function parameters
The PostgreSQL PL/pgSQL control statements
Function predefined variables
Exception handling
Dynamic SQL
Summary
8
OLAP and Data Warehousing
OLAP and Data Warehousing
Online analytical processing
Partitioning
Parallel query
Index-only scans
Summary
9
Beyond Conventional Data Types
Beyond Conventional Data Types
Arrays
The hash store data structure
The JSON data structure
A PostgreSQL full text search
Summary
10
Transactions and Concurrency Control
Transactions and Concurrency Control
Transactions
Explicit locking
Summary
11
PostgreSQL Security
PostgreSQL Security
Authentication in PostgreSQL
Authentication best practices
PostgreSQL default access privileges
Role system and proxy authentication
PostgreSQL security levels
Encrypting data
Summary
12
The PostgreSQL Catalog
The PostgreSQL Catalog
The system catalog
System catalog for administrators
Cleaning up the database
Tuning for performance 
Selective dump
Summary 
13
Optimizing Database Performance
Optimizing Database Performance
PostgreSQL configuration tuning
Tuning performance for write
Tuning performance for read 
Detecting problems in query plans
Common mistakes in writing queries
Table partitioning
Query rewriting
Summary
14
Testing
Testing
Unit testing
Database abstraction interfaces
Summary
15
Using PostgreSQL in Python Applications
Using PostgreSQL in Python Applications
Python DB API 2.0
Low-level database access with psycopg2
Alternative drivers for PostgreSQL
Object relational mapping with SQLAlchemy
Summary
16
Scalability
Scalability
The problem of scalability and the CAP theorem
Data replication in PostgreSQL
Using replication to scale PostgreSQL
Summary

Chapter 6. Advanced Query Writing

In this chapter, we will discuss some more advanced SQL features supported by PostgreSQL that were not covered in the previous chapters. Some query writing techniques will also be described.

In this chapter, we will cover the following topics:

  • Common table expressions
  • Window functions
  • Advanced SQL techniques

The same sample database car_portal is used in the code examples here. It is recommended though to recreate the sample database in order to get the same results as shown in the code examples. The scripts to create the database and fill it with data (schema.sql and data.sql) can be found in the attached code bundle for this chapter. All the code examples of this chapter can be found in the examples.sql file.

Previous Section
End of Section 1
Next Section