PostgreSQL Development Essentials

PostgreSQL Development Essentials

by Manpreet Kaur, Baji Shaik
Released September 2016
Publisher(s): Packt Publishing
ISBN: 9781783989003

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

Table of contents

  1. PostgreSQL Development Essentials
    1. PostgreSQL Development Essentials
    2. Credits
    3. About the Authors
    4. About the Reviewers
    5. www.PacktPub.com
      1. eBooks, discount offers, and more
        1. Why subscribe?
    6. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Errata
        2. Piracy
        3. Questions
    7. 1. Advanced SQL
      1. Creating views
        1. Deleting and replacing views
      2. Materialized views
        1. Why materialized views?
          1. Read-only, updatable, and writeable materialized views
          2. Read-only materialized views
          3. Updatable materialized views
          4. Writeable materialized views
      3. Creating cursors
        1. Using cursors
        2. Closing a cursor
      4. Using the GROUP BY clause
      5. Using the HAVING clause
        1. Parameters or arguments
      6. Using the UPDATE operation clauses
      7. Using the LIMIT clause
      8. Using subqueries
      9. Subqueries that return multiple rows
        1. Correlated subqueries
        2. Existence subqueries
        3. Parameters or arguments
      10. Using the Union join
      11. Using the Self join
      12. Using the Outer join
        1. Left outer join
        2. Right outer join
        3. Full outer join
      13. Summary
    8. 2. Data Manipulation
      1. Conversion between datatypes
      2. Introduction to arrays
        1. Array constructors
          1. String_to_array()
          2. Array_dims( )
          3. ARRAY_AGG()
          4. ARRAY_UPPER()
          5. Array_length()
        2. Array slicing and splicing
        3. UNNESTing arrays to rows
        4. Introduction to JSON
          1. Inserting JSON data in PostgreSQL
          2. Querying JSON
            1. Equality operation
            2. Containment
            3. Key/element existence
            4. Outputting JSON
        5. Using XML in PostgreSQL
          1. Inserting XML data in PostgreSQL
        6. Querying XML data
        7. Composite datatype
          1. Creating composite types in PostgreSQL
          2. Altering composite types in PostgreSQL
          3. Dropping composite types in PostgreSQL
      3. Summary
    9. 3. Triggers
      1. Introduction to triggers
        1. Adding triggers to PostgreSQL
        2. Modifying triggers in PostgreSQL
        3. Removing a trigger function
      2. Creating a trigger function
        1. Testing the trigger function
        2. Viewing existing triggers
      3. Summary
    10. 4. Understanding Database Design Concepts
      1. Basic design rules
        1. The ability to solve the problem
        2. The ability to hold the required data
        3. The ability to support relationships
        4. The ability to impose data integrity
        5. The ability to impose data efficiency
        6. The ability to accommodate future changes
      2. Normalization
      3. Anomalies in DBMS
        1. First normal form
        2. Second normal form
        3. Third normal form
        4. Common patterns
          1. Many-to-many relationships
          2. Hierarchy
          3. Recursive relationships
      4. Summary
    11. 5. Transactions and Locking
      1. Defining transactions
        1. ACID rules
        2. Effect of concurrency on transactions
        3. Transactions and savepoints
        4. Transaction isolation
          1. Implementing isolation levels
            1. Dirty reads
            2. Non-repeatable reads
            3. Phantom reads
        5. ANSI isolation levels
          1. Transaction isolation levels
          2. Changing the isolation level
          3. Using explicit and implicit transactions
        6. Avoiding deadlocks
        7. Explicit locking
          1. Locking rows
          2. Locking tables
      2. Summary
    12. 6. Indexes and Constraints
      1. Introduction to indexes and constraints
        1. Primary key indexes
        2. Unique indexes
        3. B-tree indexes
        4. Standard indexes
        5. Full text indexes
        6. Partial indexes
        7. Multicolumn indexes
        8. Hash indexes
        9. GIN and GiST indexes
      2. Clustering on an index
        1. Foreign key constraints
        2. Unique constraints
        3. Check constraints
        4. NOT NULL constraints
      3. Exclusion constraints
      4. Summary
    13. 7. Table Partitioning
      1. Table partitioning
        1. Partition implementation
        2. Partitioning types
          1. List partition
        3. Managing partitions
        4. Adding a new partition
        5. Purging an old partition
        6. Alternate partitioning methods
          1. Method 1
          2. Method 2
        7. Constraint exclusion
          1. Horizontal partitioning
          2. PL/Proxy
          3. Foreign inheritance
      2. Summary
    14. 8. Query Tuning and Optimization
      1. Query tuning
        1. Hot versus cold cache
        2. Cleaning the cache
          1. pg_buffercache
          2. pg_prewarm
      2. Optimizer settings for cached data
      3. Multiple ways to implement a query
      4. Bad query performance with stale statistics
        1. Optimizer hints
      5. Explain Plan
        1. Generating and reading the Explain Plan
          1. Simple example
          2. More complex example
      6. Query operators
        1. Seq Scan
        2. Index Scan
        3. Sort
        4. Unique
        5. LIMIT
        6. Aggregate
        7. Append
        8. Result
        9. Nested Loop
        10. Merge Join
        11. Hash and Hash Join
        12. Group
        13. Subquery Scan and Subplan
        14. Tid Scan
        15. Materialize
        16. Setop
      7. Summary
    15. 9. PostgreSQL Extensions and Large Object Support
      1. Creating an extension
        1. Compiling extensions
        2. Database links in PostgreSQL
      2. Using binary large objects
        1. Creating a large object
        2. Importing a large object
        3. Exporting a large object
        4. Writing data to a large object
        5. Server-side functions
      3. Summary
    16. 10. Using PHP in PostgreSQL
      1. Postgres with PHP
      2. PHP-to-PostgreSQL connections
      3. Dealing with DDLs
      4. DML operations
        1. pg_query_params
        2. pg_insert
      5. Data retrieval
        1. pg_fetch_all
        2. pg_fetch_assoc
        3. pg_fetch_result
      6. Helper functions to deal with data fetching
        1. pg_free_results
        2. pg_num_rows
        3. pg_num_fields
        4. pg_field_name
        5. pg_meta_data
        6. pg_convert
        7. UPDATE
        8. DELETE
        9. COPY
      7. Summary
    17. 11. Using Java in PostgreSQL
      1. Making database connections to PostgreSQL using Java
        1. Using Java to create a PostgreSQL table
        2. Using Java to insert records into a PostgreSQL table
        3. Using Java to update records into a PostgreSQL table
        4. Using Java to delete records into a PostgreSQL table
        5. Catching exceptions
      2. Using prepared statements
        1. Loading data using COPY
      3. Connection properties
      4. Summary

Product information

  • Title: PostgreSQL Development Essentials
  • Author(s): Manpreet Kaur, Baji Shaik
  • Release date: September 2016
  • Publisher(s): Packt Publishing
  • ISBN: 9781783989003