394

How do I list all extensions that are already installed in a database or schema from psql?

See also

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
ARV
  • 6,287
  • 11
  • 31
  • 41

6 Answers6

635

In psql that would be

\dx

See the manual of psql for details.

Doing it in plain SQL it would be a select on pg_extension:

SELECT * 
FROM pg_extension;
laurent
  • 88,262
  • 77
  • 290
  • 428
  • @SoichiHayashi: then you are probably using an old Postgres version –  Aug 23 '14 at 06:29
  • I am running PostgreSQL server 9.3.5, but I am using psql client 8.4.20 that comes with CentOS 6.5. Maybe I need to install the latest version of psql? – Soichi Hayashi Aug 25 '14 at 12:13
  • @SoichiHayashi `\dx` is a **psql** command which was introduced with 9.0 when the `create extension` feature was released. So yes, you need to upgrade your `psql` as well (you should always use the `psql` version that matches your DB version) –  Aug 25 '14 at 13:00
  • 1
    How ironic. http://stackoverflow.com/questions/9463318/how-to-exit-from-postgresql-command-line-utility-psql One of the comments there was that such questions (and the general inability to do really hard things like \?) would annoy a_horse_with_no_name :D. To be fair, this answer contains other useful information. – John Powell Sep 22 '14 at 12:25
  • 15
    you could maybe mention the full SQL query for the list: `select * from pg_extension` – icl7126 Apr 25 '16 at 19:35
133

Additionally if you want to know which extensions are available on your server: SELECT * FROM pg_available_extensions.

See pg_available_extensions and pg_available_extension_versions.

Hans Ginzel
  • 8,192
  • 3
  • 24
  • 22
Dave Glassborow
  • 3,253
  • 1
  • 30
  • 25
15

This SQL query gives output similar to \dx:

SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" 
FROM pg_catalog.pg_extension e 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace 
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass 
ORDER BY 1;

Thanks to https://blog.dbi-services.com/listing-the-extensions-available-in-postgresql/

hit3k
  • 1,240
  • 15
  • 19
  • 10
    The *psql(1)* backslash commands are implemented in SQL; they are like an alias. To view the SQL that executes for each backslash command (e.g. `\dx`) do `\set ECHO_HIDDEN on`. – Clint Pachl Dec 05 '20 at 21:06
11

Just a comment that whether you run as suggested above, in psql either

\dx

or

select extname from pg_extension ;

Keep in mind that

  1. Make sure you are connected to the right database. As your extensions are loaded database specific.
  2. Any extensions added to template1 database will by definition, appear on all databases.
Sumit S
  • 516
  • 5
  • 17
10

The following query outputs a list of installed extensions and their versions:

SELECT oid, extname, extversion FROM pg_extension;
oid extname extversion
13763 plpgsql 1.0
18536 http 1.5
22466 dict_xsyn 1.0

If you want to know which other extensions and their versions are available for installation, run the following query:

SELECT name, default_version, installed_version 
  FROM pg_available_extensions;
name default_version installed_version
dict_xsyn 1.0 1.0
seg 1.4 NULL
pgcrypto 1.3 NULL
plpgsql 1.0 1.0

To install an extension (in case it does not exist), run the following query:

CREATE EXTENSION [ IF NOT EXISTS ] extension_name;

To upgrade an extension to a newer version, use the following query:

ALTER EXTENSION extension_name UPDATE TO 'new_version';
Cloudkollektiv
  • 11,852
  • 3
  • 44
  • 71
6

Before pulling the extension info, a bit of what's running in the background as this can be helpful if you are using query directly from an app or driver to pull this info from postgresql. \dx is pulling info for you from a combination of catalogs called pg_extension, pg_namespace, pg_description and regclass.

Log in, into the psql prompt mentioned below:

psql -h localhost -d postgres -U username -E

with an -E switch which gives you the details of the hidden commands that any alias is using. Once you are IN you can simply do a \dx

which gives you this:

********* QUERY **********
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS 
"Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e 
LEFT JOIN pg_catalog.pg_namespace n 
ON n.oid = e.extnamespace 
LEFT JOIN pg_catalog.pg_description c 
ON c.objoid = e.oid AND c.classoid = 
'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;
**************************

                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

If you were to use a client like DBweaver or PGADMIN, then the slash command might not work for you or even in case of ORM's etc. That's where you can use the above and get similar info on extension or even any other alias commands of postgres.

Raj Verma
  • 1,050
  • 1
  • 7
  • 19