In PostgreSQL 9.1 the contrib modules were all converted to the new concept of extensions. So instead of having to run a SQL script you can simply run.

CREATE EXTENSION hstore;

The advantages of this new command is that extensions can easily be uninstalled, installed in other schemas, and most of the problems with modules relating to database upgrades are gone.

pgnoob here: wouldn't "install extension" have been more accurate? It's probably doing something 'create'-ive under the hood, but to install something with 'create' doesn't seem at all intuitive. If this was something legacy from 30 years ago, I'd understand changing at this point doesn't make much sense, but this seems recent.

I believe it was for symmetry with already existing CREATE commands. Especially CREATE LANGUAGE comes to mind. CREATE LANGUAGE is very similar in that what is does is installing a stored procedure programming language into the current database.

Another advantage of using CREATE is that it is an already existing convention in PostgreSQL which means it is obvious that there may be an ALTER EXTENSION and a DROP EXTENSION (both exist). If they had called it INSTALL then it could be either UNINSTALL EXTENSION or REMOVE EXTENSION.

In summary: It is meant to be intuitive to people who are used to SQL databases.

EDIT: And yes, it does "'create'-ive" under the hood. I.e. the various CREATE commands generally insert new rows in the catalog tables (the tables that store the schema definitions and users). pg_extension is the catalog table containing the list of installed extension.

Thanks for the explanation.

The state of the art of this has advanced significantly with the advent of "extension" support, as seen in CREATE EXTENSION in 9.1+:

  http://www.postgresql.org/docs/9.1/static/sql-createextension.html

The new way is much better than the old way.

using postgres 8.4? seriously? as has been said by fdr, the new extension support is far superior!

(there are many other reasons to target newer postgres as well)

What modules demonstrate well is PostgreSQL's largely unsung degree of programmability. Most databases tie you to the data types that came out of the box, which leads more complex type modelling to be pushed out into the application code.

But PostgreSQL lets you go in the other direction, pushing type logic closer to the actual data store. This makes data storage smarter and can with a bit of judicious footwork simplify your application code.

Indeed. User-defined aggregates[1] are another great tool, and complement user-defined types very nicely.

[1]: http://www.postgresql.org/docs/9.1/static/xaggr.html

This is what has enabled the creation of PostGIS which is the leading spatial SQL database.