Cover

Copyright

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Table of Contents

Preface

Chapter 1: Advanced SQL

Creating views

Deleting and replacing views

Materialized views

Why materialized views?

Read-only, updatable, and writeable materialized views

Read-only materialized views

Updatable materialized views

Writeable materialized views

Creating cursors

Using cursors

Closing a cursor

Using the GROUP BY clause

Using the HAVING clause

Parameters or arguments

Using the UPDATE operation clauses

Using the LIMIT clause

Using subqueries

Subqueries that return multiple rows

Correlated subqueries

Existence subqueries

Parameters or arguments

Using the Union join

Using the Self join

Using the Outer join

Left outer join

Right outer join

Full outer join

Summary

Chapter 2: Data Manipulation

Conversion between datatypes

Introduction to arrays

Array constructors

String_to_array()

Array_dims( )

ARRAY_AGG()

ARRAY_UPPER()

Array_length()

Array slicing and splicing

UNNESTing arrays to rows

Introduction to JSON

Inserting JSON data in PostgreSQL

Querying JSON

Equality operation

Containment

Key/element existence

Outputting JSON

Using XML in PostgreSQL

Inserting XML data in PostgreSQL

Querying XML data

Composite datatype

Creating composite types in PostgreSQL

Altering composite types in PostgreSQL

Dropping composite types in PostgreSQL

Summary

Chapter 3: Triggers

Introduction to triggers

Adding triggers to PostgreSQL

Modifying triggers in PostgreSQL

Removing a trigger function

Creating a trigger function

Testing the trigger function

Viewing existing triggers

Summary

Chapter 4: Understanding Database Design Concepts

Basic design rules

The ability to solve the problem

The ability to hold the required data

The ability to support relationships

The ability to impose data integrity

The ability to impose data efficiency

The ability to accommodate future changes

Normalization

Anomalies in DBMS

First normal form

Second normal form

Third normal form

Common patterns

Many-to-many relationships

Hierarchy

Recursive relationships

Summary

Chapter 5: Transactions and Locking

Defining transactions

ACID rules

Effect of concurrency on transactions

Transactions and savepoints

Transaction isolation

Implementing isolation levels

Dirty reads

Non-repeatable reads

Phantom reads

ANSI isolation levels

Transaction isolation levels

Changing the isolation level

Using explicit and implicit transactions

Avoiding deadlocks

Explicit locking

Locking rows

Locking tables

Summary

Chapter 6: Indexes and Constraints

Introduction to indexes and constraints

Primary key indexes

Unique indexes

B-tree indexes

Standard indexes

Full text indexes

Partial indexes

Multicolumn indexes

Hash indexes

GIN and GiST indexes

Clustering on an index

Foreign key constraints

Unique constraints

Check constraints

NOT NULL constraints

Exclusion constraints

Summary

Chapter 7: Table Partitioning

Table partitioning

Partition implementation

Partitioning types

List partition

Managing partitions

Adding a new partition

Purging an old partition

Alternate partitioning methods

Method 1

Method 2

Constraint exclusion

Horizontal partitioning

PL/Proxy

Foreign inheritance

Summary

Chapter 8: Query Tuning and Optimization

Query tuning

Hot versus cold cache

Cleaning the cache

pg_buffercache

pg_prewarm

Optimizer settings for cached data

Multiple ways to implement a query

Bad query performance with stale statistics

Optimizer hints

Explain Plan

Generating and reading the Explain Plan

Simple example

More complex example

Query operators

Seq Scan

Index Scan

Sort

Unique

LIMIT

Aggregate

Append

Result

Nested Loop

Merge Join

Hash and Hash Join

Group

Subquery Scan and Subplan

Tid Scan

Materialize

Setop

Summary

Chapter 9: PostgreSQL Extensions and Large Object Support

Creating an extension

Compiling extensions

Database links in PostgreSQL

Using binary large objects

Creating a large object

Importing a large object

Exporting a large object

Writing data to a large object

Server-side functions

Summary

Chapter 10: Using PHP in PostgreSQL

Postgres with PHP

PHP-to-PostgreSQL connections

Dealing with DDLs

DML operations

pg_query_params

pg_insert

Data retrieval

pg_fetch_all

pg_fetch_assoc

pg_fetch_result

Helper functions to deal with data fetching

pg_free_results

pg_num_rows

pg_num_fields

pg_field_name

pg_meta_data

pg_convert

UPDATE

DELETE

COPY

Summary

Chapter 11: Using Java in PostgreSQL

Making database connections to PostgreSQL using Java

Using Java to create a PostgreSQL table

Using Java to insert records into a PostgreSQL table

Using Java to update records into a PostgreSQL table

Using Java to delete records into a PostgreSQL table

Catching exceptions

Using prepared statements

Loading data using COPY

Connection properties

Summary

Index