6. Advanced Query Writing | Learning PostgreSQL

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

Common table expressions

Although SQL is a declarative language, it provides a way of implementing the logic of sequential execution of code or of reusing code. Common table expressions (CTE) is a feature that makes it possible to define a subquery, give it a name, and then use it at several places in the main query.

The simplified syntax diagram for CTE is as follows:

WITH <subquery name> AS (
<subquery code>)
[, ...]
SELECT <Select list> FROM <subquery name>;

In the preceding syntax diagram, subquery code is a query that returns results of which will be used in the primary query as if it was a real table. The subquery in parenthesis, after the keyword AS, is a common table expression. It can also be called a substatement or an auxiliary statement. The query after the WITH block is the primary or main query. The whole statement itself is called the WITH query. It is possible to use not only the SELECT statements in CTE but also the INSERT, UPDATE, or DELETE statements...

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