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

User-defined data types

PostgreSQL provides two methods for implementing user-defined data types through the following commands:

  • CREATE DOMAIN: The CREATE DOMAIN command allows developers to create a user-defined data type with constraints. This helps to make the source code more modular.
  • CREATE TYPE: The CREATE TYPE command is often used to create a composite type, which is useful in procedural languages, and is used as the return data type. Also, one can use the create type to create the ENUM type, which is useful to decrease the number of joins, specifically for lookup tables.

Often, developers tend not to use user-defined data types and use flat tables instead due to a lack of support on the driver side, such as JDBC and ODBC. Nonetheless, in JDBC, the composite data types can be retried as Java objects and parsed manually.

Domain objects, as with other database objects, should have a unique name within the schema scope. The first use case of domains is to use them for common patterns. For...

Previous Section
End of Section 8
Next Section