Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Learning PostgreSQL

By : Salahaldin Juba, Achim Vannahme, Andrey Volkov

4.3 (6)

Learning PostgreSQL

4.3 (6)

By: Salahaldin Juba, Achim Vannahme, Andrey Volkov

Overview of this book

PostgreSQL is one of the most powerful and easy to use database management systems. It supports the most advanced features included in SQL standards. 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.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Relational Databases

1. Relational Databases

Database management systems

Relational algebra

Data modeling

Summary

2. PostgreSQL in Action

2. PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL capabilities

Installing PostgreSQL

Summary

3. PostgreSQL Basic Building Blocks

3. PostgreSQL Basic Building Blocks

Database coding

PostgreSQL objects hierarchy

PostgreSQL high-level object interaction

PostgreSQL database components

Summary

4. PostgreSQL Advanced Building Blocks

4. PostgreSQL Advanced Building Blocks

Views

Indexes

Functions

PostgreSQL user-defined data types

Triggers and rule systems

Summary

5. SQL Language

5. SQL Language

SQL fundamentals

Querying the data with the SELECT statement

Changing the data in the database

Summary

6. Advanced Query Writing

6. Advanced Query Writing

Common table expressions

Window functions

Advanced SQL

Transaction isolation and multiversion concurrency control

Summary

7. Server-Side Programming with PL/pgSQL

7. Server-Side Programming with PL/pgSQL

Introduction

PostgreSQL function parameters

The PostgreSQL PL/pgSQL control statements

Exception handling

Dynamic SQL

Summary

8. PostgreSQL Security

8. PostgreSQL Security

Authentication in PostgreSQL

PostgreSQL default access privileges

PostgreSQL security levels

Encrypting data

Summary

9. The PostgreSQL System Catalog and System Administration Functions

9. The PostgreSQL System Catalog and System Administration Functions

The system catalog

Getting the database cluster and client tools version

Terminating and canceling user sessions

Setting and getting database cluster settings

Getting the database and database object size

Cleaning up the database

Cleaning up data in the database

Managing database locks

Adding missing indexes on foreign keys and altering the default statistic

Getting the views dependency tree

Summary

10. Optimizing Database Performance

10. Optimizing Database Performance

PostgreSQL configuration tuning

Tuning PostgreSQL queries

Cross column correlation

Table partitioning

Summary

11. Beyond Conventional Data types

11. Beyond Conventional Data types

PostgreSQL arrays

Hash store

The PostgreSQL JSON data type

A PostgreSQL full text search

Summary

12. Testing

12. Testing

Unit testing

Schema difference

The interfaces test

PostgreSQL benchmarks

Summary

13. PostgreSQL JDBC

13. PostgreSQL JDBC

Introduction to JDBC

Connecting to a PostgreSQL database

Issuing a query and processing the results

Function handling

Design considerations

Summary

14. PostgreSQL and Hibernate

14. PostgreSQL and Hibernate

Introduction to ORM and Hibernate

Installation and configuration

Working with entities

Summary

Index

Index

Conventions

In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "The customer_service associates the customer and the service relations."

A block of code is set as follows:

<hibernate-mapping package="carportal" schema="carportal_app">
  <class name="Account" table="account">
    <id name="accountID" column="account_id">
      <generator class="identity"/>
    </id>

Any command-line input or output is written as follows:

SELECT first_name, last_name, service_id
FROM customer AS c CROSS JOIN customer_service AS cs
WHERE c.customer_id=cs.customer_id AND c.customer_id = 3;

New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "Another option is to use a Linux emulator such as Cygwin and MobaXterm."

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Learning PostgreSQL

Search

Your notes and bookmarks