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

Python DB API 2.0

PostgreSQL is not the only relational database and of course it is not the only one you can use with Python. There is an API in Python that is designed to unify the way applications work with databases. It is called the Python Database API. By the time this book was written, it already had the second specification, defined in the Python Enhancement Proposal (PEP) 249. The specification of the Python DB API 2.0 is available at https://www.python.org/dev/peps/pep-0249/.

The API defines the following objects that are used when connecting to a database and interacting with it:

  • connection: This implements the logic of connecting to the database server, authenticating and managing transactions. A connection is created by calling the connect() function with a number of parameters that can differ depending on the database and the driver that is used.
  • cursor: This represents a cursor in the database. It is an object that is used to manage the context of execution of a SQL command....
Previous Section
End of Section 2
Next Section