In addition to the extensions which are maintained and provided by the core PostgreSQL development team, there are extensions available from third parties. Notably, there is a site dedicated to that purpose: http://www.pgxn.org/
While Evan Carrol's answer is correct, please note that you need to install the postgresql contrib package in order for the CREATE EXTENSION command to work.
The extensions available for each version of Postgresql vary. An easy way to check which extensions are available is, as has been already mentioned:
SELECT * FROM pg_available_extensions;
If the extension that you are looking for is available, you can install it using:
CREATE EXTENSION 'extensionName';
or if you want to drop it use:
DROP EXTENSION 'extensionName';
With psql you can additionally check if the extension has been successfully installed using \dx, and find more details about the extension using \dx+ extensioName. It returns additional information about the extension, like which packages are used with it.
If the extension is not available in your Postgres version, then you need to download the necessary binary files and libraries and locate it them at /usr/share/conrib
How to download and install if you have SUSE. As an example I am downloading the tablefunc module so I can use crosstab. I have PostgreSQL 9.6.1.
right-click desktop, terminal, type:
Enter credentials, continue by typing:
Run query (I ran mine from pgAdminIII):
You should now have the
crosstab
function.I did not have to restart.
In addition to the extensions which are maintained and provided by the core PostgreSQL development team, there are extensions available from third parties. Notably, there is a site dedicated to that purpose: http://www.pgxn.org/
While Evan Carrol's answer is correct, please note that you need to install the postgresql contrib package in order for the CREATE EXTENSION command to work.
In Ubuntu 12.04 it would go like this:
Restart the postgresql server:
All available extension are in:
Now you can run the CREATE EXTENSION command.
Postgrseql 9.1 provides for a new command
CREATE EXTENSION
. You should use it to install modules.Modules provided in 9.1 can be found here.. The include,
If for instance you wanted to install
earthdistance
, simply use this command:If you wanted to install an extension with a hyphen in its name, like
uuid-ossp
, you need to enclose the extension name in double quotes:DROP EXTENSION
. You can also get an extension list, and there is basic support for version numbers.The extensions available for each version of Postgresql vary. An easy way to check which extensions are available is, as has been already mentioned:
If the extension that you are looking for is available, you can install it using:
or if you want to drop it use:
With
psql
you can additionally check if the extension has been successfully installed using\dx
, and find more details about the extension using\dx+ extensioName
. It returns additional information about the extension, like which packages are used with it.If the extension is not available in your Postgres version, then you need to download the necessary binary files and libraries and locate it them at
/usr/share/conrib
Into psql terminal put:
in ubuntu it usually is
/usr/share/postgreslq/<your pg version>/contrib/<contrib file>.sql