PostgreSQL is an open source relational database management system that began as a research project at the University of California, Berkeley. It was originally released under the BSD license but now uses the PostgreSQL License (TPL). For all intents and purposes, it’s BSD-licensed. It has a long history, dating back to 1985.
PostgreSQL has enterprise-class features such as SQL windowing functions, the ability to create aggregate functions and also utilize them in window constructs, common table and recursive common table expressions, and streaming replication. These features are rarely found in other open source databases but are common in newer versions of proprietary databases such as Oracle, SQL Server, and DB2. What sets PostgreSQL apart from other databases, including the proprietary ones we just mentioned, is how easily you can extend it, usually without compiling any code. Not only does it include advanced features, but it also performs them quickly. It can outperform many other databases, including proprietary ones, for many types of database workloads.
In this book, we’ll expose you to the advanced ANSI SQL features that PostgreSQL offers and the unique features it contains. If you’re an existing PostgreSQL user or have some familiarity with it, we hope to show you some gems you may have missed along the way or features found in newer PostgreSQL versions that are not in the version you’re using. This book assumes you’ve used another relational database before but may be new to PostgreSQL. We’ll show some parallels in how PostgreSQL handles tasks compared to other common databases, and we’ll demonstrate feats you can achieve with PostgreSQL that are difficult or impossible to do in other databases. If you’re completely new to databases, you’ll still learn a lot about what PostgreSQL has to offer and how to use it; however, we won’t try to teach you SQL or relational theory. You should read other books on these topics to take the greatest advantage of what this book has to offer.
This book focuses on PostgreSQL versions 9.2, 9.3, and 9.4, but we will cover some unique and advanced features that are also present in prior versions of PostgreSQL.
We hope that both working and budding database professionals will find this book to be of use. We specifically target the following ilk:
We hope that someone who’s 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 you’re currently using PostgreSQL or managing it as a DBA, we hope you’ll find this book handy. We’ll be flying over familiar terrain, but you’ll 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 propaganda—the good kind. Each day that you’re wedded to a proprietary system, you’re bleeding dollars. Each day you’re using a less powerful database, you’re making compromises with no benefits.
If your work has nothing to do with databases or IT, or if you’ve just graduated from kindergarten, the cute picture of the elephant shrew on the cover should be worthy of the price alone.
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 won’t 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
(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,
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
hstore key-value store,
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 Extensions) 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, I’ve seen Python, and I’ve seen Perl.... So what?” As we delve further, we hope you experience the same “wow” moments we’ve 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.
On top of this, PostgreSQL works fairly consistently across all supported platforms. So if you’re developing an app you need to resell to customers who are running Unix, Linux, Mac OS X, or Windows, you have no need to worry, because it will work on all of them. Binaries are available for all platforms if you’re not in the mood to compile your own.
PostgreSQL was designed from the ground up to be a multiapplication, high-transactional database. Many people do use it on the desktop in the same way they use SQL Server Express or Oracle Express, but just like those products, PostgreSQL cares about security management and doesn’t leave this up to the application connecting to it. As such, it’s not ideal as an embeddable database for single-user applications—unlike SQLite or Firebird, which perform role management, security checking, and database journaling in the application.
Sadly, many shared hosts don’t have PostgreSQL preinstalled, or they include a fairly antiquated version of it. So, if you’re using shared hosting, you might be forced to use MySQL. This situation has been improving and has gotten much better since the first edition of this book. Keep in mind that virtual, dedicated hosting and cloud-server hosting are reasonably affordable and getting more competitively priced. The cost is not that much higher than for shared hosting, and you can install any software you want. Because you’ll want to install the latest stable version of PostgreSQL, choosing a virtual, dedicated, or cloud server for which you are not confined to what the ISP preinstalls is more suitable for running PostgreSQL. In addition, Platform as a Service (PaaS) offerings have added PostgreSQL support, which often offers the latest released versions of PostgreSQL: four notable offerings are SalesForce Heroku PostgreSQL, Engine Yard, Red Hat OpenShift, and Amazon RDS for PostgreSQL.
PostgreSQL does a lot and can be daunting. It’s not a dumb data store; it’s a smart elephant. If all you need is a key-value store or you expect your database to just sit there and hold stuff, it’s probably overkill for your needs.
This book is geared toward demonstrating the unique features of PostgreSQL that make it stand apart from other databases, as well as how to use these features to solve real-world problems. You’ll learn how to do things you never knew were possible with a database. Aside from the cool “eureka!” stuff, we will also demonstrate bread-and-butter tasks, such as how to manage your database, set up security, troubleshoot performance problems, improve performance, and connect to your database with various desktop, command-line, and development tools.
PostgreSQL has a rich set of online documentation. We won’t endeavor to repeat this information, but we encourage you to explore what is available. There are more than 2,250 pages in the manuals available in both HTML and PDF formats. In addition, fairly recent versions of these online manuals are available for hard-copy purchase if you prefer paper form. Since the manual is so large and rich in content, it’s usually split into a three- to four-volume book set when packaged in hard-copy form.
Other PostgreSQL resources include:
Planet PostgreSQL is an aggregator of PostgreSQL blogs. You’ll find PostgreSQL core developers and general users showcasing new features and demonstrating how to use existing ones.
PostgreSQL Wiki provides lots of tips and tricks for managing various facets of the database and migrating from other databases.
PostgreSQL Books is a list of books about PostgreSQL.
PostGIS in Action Books is the website for the books we’ve written about PostGIS, the spatial extender for PostgreSQL.
For elements in parentheses, we gravitate toward placing the open parenthesis on the same line as the preceding element and the closing parenthesis on a line by itself to satisfy columnar constraints for printing:
function ( Welcome to PostgreSQL );
We also remove gratuitous spaces in screen output, so if the formatting of your results doesn’t match ours exactly, don’t fret.
We recommend adding a single space after a serial comma, but we do omit them at times in this book to fit to page width.
The SQL interpreter treats tabs, new lines, and carriage returns as white space. In our code, we generally use white spaces for indentation, not tabs. Make sure that your editor doesn’t automatically remove tabs, new lines, and carriage returns or convert them to something other than spaces.
After copying and pasting, if you find your code not working, check the copied code to make sure it looks like what we have in the listing.
Some examples use Linux and some use Windows. For examples such as
foreign data wrappers that require full-path settings, you may see a path
/postgresql_book/somefile.csv. These are always
relative to the root of your server. If you are on Windows, you must
include the drive letter:
Even on Windows, you need to use the standard Linux path slash
The following typographical conventions are used in this book:
Indicates new terms, URLs, email addresses, file names, and file extensions.
Used for program listings. Used within paragraphs, where needed for clarity, to refer to programming elements such as variables, functions, databases, data types, environment variables, statements, and keywords.
Constant width bold
Shows commands or other text that should be typed literally by the user.
Constant width italic
Shows text that should be replaced with user-supplied values or by values determined by context.
This icon signifies a tip, suggestion, or general note.
This icon indicates a warning or caution.
Supplemental material (code examples, exercises, etc.) is available for download at http://www.postgresonline.com/downloads/postgresql_book_2e.zip.
This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “PostgreSQL: Up and Running, Second Edition by Regina Obe and Leo Hsu (O’Reilly). Copyright 2015 Regina Obe and Leo Hsu, 978-1-4493-7319-1.”
If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at firstname.lastname@example.org.
Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.
Safari Books Online offers a range of product mixes and pricing programs for organizations, government agencies, and individuals. Subscribers have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and dozens more. For more information about Safari Books Online, please visit us online.
Please address comments and questions concerning this book to the publisher:
|O’Reilly Media, Inc.|
|1005 Gravenstein Highway North|
|Sebastopol, CA 95472|
|800-998-9938 (in the United States or Canada)|
|707-829-0515 (international or local)|
We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at:
To comment or ask technical questions about this book, send email to:
For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.
Find us on Facebook: http://facebook.com/oreilly
Follow us on Twitter: http://twitter.com/oreillymedia
Watch us on YouTube: http://www.youtube.com/oreillymedia