Extensions in Postgres

Upon installing the new Postgres 9.1, I was surprised and stumped when I went to activate the UUID features, as I’ve done for 8.4 and 9.0.

Using the Mac installers provided by EnterpriseDB, I knew the UUID generation and handling features to be bundled but not activated. In the past I’d learned to open this file:

/Library/PostgreSQL/9.0/share/postgresql/contrib/uuid-ossp.sql

and copy-paste to execute the pre-written SQL commands defining Postgres functions. For example, this fragment defines a function to generate a Version 1 UUID:


CREATE OR REPLACE FUNCTION uuid_generate_v1()
RETURNS uuid
AS '$libdir/uuid-ossp', 'uuid_generate_v1'
VOLATILE STRICT LANGUAGE C;


In 9.1, I could not find that "uuid-ossp.sql" file. In fact all but one of the files previously in that "contrib" folder are gone in 9.1. I found some similar files in another folder  named "extension":
/Library/PostgreSQL/9.1/share/postgresql/extension

After some googling, I discovered that “extensions” are a major new feature in 9.1. The idea seems to be making it easier to install and track a bunch of functions or other pieces as one collection rather than separate pieces. Makes sense, as we rarely want to install just one single piece.

This Extension feature is new as of 9.1 as seen on the Feature Matrix, and is discussed in the What's New wiki page. The 9.1 press kit provides a definition:

While PostgreSQL has always been extensible, now users can easily create, load, upgrade, and manage any of dozens of database extensions using the EXTENSION database object. The new site PGXN.org offers a repository for contributing and downloading extensions.

To see what extensions are currently installed, try this SQL command:
select * from pg_extension;
On my newly-installed Postgres 9.1, I see 'plpgsl' and 'adminpack' as the only two installed extensions. See the doc.

To see available extensions bundled with your Postgres and ready to be installed, try either of these SQL commands:

• select * from pg_available_extensions;
• select pg_available_extensions();

For example, I see “uuid-ossp” as the 46th item.

To install ("load") an extension, use the SQL command CREATE EXTENSION. While that command may seem like a misnomer, I suppose it makes sense if you think of it as creating an object within Postgres' world that represents the content of, or access to, the items in the extension. Anyways, to install the UUID extension, execute this SQL:

CREATE EXTENSION "uuid-ossp";

I found the quote marks to be required, despite the example in the doc.

To avoid re-installing, add "in not exists".

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Chapter 35 of the Postgres manual explains how extensions are packaged, if you are curious.

An independent repository of extensions can be found at the PostgreSQL Extension Network.

By the way, to be clear about Postgres’ UUID features, let me say:

• UUID support is built-into Postgres by default, in that Postgres stores UUID values efficiently in their 128-bit form. While your database driver may choose to show UUIDs as hex strings, the UUID values are actually stored in Postgres as their native bits not inefficiently as a string. So UUIDs are not a hack or an add-on in Postgres, and may be searched and indexed like any other supported data type. No need to add a function or extension in order to store and retrieve UUID values in Postgres – you get that for free.
• If you wish to generate any of the various versions of UUID values server-side within the Postgres, then you may choose to load the OSSP-UUID extension.