Chapter 1, Getting Started with PostgreSQL, explains the birth, present, and future along with the evolution of PostgreSQL in terms of its features, maintainability, and its immensely huge global following. This chapter will explain the concepts of DDL (Data Description Language) and DML (Data Manipulation Language), and explain how to write DDL and DML statements.

Chapter 2, The Procedural Language, encompasses the diversified features of PL/pgSQL, native support for four languages, and extensibility for others. We will skim through the description of PL/pgSQL by explaining its structure, declarations, and verbal expressions. This chapter will shed light on using native support and utilization examples of other procedural languages such as PL/Python, PL/Tcl, and PL/Perl.

Chapter 3, Working with Indexes, is all about indexes, so expect to see a discussion of the fundamental concepts of indexes, such as the kinds of indexes PostgreSQL supports and the syntax to create them. The main story of this chapter is where to utilize what kind of index and which condition it is best suited for. You can then build different kinds of indexes in the warehouse database to explicate the practical use of indexes.

Chapter 4, Triggers, Rules, and Views, consists of three sections: triggers, rules, and views. The first section of this chapter will explain what a trigger is and how to create triggers in PostgreSQL. The second part will deal with PostgreSQL rules. There will be a focus on how the rules work by explaining their call, input, and the results. The final third part will revolve around views and why they are important in database design.

Chapter 5, Window Functions, discusses the power and concepts of window functions in conjunction with aggregate functions. We will also cover the scope, structure, and usage of window functions with examples. Another objective will be to acquire a crystal clear understanding of the core of window functions and the data that is processed with the help of frame, OVER, PARTITION BY, and ORDER BY clauses. This chapter will also discuss the available built-in window functions, along with custom ones.

Chapter 6, Partitioning, deals with table partitioning. Table partitioning in PostgreSQL is implemented through table inheritance. In this section, there will be a brief overview of partition in order to improve the performance of queries before moving on to its implementation in PostgreSQL. The chapter also covers the list (that utilizes key values) and range (that utilizes key columns) partitions.

Chapter 7, Query Optimization, is about query analysis and optimization. Queries can be optimized utilizing indexes and hints and manipulating planner parameters. As a reader, you will find this chapter very useful in utilizing and optimizing your queries.

Chapter 8, Dealing with Large Objects, is about the handling of Large Objects (LO) as there is a need to store large objects such as audio and video files. PostgreSQL has support to manipulate these objects. The handling of sizably huge objects is consummately different from the other objects such as text, varchar, and int. This chapter will explain why we need to store Large Objects (LO) and how PostgreSQL implements LO storage.

Chapter 9, Communicating with PostgreSQL Using LibPQ, explains how to write C programs and connect and execute queries in the C language using libpq, which is a PostgreSQL client library to communicate with the PostgreSQL server. In this chapter, we will grip on the different ways of communication by utilizing libpq and the utilization of all libpq functions. To extend our story of the warehouse database, we will write a program to manipulate the data from the database.

Chapter 10, Embedded SQL in C – ECPG, covers all the syntax and utilization of Embedded SQL to manipulate the data inside this code. Other than libpq, there is an alternative to communicate in C code to a PostgreSQL server called ECPG. Additionally, there will be coverage of how to compile the ECPG program, and we will discuss the command-line options of the ECPG binary.

Chapter 11, Foreign Data Wrapper, covers how to explain the building blocks of the foreign data wrapper and discusses how to utilize postgres_fdw and file_fdw to manipulate foreign data. PostgreSQL introduces an incipient feature called the foreign data wrapper. It's a template to write the module to access foreign data. This is rigorously based in SQL/MED standards (SQL Management of External Data). There are only two community maintained wrappers, postgres_fdw and file_fdw, along with many externally maintained foreign data wrappers.

Chapter 12, Extensions, covers how to install and use available extensions in PostgreSQL. PostgreSQL has features to install the loadable modules called extensions. Instead of creating a bunch of objects by running SQL queries, an extension, which is a collection of objects, can be created and dropped using a single command. The main advantage of an extension is maintainability. There are several extensions available.

To get the most out of this book along with practical, hands-on experience, you should practice the examples described. For this, you'll need the following:

This book is for database developers who are interested in learning and understanding PostgreSQL from scratch. Though you will need to know the basic database concepts in order to understand the technical terms used throughout this book, PostgreSQL is an open source and growing database community at, so having a firm grasp on this will definitely increase your confidence in the domain of open source databases.

You will be able to understand how applications can be programmed with PostgreSQL, along with understanding the core development concepts. All the examples will cover the functionality and syntax that is in compliance with the latest versions of PostgreSQL, 9.3 and 9.4.

In this book, you will find a number of styles of text 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: "With the ALTER TABLE command, we can add, remove, or rename table columns."

A block of code is set as follows:

warehouse_db=# CREATE TABLE item
  (
  item_unique INTEGER PRIMARY KEY,
  item_name TEXT,
  item_price NUMERIC,
  item_data TEXT
  );

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

warehouse_db=# CREATE OR REPLACE FUNCTION getRecords()
RETURNS INTEGER AS $$
DECLARE
  total INTEGER;
BEGIN
  SELECT COUNT(*) INTO total FROM warehouse_tbl;
  IF (total > 0) THEN
    RETURN total;
  ELSE
    RAISE NOTICE 'table is empty';
  END IF;
END;
$$ LANGUAGE plpgsql;

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

$ createlang plpgsql warehouse_db -U postgres

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "The team added core object-oriented features in Ingres and named the new version PostgreSQL."

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.