PostgreSQL Development Essentials

图书信息

作者	Manpreet Kaur,Baji Shaik,
出版社	Packt Publishing
ISBN	9781783989010
出版时间	2016-09-01
字数	220.0万
分类	Packt Publishing,进口书,外文原版书,电脑,网络

读书简介

Develop programmatic functions to create powerful database applications About This Book Write complex SQL queries and design a robust database design that fits your application's need Improve database performance by indexing, partitioning tables, and query optimizing A comprehensive guide covering the advanced PostgreSQL concepts without any hassle Who This Book Is For If you are a PostgreSQL developer with a basic knowledge of PostgreSQL development and you’re want deeper knowledge to develop applications, then this book is for you. As this book does not cover basic installation and configurations, you should have PostgreSQL installed on your machine as a prerequisite. What You Will Learn Write more complex queries with advanced SQL queries Design a database that works with the application exactly the way you want Make the database work in extreme conditions by tuning, optimizing, partitioning, and indexing Develop applications in other programming languages such as Java and PHP Use extensions to get extra benefits in terms of functionality and performance Build an application that does not get locked by data manipulation Explore in-built db functions and data type conversions In Detail PostgreSQL is the most advanced open source database in the world. It is easy to install, configure, and maintain by following the documentation; however, it’s difficult to develop applications using programming languages and design databases accordingly. This book is what you need to get the most out of PostgreSQL You will begin with advanced SQL topics such as views, materialized views, and cursors, and learn about performing data type conversions. You will then perform trigger operations and use trigger functions in PostgreSQL. Next we walk through data modeling, normalization concepts, and the effect of transactions and locking on the database. The next half of the book covers the types of indexes, constrains, and the concepts of table partitioning, as well as the different mechanisms and approaches available to write efficient queries or code. Later, we explore PostgreSQL Extensions and Large Object Support in PostgreSQL. Finally, you will perform database operations in PostgreSQL using PHP and Java. By the end of this book, you will have mastered all the aspects of PostgreSQL development. You will be able to build efficient enterprise-grade applications with PostgreSQL by making use of these concepts Style and approach Every chapter follows a step by step approach that first explains the concept , then shows you how to execute it practically so that you can implement them in your application.

目录

PostgreSQL Development Essentials

PostgreSQL Development Essentials

Credits

About the Authors

About the Reviewers

www.PacktPub.com

eBooks, discount offers, and more

Why subscribe?

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

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

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

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

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

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

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

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

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

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

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

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