Learning PostgreSQL

Learning PostgreSQL

by Salahaldin Juba, Achim Vannahme, Andrey Volkov
Released November 2015
Publisher(s): Packt Publishing
ISBN: 9781783989188

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

Create, develop and manage relational databases in real world applications using PostgreSQL

About This Book

  • Learn about the PostgreSQL development life cycle including its testing and refactoring
  • Build productive database solutions and use them in Java applications
  • A comprehensive guide to learn about SQL, PostgreSQL procedural language and PL/pgSQL

Who This Book Is For

If you are a student, database developer or an administrator, interested in developing and maintaining a PostgreSQL database, then this book is for you. No knowledge of database programming or administration is necessary.

What You Will Learn

  • Learn concepts of data modelling and relation algebra
  • Install and set up PostgreSQL database server and client software
  • Implement data structures in PostgreSQL
  • Manipulate data in the database using SQL
  • Implement data processing logic in the database with stored functions, triggers and views
  • Test database solutions and assess the performance
  • Integrate database with Java applications
  • Detailed knowledge of the main PostgreSQL building objects, most used extensions
  • Practice database development life cycle including analysis, modelling, (documentation), testing, bug fixes and refactoring

In Detail

PostgreSQL is one of the most powerful and easy to use database management systems. It has strong support from the community and is being actively developed with a new release every year. PostgreSQL supports the most advanced features included in SQL standards. Also it provides NoSQL capabilities, and very rich data types and extensions. All that makes PostgreSQL a very attractive solution in various kinds of software systems.

The book starts with the introduction of relational databases with PostegreSQL. It then moves on to covering data definition language (DDL) with emphasis on PostgreSQL and common DDL commands supported by ANSI SQL. You will then learn the data manipulation language (DML), and advanced topics like locking and multi version concurrency control (MVCC). This will give you a very robust background to tune and troubleshoot your application. The book then covers the implementation of data models in the database such as creating tables, setting up integrity constraints, building indexes, defining views and other schema objects. Next, it will give you an overview about the NoSQL capabilities of PostgreSQL along with Hstore, XML, Json and arrays. Finally by the end of the book, you'll learn to use the JDBC driver and manipulate data objects in the Hibernate framework.

Style and approach

An easy-to-follow guide to learn programming build applications with PostgreSQL, and manage a PostgreSQL database instance.

