347

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

See also

Evan Carroll
  • 71,692
  • 44
  • 234
  • 400
ARV
  • 5,727
  • 10
  • 30
  • 38

4 Answers4

545

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
  • 83,816
  • 72
  • 267
  • 404
a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
  • @SoichiHayashi: then you are probably using an old Postgres version – a_horse_with_no_name 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) – a_horse_with_no_name 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
  • 13
    you could maybe mention the full SQL query for the list: `select * from pg_extension` – icl7126 Apr 25 '16 at 19:35
118

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
  • 6,784
  • 3
  • 21
  • 22
Dave Glassborow
  • 3,005
  • 1
  • 29
  • 24
14

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,040
  • 13
  • 18
  • 9
    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
2

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
  • 344
  • 2
  • 9