Regina O. Obe - PostgreSQL

• We hope that someone whos just learning about relational databases will find this book useful and make a bond with PostgreSQL for life. In this second edition, we have expanded on many topics, providing elementary examples where possible.

• If youre currently using PostgreSQL or managing it as a DBA, we hope youll find this book handy. Well be flying over familiar terrain, but youll be able to pick up a few pointers and shortcuts introduced in newer versions that could save time. If nothing else, this book is 20 times lighter than the PostgreSQL manual.

• Not using PostgreSQL yet? This book is propagandathe good kind. Each day that youre wedded to a proprietary system, youre bleeding dollars. Each day youre using a less powerful database, youre making compromises with no benefits.

PostgreSQL is special because its not just a database: its also an application platform, and an impressive one at that.

PostgreSQL allows you to write stored procedures and functions in several programming languages. In addition to the prepackaged languages, you can enable support for more languages via the use of extensions. Example built-in languages that you can write stored functions in are SQL and PL/pgSQL. Languages you can enable via extensions are PL/Perl, PL/Python, PL/V8 (aka PL/JavaScript), and PL/R, to name a few. Many of these are packaged with common distributions. This support for a wide variety of languages allows you to solve problems best addressed with a domain-specific or more procedural or functional language; for example, using R statistics and graphing functions, and R succinct domain idioms, to solve statistics problems; calling a web service via Python; or writing map reduce constructs and then using these functions within an SQL statement.

You can even write aggregate functions in any of these languages, thereby combining the data-aggregation power of SQL with the native capabilities of each language to achieve more than you can with the language alone. In addition to using these languages, you can write functions in C and make them callable, just like any other stored function. Functions written in several different languages can participate in one query. You can even define aggregate functions containing nothing but SQL. Unlike in MySQL and SQL Server, no compilation is required to build an aggregate function in PostgreSQL. So, in short, you can use the right tool for the job even if each subpart of a job requires a different tool. You can use plain SQL in areas where most other databases wont let you. You can create fairly sophisticated functions without having to compile anything.

The custom type support in PostgreSQL is sophisticated and very easy to use, rivaling and often outperforming most other relational databases. The closest competitor in terms of custom type support is Oracle. You can define new data types in PostgreSQL that can then be used as a table column type. Every data type has a companion array type so that you can store an array of a type in a data column or use it in an SQL statement. In addition to having the ability to define new types, you can also define operators, functions, and index bindings to work with these new types. Many third-party extensions for PostgreSQL take advantage of these features to achieve performance speedups, provide domain-specific constructs to allow shorter and more maintainable code, and accomplish tasks you can only fantasize about in other databases.

If building your own types and functions is not your thing, you have a wide variety of built-in data types, such as json (introduced in version 9.2), and extensions that provide more types to choose from. Many of these extensions are packaged with PostgreSQL distributions. PostgreSQL 9.1 introduced a new SQL construct, CREATE EXTENSION, that allows you to install an extension with a single SQL statement. Each extension must be installed in each database you plan to use it in. With CREATE EXTENSION, you can install in each database you plan to use any of the aforementioned PL languages and popular types with their companion functions and operators, such as the hstore key-value store, ltree hierarchical store, PostGIS spatial extension, and countless others. For example, to install the popular PostgreSQL key-value store type and its companion functions, operators, and index classes, you would run:

In addition, there is an SQL command you can run (see ) to list the available and installed extensions.

Many of the extensions we mentioned, and perhaps even the languages we discussed, may seem uninteresting to you. You may recognize them and think, Meh, Ive seen Python, and Ive seen Perl.... So what? As we delve further, we hope you experience the same wow moments weve come to appreciate with our many years of using PostgreSQL. Each update treats us to new features, increases usability, brings improvements in speed, and pushes the envelope of what is possible with a relational database. In the end, you will wonder why you ever used any other database, because PostgreSQL does everything you could hope for and does it for free. No more reading the licensing-cost fine print of those other databases to figure out how many dollars you need to spend if you have 8 cores on your server and you need X,Y, and Z functionality, and how much it will cost to go to 16 cores.