You are previewing PostgreSQL Developer's Guide.
O'Reilly logo
PostgreSQL Developer's Guide

Book Description

Design, develop, and implement streamlined databases with PostgreSQL

In Detail

PostgreSQL is an enterprise-level database that competes among proprietary database vendors, owing to its remarkable feature set and reliability without the expensive licensing overhead.

This book is a comprehensive and pragmatic guide to developing databases in PostgreSQL. Beginning with a refresher of basic SQL skills, you will gradually be exposed to advanced concepts, such as learning how to program in native PostgreSQL procedural languages, implementing triggers, custom functions, and views. You will learn database optimization techniques such as query optimization and indexing while exploring extensive code examples. Lastly, you will explore foreign data wrappers, implementing extensibility, and improving maintainability.

What You Will Learn

  • Refresh your knowledge of SQL with PostgreSQL-supported DDL and DML

  • Explore native PostgreSQL procedural languages and learn how to write custom functions

  • Leverage the power of indexes for optimal database efficiency

  • Unfold tricky trigger concepts and implement them in native procedural languages

  • Discover how to implement views, partitioned tables, and window functions in PostgreSQL

  • Design efficient queries with query optimization and query analysis

  • Communicate with PostgreSQL servers using foreign data wrappers such as LibPQ and ECPG

  • Extend database powers using foreign data wrappers and achieve maintainability via PostgreSQL extensions

  • Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

    Table of Contents

    1. PostgreSQL Developer's Guide
      1. Table of Contents
      2. PostgreSQL Developer's Guide
      3. Credits
      4. About the Authors
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers, and more
          1. Why subscribe?
          2. Free access for Packt account holders
      7. 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. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Getting Started with PostgreSQL
        1. Writing queries using psql
        2. Creating, altering, and truncating a table
          1. Creating tables
          2. Altering tables
          3. Truncating tables
        3. Inserting, updating, and deleting data from tables
          1. Inserting data
          2. Updating data
          3. Deleting data
        4. PostgreSQL's supported data types
        5. PostgreSQL's operators and usage
          1. Logical operators
          2. Comparison operators
          3. Mathematical operators
        6. Constraints in PostgreSQL
          1. Unique constraints
          2. Not-null constraints
          3. Exclusion constraints
          4. Primary key constraints
          5. Foreign key constraints
          6. Check constraints
        7. Privileges in PostgreSQL
        8. Summary
      9. 2. The Procedural Language
        1. Why should you use PL/pgSQL?
        2. Installing PL/pgSQL
        3. Understanding the structure of PL/pgSQL
        4. Using comments in PL/pgSQL
        5. Declaring variables in PL/pgSQL
        6. Declaring function parameters
        7. Declaring the %TYPE attribute
        8. Declaring the row-type and record type variables
        9. Statements and expressions
          1. Using statements
          2. The assignment statement
          3. The call/return function
          4. The RETURN expression
          5. Exception handling statements
          6. Compound statements
          7. Expressions
        10. Control structures
          1. Conditional statements
            1. IF-THEN
            2. IF-THEN-ELSE
            3. IF-THEN-ELSIF
            4. Simple CASE
            5. Searched CASE
          2. Loops
            1. The simple loop
            2. The WHILE loop
            3. The FOR loop
        11. Exception handling
        12. Native support for other procedural languages
          1. PL/Python
            1. Installing PL/Python
            2. Writing functions in PL/Python
            3. Handling arguments with PL/Python
            4. Accessing the database with PL/Python
          2. PL/Perl
            1. Installing PL/Perl
            2. Writing functions in PL/Perl
            3. Handling arguments with PL/Perl
            4. Accessing the database with PL/Perl
          3. PL/Tcl
            1. Installing PL/Tcl
            2. Writing functions in PL/Tcl
            3. Handling arguments with PL/Tcl
            4. Accessing the database with PL/Tcl
        13. Summary
      10. 3. Working with Indexes
        1. What is an index?
        2. How to create an index
        3. How to drop an index
        4. Types of indexes
          1. The single-column index
          2. The multicolumn index
          3. The partial index
          4. The unique index
          5. Explicitly creating an index using the CREATE INDEX command
          6. The expression index
          7. The implicit index
          8. The concurrent index
        5. Index types
          1. The B-tree index
          2. The hash index
          3. The GiST index
          4. The GIN index
        6. Index bloating
          1. Dump and restore
          2. VACUUM
          3. CLUSTER
        7. Reindexing
        8. Points to ponder
        9. Summary
      11. 4. Triggers, Rules, and Views
        1. Understanding triggers
          1. Tricky triggers
          2. Creating triggers and trigger functions with PL/pgSQL
            1. Creating triggers on views
            2. Creating triggers in PL/Perl
            3. Creating triggers in PL/Tcl
            4. Creating triggers in PL/Python
        2. PostgreSQL rules
          1. Rules versus triggers – creating updateable views with rules
        3. Understanding views
          1. Materialized views
            1. Creating materialized views
        4. Summary
      12. 5. Window Functions
        1. Understanding window functions
          1. The cume_dist() function
          2. The row_number() function
          3. The rank() function
          4. The dense_rank() function
          5. The percent_rank() function
          6. The first_value() function
          7. The last_value() function
          8. The nth_value() function
          9. The ntile() function
          10. The lag() function
          11. The lead() function
        2. Summary
      13. 6. Partitioning
        1. Creating a table partition
        2. Partitioning in PostgreSQL
          1. Range partition
            1. Creating the master table
            2. Creating a range partition table
            3. Creating an index on child tables
            4. Creating a trigger on the master table
            5. Enabling the constraint exclusion
          2. Performing DML operations on a partition table
          3. Handling the UPDATE and DELETE statements on a partition table
          4. List partition
        3. Summary
      14. 7. Query Optimization
        1. What is EXPLAIN?
          1. Working with EXPLAIN ANALYZE
          2. EXPLAIN VERBOSE
          3. EXPLAIN pretty formats
        2. Cost parameters
        3. Sequential scans
        4. Index scans
          1. Index-only scans
        5. Bitmap scans
        6. Common Table Expressions
        7. Joins
          1. Nested loop joins
          2. Merge joins
          3. Hash joins
          4. Hash semi and anti joins
          5. Join ordering
        8. Query planning
          1. Window functions
        9. Hints
        10. Configuration parameters to optimize queries
        11. Summary
      15. 8. Dealing with Large Objects
        1. Why large objects?
        2. PostgreSQL large objects
          1. Implementing large objects
          2. Large objects in action
        3. Manipulating large objects through the libpq client interface library
          1. lo_create
          2. lo_import
          3. lo_export
          4. lo_open
          5. lo_write
          6. lo_read
          7. lo_close
          8. lo_unlink
        4. Summary
      16. 9. Communicating with PostgreSQL Using LibPQ
        1. Connecting and disconnecting to PostgreSQL
          1. Using PQconnectdb
          2. Using PQconnectdbParams
          3. Using PQsetdbLogin
          4. Using PQsetdb
          5. Using PQfinish
          6. Using PQreset
        2. Establishing a connection asynchronously
          1. Using PQconnectStartParams
          2. Using PQconnectStart
          3. Using PQconnectPoll
          4. Using PQresetStart
          5. Using PQresetPoll
        3. Connection-supporting functions
          1. Using PQconninfoParse
          2. Using PQpingParams
        4. Executing a query
          1. Using PQexec
          2. Using PQexecParams
        5. Executing prepared statements
          1. Using PQprepare
          2. Using PQexecPrepared
        6. Retrieving data
          1. Using PQntuples
          2. Using PQnfields
          3. Using PQfname
          4. Using PQfnumber
          5. Using PQftable
          6. Using PQftablecol
          7. Using PQfformat
          8. Using PQftype
          9. Using PQfmod
          10. Using PQfsize
          11. Using PQbinaryTuples
          12. Using PQgetvalue
          13. Using PQgetisnull
          14. Using PQgetlength
          15. Using PQnparams
          16. Using PQparamtype
        7. Using status functions
          1. Using PQresultStatus
          2. Using PQresStatus
        8. Summary
      17. 10. Embedded SQL in C – ECPG
        1. Writing ECPG programs
        2. Compiling an ECPG program
          1. ECPG DECLARE sections
          2. Connection using ECPG
          3. Disconnecting using ECPG
          4. Selecting a connection
        3. Running SQL commands
          1. Using host variables
          2. Passing values to SQL
          3. Getting values from SQL
            1. Dynamic SQL
        4. Error handling
          1. How to set an error callback
            1. Conditions
            2. Actions
          2. Using sqlca for error handling
        5. Summary
      18. 11. Foreign Data Wrapper
        1. Creating foreign data wrappers
        2. The basic components of foreign data wrappers
          1. The C file
          2. Makefile to compile the foreign data wrapper
            1. A SQL file to map SQL functions to C functions
          3. The control file to manage version and module path
        3. Loading foreign data wrappers
          1. Creating a server
          2. Creating user mapping
          3. Creating a foreign table
        4. Using foreign data wrappers
          1. Working with postgres_fdw
          2. Working with file_fdw
        5. Summary
      19. 12. Extensions
        1. Features of an extension
        2. Creating extensions
        3. Altering extensions
        4. Dropping extensions
        5. How to check available extensions
        6. Additional supplied extensions
          1. The adminpack extension
          2. The chkpass extension
          3. The citext extension
          4. The cube extension
          5. The dblink extension
          6. The file_fdw extension
        7. Other available extensions
        8. Summary
      20. Index