Book Image

Learning PostgreSQL 11 - Third Edition

By : Salahaldin Juba, Andrey Volkov
Book Image

Learning PostgreSQL 11 - Third Edition

By: Salahaldin Juba, Andrey Volkov

Overview of this book

PostgreSQL is one of the most popular open source database management systems in the world, and it supports advanced features included in SQL standards. This book will familiarize you with the latest features in PostgreSQL 11, and get you up and running with building efficient PostgreSQL database solutions from scratch. Learning PostgreSQL, 11 begins by covering the concepts of relational databases and their core principles. You’ll explore the Data Definition Language (DDL) and commonly used DDL commands supported by ANSI SQL. You’ll also learn how to create tables, define integrity constraints, build indexes, and set up views and other schema objects. As you advance, you’ll come to understand Data Manipulation Language (DML) and server-side programming capabilities using PL/pgSQL, giving you a robust background to develop, tune, test, and troubleshoot your database application. The book will guide you in exploring NoSQL capabilities and connecting to your database to manipulate data objects. You’ll get to grips with using data warehousing in analytical solutions and reports, and scaling the database for high availability and performance. By the end of this book, you’ll have gained a thorough understanding of PostgreSQL 11 and developed the necessary skills to build efficient database solutions.

Related Content you might be interested in

Current Title:

Small book image
Learning PostgreSQL 11 - Third Edition
Salahaldin Juba | Andrey Volkov
Jan, 2019 | 556 pages
Small book image
PostgreSQL 11 Administration Cookbook
Simon Riggs | Gianni Ciolli | Sudheer Kumar Meesala
May, 2019 | 600 pages
Small book image
PostgreSQL 16 Administration Cookbook
Boris Mejias | Simon Riggs | Gianni Ciolli | Vibhor Kumar | Dimitrios Angelakos
Dec, 2023 | 636 pages
Small book image
Learn PostgreSQL
Luca Ferrari | Enrico Pirozzi
Oct, 2020 | 650 pages
Table of Contents (18 chapters)
Free Chapter
1
Relational Databases
Relational Databases
Database management systems
Relational and object-relational databases
Relational algebra
Data modeling
Summary
Questions
2
PostgreSQL in Action
PostgreSQL in Action
An overview of PostgreSQL
PostgreSQL capabilities
Installing PostgreSQL
Summary
Questions
3
PostgreSQL Basic Building Blocks
PostgreSQL Basic Building Blocks
Database coding
PostgreSQL object hierarchy
PostgreSQL database components
PostgreSQL native data types
The car web portal database
Summary
Questions
4
PostgreSQL Advanced Building Blocks
PostgreSQL Advanced Building Blocks
Views
Indexes
Functions
User-defined data types
Trigger and rule systems
Summary
Questions
5
SQL Language
SQL Language
SQL fundamentals
Querying data with SELECT statements
Changing the data in a database
Summary
Questions
6
Advanced Query Writing
Advanced Query Writing
Common table expressions
Window functions
Advanced SQL techniques
Summary
Questions
7
Server-Side Programming with PL/pgSQL
Server-Side Programming with PL/pgSQL
The SQL language and PL/pgSQL – a comparison
Functions and procedures – a comparison
PostgreSQL function parameters
The PostgreSQL PL/pgSQL control statements
Function predefined variables
Exception handling
Dynamic SQL
Summary
Questions
8
OLAP and Data Warehousing
OLAP and Data Warehousing
OLTP and OLAP
Online analytical processing
Partitioning
Parallel query
Index-only scans
Summary
Questions
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
Questions
10
Transactions and Concurrency Control
Transactions and Concurrency Control
Transactions
Explicit locking
Transaction in functions and procedures
Summary
Questions
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
Questions
12
The PostgreSQL Catalog
The PostgreSQL Catalog
The system catalog
System catalog for administrators
Cleaning up the database
Tuning for performance
Selective dump
Summary
Questions
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
Questions
14
Testing
Testing
Technical requirements
Unit testing
Database-abstraction interfaces
Summary
Questions
15
Using PostgreSQL in Python Applications
Using PostgreSQL in Python Applications
Technical requirements
Python DB-API 2.0
Low-level database access with Psycopg 2
Alternative drivers for PostgreSQL
Object-relational mapping with SQLAlchemy
Summary
Questions
16
Scalability
Scalability
Technical requirements
The problem of scalability and the CAP theorem
Data replication in PostgreSQL
Building a scalable PostgreSQL solution
Summary
Questions
Further reading
17
What's Next?
What's Next?
Fundamentals
Working with PostgreSQL software
Working with data in PostgreSQL databases
Summary
18
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

User-defined data types

PostgreSQL provides two methods to implement user-defined data types, via 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, we 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 decide not to use user-defined data types and to 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...

Previous Section
End of Section 5
Next Section