Using psql how do I list extensions installed in a database?

Question

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

See also

• Finding a list of available extensions that PostgreSQL ships with

Caton

Answer 1

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;

Meteorograph

@SoichiHayashi: then you are probably using an old Postgres version – Meteorograph

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? – Venezuela

@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) – Meteorograph

How ironic. #9463818 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. – Kerato

you could maybe mention the full SQL query for the list: select * from pg_extension – Sweetheart

Answer 2

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.

Exceeding

Answer 3

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/

Aecium

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. – Printmaker

Answer 4

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';

Lamp

Answer 5

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.

Kashakashden

Answer 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.

Bond

Good to know the -E flag. – Kashakashden