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
Luca Ferrari | Enrico Pirozzi
Oct, 2020 | 650 pages
Small book image
PostgreSQL 14 Administration Cookbook
Simon Riggs | Gianni Ciolli
Mar, 2022 | 608 pages
Small book image
PostgreSQL 16 Administration Cookbook
Boris Mejias | Simon Riggs | Gianni Ciolli | Vibhor Kumar | Dimitrios Angelakos
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

Advanced SQL techniques

In the following section, some other advanced SQL techniques will be introduced:

  • The DISTINCT ON clause, which helps in finding the first records in groups
  • Selecting sample data from a big table
  • The set returning functions, which are functions that return relations
  • LATERAL joins, which allow subqueries to reference each other
  • Advanced grouping techniques that can be used in generating reports
  • Some special aggregating functions

Selecting the first records

Quite often, it is necessary to find the first records based on some criteria. For example, let's take the car_portal database; suppose it is required to find the first advertisement for each car_id in the advertisement table.

Grouping can help in this case. It requires a subquery to implement the logic:

SELECT advertisement_id, advertisement_date, adv.car_id, seller_account_id
 FROM car_portal_app.advertisement adv
 INNER JOIN
 (SELECT car_id, min(advertisement_date) min_date FROM car_portal_app.advertisement GROUP BY car_id...
Previous Section
End of Section 4
Next Section