Learning PostgreSQL电子书

Learning PostgreSQL

Table of Contents

Learning PostgreSQL

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Relational Databases

Database management systems

A brief history

Database categories

The NoSQL databases

The CAP theorem

NoSQL motivation

Key value databases

Columnar databases

Document databases

Graph databases

Relational and object relational databases

ACID properties

The SQL Language

Basic concepts

Relation

Tuple

Attribute

Constraint

Domain integrity constraint

Entity integrity constraint

Referential integrity constraints

Semantic constraints

Relational algebra

The SELECT and PROJECT operations

The RENAME operation

The Set theory operations

The CROSS JOIN (Cartesian product) operation

Data modeling

Data model perspectives

The entity-relation model

Sample application

Entities, attributes, and keys

Mapping ER to Relations

UML class diagrams

Summary

2. PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL history

The advantages of PostgreSQL

Business advantages of PostgreSQL

PostgreSQL user advantages

PostgreSQL applications

Success stories

Forks

PostgreSQL architecture

PostgreSQL abstract architecture

The PostgreSQL community

PostgreSQL capabilities

Replication

Security

Extension

NoSQL capabilities

Foreign data wrapper

Performance

Very rich SQL constructs

Installing PostgreSQL

Installing PostgreSQL on Ubuntu

Client installation

Server installation

Basic server configuration

Installing PostgreSQL on Windows

The PostgreSQL clients

The psql client

Psql advanced settings

PostgreSQL utility tools

Backup and replication

Utilities

PgAdmin III

Summary

3. PostgreSQL Basic Building Blocks

Database coding

Database naming conventions

PostgreSQL identifiers

Documentation

Version control system

PostgreSQL objects hierarchy

Template databases

User databases

Roles

Tablespace

Template procedural languages

Settings

Setting parameters

Setting a context

PostgreSQL high-level object interaction

PostgreSQL database components

Schema

Schema usages

Table

PostgreSQL native data types

Numeric types

Character types

Date and time types

The car web portal database

Summary

4. PostgreSQL Advanced Building Blocks

Views

View synopsis

Views categories

Materialized views

Updatable views

Indexes

Index types

Partial indexes

Indexes on expressions

Unique indexes

Multicolumn indexes

Best practices on indexes

Functions

PostgreSQL native programming languages

Creating a function in the C language

Creating functions in the SQL language

Creating a function in the PL/pgSQL language

PostgreSQL function usages

PostgreSQL function dependency

PostgreSQL function categories

PostgreSQL anonymous functions

PostgreSQL user-defined data types

The PostgreSQL CREATE DOMAIN command

The PostgreSQL CREATE TYPE command

Triggers and rule systems

The PostgreSQL rule system

The PostgreSQL trigger system

Triggers with arguments

Using triggers to make views updatable

Summary

5. SQL Language

SQL fundamentals

SQL lexical structure

Querying the data with the SELECT statement

The structure of the SELECT query

Select-list

SQL expressions

DISTINCT

FROM clause

Selecting from multiple tables

Self-joins

WHERE clause

Comparison operators

Pattern matching

Row and array comparison constructs

Grouping and aggregation

GROUP BY clause

HAVING clause

Ordering and limiting the results

Subqueries

Set operations – UNION, EXCEPT, and INTERSECT

Dealing with NULLs

Changing the data in the database

INSERT statement

UPDATE statement

UPDATE using sub-select

UPDATE using additional tables

DELETE statement

TRUNCATE statement

Summary

6. Advanced Query Writing

Common table expressions

Reusing SQL code with CTE

Recursive and hierarchical queries

Changing data in multiple tables at a time

Window functions

Window definition

The WINDOW clause

Using window functions

Window functions with grouping and aggregation

Advanced SQL

Selecting the first records

Set returning functions

Lateral subqueries

Advanced usage of aggregating functions

Transaction isolation and multiversion concurrency control

Summary

7. Server-Side Programming with PL/pgSQL

Introduction

SQL language and PL/pgSQL – a comparison

PostgreSQL function parameters

Function authorization-related parameters

Function planner-related parameters

Function configuration-related parameters

The PostgreSQL PL/pgSQL control statements

Declaration statements

Assignment statements

Conditional statements

Iteration

The loop statement

