Regina Obe - PostgreSQL: Up and Running

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

PostgreSQL allows you to write stored procedures and functions in several programming languages, and the architecture allows you the flexibility to support more languages. Example languages that you can write stored functions in are SQL (built-in), PL/pgSQL (built-in), PL/Perl, PL/Python, PL/Java, and PL/R, to name a few, most of which are packaged with many distributions. This support for a wide variety of languages allows you to solve problems best addressed with a domain or more procedural language; for example, using R statistics 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 that makes the combination more powerful than you can achieve in any one, straight language environment. In addition to these languages, you can write functions in C and make them callable, just like any other stored function. You can have functions written in several different languages participating in one query. You can even define aggregate functions with nothing but SQL. Unlike 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 sub-part 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 of 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. 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 the ability of defining new types, you can also define operators, functions, and index bindings to work with these. Many third-party extensions for PostgreSQL take advantage of these fairly unique features to achieve performance speeds, 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 extensions to choose from, many of which are packaged with PostgreSQL distros. PostgreSQL 9.1 introduced a new SQL construct, CREATE EXTENSION, which allows you to install the many available extensions with a single SQL statement for each in a specific database. With CREATE EXTENSION, you can install in your database any of the aforementioned PL languages and popular types with their companion functions and operators, like hstore, ltree, postgis, and countless others. For example, to install the popular PostgreSQL key-value store type and its companion functions and operators, you would type:

In addition, there is an SQL command you can runsect_extensionsto see the list of available and installed extensions.

Many of the extensions we mentioned, and perhaps even the languages we discussed, may seem like arbitrary terms 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 we have come to appreciate with our many years of using PostgreSQL. Each update treats us to new features, eases usability, brings improvements in speed, and pushes the envelope of what is possible with a database. In the end, you will wonder why you ever used any other relational database, when PostgreSQL does everything you could hope forand 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, Z functionality, and how much it will cost you when you get 16 cores.

On top of this, PostgreSQL works fairly consistently across all supported platforms. So if youre developing an app you need to resell to customers who are running Linux, Mac OS X, or Windows, you have no need to worry, because it will work on all of them. There are binaries available for all if youre not in the mood to compile your own.