Table of contents

  1. Learning PostgreSQL
  2. Table of Contents
  3. Learning PostgreSQL
  4. Credits
  5. About the Authors
  6. About the Reviewers
  7. www.PacktPub.com
    1. Support files, eBooks, discount offers, and more
      1. Why subscribe?
      2. Free access for Packt account holders
  8. 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
  9. 1. Relational Databases
    1. Database management systems
      1. A brief history
      2. Database categories
      3. The NoSQL databases
        1. The CAP theorem
        2. NoSQL motivation
        3. Key value databases
        4. Columnar databases
        5. Document databases
        6. Graph databases
      4. Relational and object relational databases
        1. ACID properties
        2. The SQL Language
        3. Basic concepts
        4. Relation
        5. Tuple
        6. Attribute
        7. Constraint
          1. Domain integrity constraint
          2. Entity integrity constraint
          3. Referential integrity constraints
          4. Semantic constraints
    2. Relational algebra
      1. The SELECT and PROJECT operations
      2. The RENAME operation
      3. The Set theory operations
      4. The CROSS JOIN (Cartesian product) operation
    3. Data modeling
      1. Data model perspectives
      2. The entity-relation model
        1. Sample application
        2. Entities, attributes, and keys
        3. Mapping ER to Relations
      3. UML class diagrams
    4. Summary
  10. 2. PostgreSQL in Action
    1. An overview of PostgreSQL
      1. PostgreSQL history
      2. The advantages of PostgreSQL
        1. Business advantages of PostgreSQL
        2. PostgreSQL user advantages
      3. PostgreSQL applications
      4. Success stories
      5. Forks
      6. PostgreSQL architecture
        1. PostgreSQL abstract architecture
      7. The PostgreSQL community
    2. PostgreSQL capabilities
      1. Replication
      2. Security
      3. Extension
      4. NoSQL capabilities
      5. Foreign data wrapper
      6. Performance
      7. Very rich SQL constructs
    3. Installing PostgreSQL
      1. Installing PostgreSQL on Ubuntu
        1. Client installation
        2. Server installation
        3. Basic server configuration
      2. Installing PostgreSQL on Windows
      3. The PostgreSQL clients
        1. The psql client
          1. Psql advanced settings
        2. PostgreSQL utility tools
        3. Backup and replication
        4. Utilities
        5. PgAdmin III
    4. Summary
  11. 3. PostgreSQL Basic Building Blocks
    1. Database coding
      1. Database naming conventions
      2. PostgreSQL identifiers
      3. Documentation
      4. Version control system
    2. PostgreSQL objects hierarchy
      1. Template databases
      2. User databases
      3. Roles
      4. Tablespace
      5. Template procedural languages
      6. Settings
        1. Setting parameters
        2. Setting a context
    3. PostgreSQL high-level object interaction
    4. PostgreSQL database components
      1. Schema
        1. Schema usages
      2. Table
        1. PostgreSQL native data types
        2. Numeric types
        3. Character types
        4. Date and time types
        5. The car web portal database
    5. Summary
  12. 4. PostgreSQL Advanced Building Blocks
    1. Views
      1. View synopsis
      2. Views categories
      3. Materialized views
      4. Updatable views
    2. Indexes
      1. Index types
      2. Partial indexes
      3. Indexes on expressions
      4. Unique indexes
      5. Multicolumn indexes
      6. Best practices on indexes
    3. Functions
      1. PostgreSQL native programming languages
        1. Creating a function in the C language
        2. Creating functions in the SQL language
        3. Creating a function in the PL/pgSQL language
      2. PostgreSQL function usages
      3. PostgreSQL function dependency
      4. PostgreSQL function categories
      5. PostgreSQL anonymous functions
    4. PostgreSQL user-defined data types
      1. The PostgreSQL CREATE DOMAIN command
      2. The PostgreSQL CREATE TYPE command
    5. Triggers and rule systems
      1. The PostgreSQL rule system
      2. The PostgreSQL trigger system
        1. Triggers with arguments
        2. Using triggers to make views updatable
    6. Summary
  13. 5. SQL Language
    1. SQL fundamentals
      1. SQL lexical structure
    2. Querying the data with the SELECT statement
      1. The structure of the SELECT query
      2. Select-list
        1. SQL expressions
        2. DISTINCT
      3. FROM clause
        1. Selecting from multiple tables
        2. Self-joins
      4. WHERE clause
        1. Comparison operators
        2. Pattern matching
        3. Row and array comparison constructs
      5. Grouping and aggregation
        1. GROUP BY clause
        2. HAVING clause
      6. Ordering and limiting the results
      7. Subqueries
      8. Set operations – UNION, EXCEPT, and INTERSECT
      9. Dealing with NULLs
    3. Changing the data in the database
      1. INSERT statement
      2. UPDATE statement
        1. UPDATE using sub-select
        2. UPDATE using additional tables
      3. DELETE statement
      4. TRUNCATE statement
    4. Summary
  14. 6. Advanced Query Writing
    1. Common table expressions
      1. Reusing SQL code with CTE
      2. Recursive and hierarchical queries
      3. Changing data in multiple tables at a time
    2. Window functions
      1. Window definition
      2. The WINDOW clause
      3. Using window functions
      4. Window functions with grouping and aggregation
    3. Advanced SQL
      1. Selecting the first records
      2. Set returning functions
      3. Lateral subqueries
      4. Advanced usage of aggregating functions
    4. Transaction isolation and multiversion concurrency control
    5. Summary
  15. 7. Server-Side Programming with PL/pgSQL
    1. Introduction
      1. SQL language and PL/pgSQL – a comparison
    2. PostgreSQL function parameters
      1. Function authorization-related parameters
      2. Function planner-related parameters
      3. Function configuration-related parameters
    3. The PostgreSQL PL/pgSQL control statements
      1. Declaration statements
      2. Assignment statements
      3. Conditional statements
      4. Iteration
        1. The loop statement
        2. The while loop statement
        3. The for loop statement
      5. Returning from the function
        1. Returning void
        2. Returning a single row
        3. Returning multiple rows
      6. Function predefined variables
    4. Exception handling
    5. Dynamic SQL
      1. Executing DDL statements in dynamic SQL
      2. Executing DML statements in dynamic SQL
      3. Dynamic SQL and the caching effect
      4. Recommended practices when using dynamic SQL
    6. Summary
  16. 8. PostgreSQL Security
    1. Authentication in PostgreSQL
      1. PostgreSQL pg_hba.conf
      2. Listen addresses
      3. Authentication best practices
    2. PostgreSQL default access privileges
      1. Role system and proxy authentication
    3. PostgreSQL security levels
      1. Database security level
      2. Schema security level
      3. Table-level security
      4. Column-level security
      5. Row-level security
    4. Encrypting data
      1. PostgreSQL role password encryption
      2. pgcrypto
        1. One-way encryption
        2. Two-way encryption
    5. Summary
  17. 9. The PostgreSQL System Catalog and System Administration Functions
    1. The system catalog
    2. Getting the database cluster and client tools version
      1. Getting ready
      2. How to do it…
      3. There's more…
    3. Terminating and canceling user sessions
      1. Getting ready
      2. How to do it…
      3. How it works…
      4. There's more…
    4. Setting and getting database cluster settings
      1. Getting ready
      2. How to do it…
      3. There's more…
    5. Getting the database and database object size
      1. Getting ready
      2. How to do it…
      3. There's more…
    6. Cleaning up the database
      1. Getting ready
      2. How to do it…
      3. There's more…
    7. Cleaning up data in the database
      1. Getting ready
      2. How to do it…
      3. There's more…
    8. Managing database locks
    9. Adding missing indexes on foreign keys and altering the default statistic
      1. Getting ready
      2. How to do it…
    10. Getting the views dependency tree
      1. Getting ready
      2. How to do it…
      3. There's more…
    11. Summary
  18. 10. Optimizing Database Performance
    1. PostgreSQL configuration tuning
      1. Maximum number of connections
      2. Memory settings
      3. Hard disk settings
      4. Planner-related settings
      5. Benchmarking is your friend
    2. Tuning PostgreSQL queries
      1. The EXPLAIN command and execution plan
      2. Detecting problems in query plans
      3. Common mistakes in writing queries
        1. Unnecessary operations
        2. Misplaced indexes
        3. Unnecessary table or index scans
        4. Using correlated nested queries
        5. Using CTE when not mandatory
        6. Using the PL/pgSQL procedural language consideration
    3. Cross column correlation
    4. Table partitioning
      1. Constraint exclusion limitations
    5. Summary
  19. 11. Beyond Conventional Data types
    1. PostgreSQL arrays
      1. Common functions of arrays and their operators
      2. Modifying and accessing arrays
      3. Indexing arrays in PostgreSQL
    2. Hash store
      1. Modifying and accessing an hstore
      2. Indexing an hstore in PostgreSQL
    3. The PostgreSQL JSON data type
      1. JSON and XML
      2. The JSON data type
      3. Modifying and accessing JSON types
      4. Indexing a JSON data type
      5. The PostgreSQL RESTful API with JSON
    4. A PostgreSQL full text search
      1. The tsquery and tsvector data types
        1. The tsvector data type
        2. The tsquery data type
      2. Pattern matching
      3. Full text search indexing
    5. Summary
  20. 12. Testing
    1. Unit testing
      1. Unit testing in databases
      2. Unit test frameworks
    2. Schema difference
    3. The interfaces test
      1. Data difference
    4. PostgreSQL benchmarks
    5. Summary
  21. 13. PostgreSQL JDBC
    1. Introduction to JDBC
    2. Connecting to a PostgreSQL database
      1. Installing the driver
      2. Initializing the driver
      3. Obtaining a connection
      4. Error handling
      5. SQLWarnings
    3. Issuing a query and processing the results
      1. Static statements
      2. PreparedStatements
      3. Using a ResultSet
        1. Navigating through a ResultSet
        2. Reading row data
        3. Handling null values
        4. Scrollable and updateable ResultSets
          1. Navigating through a ResultSet
          2. Changing the data in a ResultSet
      4. Using cursors
      5. Getting information about the table structure
    4. Function handling
      1. Calling a stored function
      2. Getting a ResultSet from a stored function
        1. Getting a ResultSet from a function returning SETOF
        2. Getting a ResultSet from a function returning a refcursor
    5. Design considerations
    6. Summary
  22. 14. PostgreSQL and Hibernate
    1. Introduction to ORM and Hibernate
      1. Hibernate overview and architecture
    2. Installation and configuration
      1. Installation of Hibernate
      2. Configuring Hibernate
      3. Getting a session from the SessionFactory
      4. Mapping classes to tables
        1. Creating an entity class
        2. Creating a mapping file
        3. Using annotation-based mapping
    3. Working with entities
      1. States of an entity
      2. Making a new entity persistent
      3. Loading an entity from the database
      4. Loading a list of entries
        1. Named queries
      5. Creating dynamic queries
      6. Modifying entities
      7. Deleting entities
      8. Using association mapping
        1. One-to-many and many-to-one mappings
        2. One-to-one mapping and component mapping
        3. Many-to-many mapping
      9. Fetching strategies
        1. Configuring the fetch type
        2. Configuring the fetch mode
      10. Tuning the performance of Hibernate
        1. Using caching
        2. Using connection pools
        3. Dealing with partitioned tables
    4. Summary
  23. Index

Product information

  • Title: Learning PostgreSQL
  • Author(s): Salahaldin Juba, Achim Vannahme, Andrey Volkov
  • Release date: November 2015
  • Publisher(s): Packt Publishing
  • ISBN: 9781783989188