The while loop statement

The for loop statement

Returning from the function

Returning void

Returning a single row

Returning multiple rows

Function predefined variables

Exception handling

Dynamic SQL

Executing DDL statements in dynamic SQL

Executing DML statements in dynamic SQL

Dynamic SQL and the caching effect

Recommended practices when using dynamic SQL

Summary

8. PostgreSQL Security

Authentication in PostgreSQL

PostgreSQL pg_hba.conf

Listen addresses

Authentication best practices

PostgreSQL default access privileges

Role system and proxy authentication

PostgreSQL security levels

Database security level

Schema security level

Table-level security

Column-level security

Row-level security

Encrypting data

PostgreSQL role password encryption

pgcrypto

One-way encryption

Two-way encryption

Summary

9. The PostgreSQL System Catalog and System Administration Functions

The system catalog

Getting the database cluster and client tools version

Getting ready

How to do it…

There's more…

Terminating and canceling user sessions

Getting ready

How to do it…

How it works…

There's more…

Setting and getting database cluster settings

Getting ready

How to do it…

There's more…

Getting the database and database object size

Getting ready

How to do it…

There's more…

Cleaning up the database

Getting ready

How to do it…

There's more…

Cleaning up data in the database

Getting ready

How to do it…

There's more…

Managing database locks

Adding missing indexes on foreign keys and altering the default statistic

Getting ready

How to do it…

Getting the views dependency tree

Getting ready

How to do it…

There's more…

Summary

10. Optimizing Database Performance

PostgreSQL configuration tuning

Maximum number of connections

Memory settings

Hard disk settings

Planner-related settings

Benchmarking is your friend

Tuning PostgreSQL queries

The EXPLAIN command and execution plan

Detecting problems in query plans

Common mistakes in writing queries

Unnecessary operations

Misplaced indexes

Unnecessary table or index scans

Using correlated nested queries

Using CTE when not mandatory

Using the PL/pgSQL procedural language consideration

Cross column correlation

Table partitioning

Constraint exclusion limitations

Summary

11. Beyond Conventional Data types

PostgreSQL arrays

Common functions of arrays and their operators

Modifying and accessing arrays

Indexing arrays in PostgreSQL

Hash store

Modifying and accessing an hstore

Indexing an hstore in PostgreSQL

The PostgreSQL JSON data type

JSON and XML

The JSON data type

Modifying and accessing JSON types

Indexing a JSON data type

The PostgreSQL RESTful API with JSON

A PostgreSQL full text search

The tsquery and tsvector data types

The tsvector data type

The tsquery data type

Pattern matching

Full text search indexing

Summary

12. Testing

Unit testing

Unit testing in databases

Unit test frameworks

Schema difference

The interfaces test

Data difference

PostgreSQL benchmarks

Summary

13. PostgreSQL JDBC

Introduction to JDBC

Connecting to a PostgreSQL database

Installing the driver

Initializing the driver

Obtaining a connection

Error handling

SQLWarnings

Issuing a query and processing the results

Static statements

PreparedStatements

Using a ResultSet

Navigating through a ResultSet

Reading row data

Handling null values

Scrollable and updateable ResultSets

Navigating through a ResultSet

Changing the data in a ResultSet

Using cursors

Getting information about the table structure

Function handling

Calling a stored function

Getting a ResultSet from a stored function

Getting a ResultSet from a function returning SETOF

Getting a ResultSet from a function returning a refcursor

Design considerations

Summary

14. PostgreSQL and Hibernate

Introduction to ORM and Hibernate

Hibernate overview and architecture

Installation and configuration

Installation of Hibernate

Configuring Hibernate

Getting a session from the SessionFactory

Mapping classes to tables

Creating an entity class

Creating a mapping file

Using annotation-based mapping

Working with entities

States of an entity

Making a new entity persistent

Loading an entity from the database

Loading a list of entries

Named queries

Creating dynamic queries

Modifying entities

Deleting entities

Using association mapping

One-to-many and many-to-one mappings

One-to-one mapping and component mapping

Many-to-many mapping

Fetching strategies

Configuring the fetch type

Configuring the fetch mode

Tuning the performance of Hibernate

Using caching

Using connection pools

Dealing with partitioned tables

Summary

Index