Sign In Start Free Trial

Book Overview & Buying
Table Of Contents
Feedback & Rating

Learning PostgreSQL 11

By : Christopher Travers, Volkov

2.7 (6)

Learning PostgreSQL 11

2.7 (6)

By: Christopher Travers, 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.

Free Chapter

Relational Databases

Relational Databases

Database management systems

Relational and object-relational databases

Relational algebra

Data modeling

Summary

Questions

PostgreSQL in Action

PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL capabilities

Installing PostgreSQL

Summary

Questions

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

PostgreSQL Advanced Building Blocks

PostgreSQL Advanced Building Blocks

Views

Indexes

Functions

User-defined data types

Trigger and rule systems

Summary

Questions

SQL Language

SQL Language

SQL fundamentals

Querying data with SELECT statements

Changing the data in a database

Summary

Questions

Advanced Query Writing

Advanced Query Writing

Common table expressions

Window functions

Advanced SQL techniques

Summary

Questions

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

OLAP and Data Warehousing

OLAP and Data Warehousing

OLTP and OLAP

Online analytical processing

Partitioning

Parallel query

Index-only scans

Summary

Questions

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

Transactions and Concurrency Control

Transactions and Concurrency Control

Transactions

Explicit locking

Transaction in functions and procedures

Summary

Questions

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

The PostgreSQL Catalog

The PostgreSQL Catalog

The system catalog

System catalog for administrators

Cleaning up the database

Tuning for performance

Selective dump

Summary

Questions

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

Testing

Testing

Technical requirements

Unit testing

Database-abstraction interfaces

Summary

Questions

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

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

What's Next?

What's Next?

Fundamentals

Working with PostgreSQL software

Working with data in PostgreSQL databases

Summary

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

2.7 (6)

5 star

16.7%

4 star

16.7%

3 star

0

2 star

50%

1 star

16.7%

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...

Search

Your notes and bookmarks