Contents

pg_extension

A system table containing information about extensions

pg_extension is a system catalogue table containing information about extensions.

pg_extension was added in PostgreSQL 9.1.

psql command

  • \dx lists the available extensions
  • \dx+ lists all objects in each extension.

Note that as of PostgreSQL 13, \dx+ does not show whether an object is marked as a configuration relation (see pg_extension_config_dump()).

Dependencies

Objects associated with an extension receive an entry in pg_depend with deptype set to 'e' (DEPENDENCY_EXTENSION).

Definition by PostgreSQL version

pg_extension (PostgreSQL 15)

              Table "pg_catalog.pg_extension"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 oid            | oid     |           | not null | 
 extname        | name    |           | not null | 
 extowner       | oid     |           | not null | 
 extnamespace   | oid     |           | not null | 
 extrelocatable | boolean |           | not null | 
 extversion     | text    | C         | not null | 
 extconfig      | oid[]   |           |          | 
 extcondition   | text[]  | C         |          | 
Indexes:
    "pg_extension_oid_index" PRIMARY KEY, btree (oid)
    "pg_extension_name_index" UNIQUE CONSTRAINT, btree (extname)

Documentation: pg_extension

pg_extension (PostgreSQL 14)

              Table "pg_catalog.pg_extension"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 oid            | oid     |           | not null | 
 extname        | name    |           | not null | 
 extowner       | oid     |           | not null | 
 extnamespace   | oid     |           | not null | 
 extrelocatable | boolean |           | not null | 
 extversion     | text    | C         | not null | 
 extconfig      | oid[]   |           |          | 
 extcondition   | text[]  | C         |          | 
Indexes:
    "pg_extension_oid_index" PRIMARY KEY, btree (oid)
    "pg_extension_name_index" UNIQUE CONSTRAINT, btree (extname)

Documentation: pg_extension

pg_extension (PostgreSQL 13)

              Table "pg_catalog.pg_extension"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 oid            | oid     |           | not null | 
 extname        | name    |           | not null | 
 extowner       | oid     |           | not null | 
 extnamespace   | oid     |           | not null | 
 extrelocatable | boolean |           | not null | 
 extversion     | text    | C         | not null | 
 extconfig      | oid[]   |           |          | 
 extcondition   | text[]  | C         |          | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 12)

              Table "pg_catalog.pg_extension"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 oid            | oid     |           | not null | 
 extname        | name    |           | not null | 
 extowner       | oid     |           | not null | 
 extnamespace   | oid     |           | not null | 
 extrelocatable | boolean |           | not null | 
 extversion     | text    | C         | not null | 
 extconfig      | oid[]   |           |          | 
 extcondition   | text[]  | C         |          | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 11)

              Table "pg_catalog.pg_extension"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 extname        | name    |           | not null | 
 extowner       | oid     |           | not null | 
 extnamespace   | oid     |           | not null | 
 extrelocatable | boolean |           | not null | 
 extversion     | text    |           | not null | 
 extconfig      | oid[]   |           |          | 
 extcondition   | text[]  |           |          | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 10)

              Table "pg_catalog.pg_extension"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 extname        | name    |           | not null | 
 extowner       | oid     |           | not null | 
 extnamespace   | oid     |           | not null | 
 extrelocatable | boolean |           | not null | 
 extversion     | text    |           | not null | 
 extconfig      | oid[]   |           |          | 
 extcondition   | text[]  |           |          | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 9.6)

   Table "pg_catalog.pg_extension"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 extname        | name    | not null
 extowner       | oid     | not null
 extnamespace   | oid     | not null
 extrelocatable | boolean | not null
 extversion     | text    | not null
 extconfig      | oid[]   | 
 extcondition   | text[]  | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 9.5)

   Table "pg_catalog.pg_extension"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 extname        | name    | not null
 extowner       | oid     | not null
 extnamespace   | oid     | not null
 extrelocatable | boolean | not null
 extversion     | text    | not null
 extconfig      | oid[]   | 
 extcondition   | text[]  | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 9.4)

   Table "pg_catalog.pg_extension"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 extname        | name    | not null
 extowner       | oid     | not null
 extnamespace   | oid     | not null
 extrelocatable | boolean | not null
 extversion     | text    | 
 extconfig      | oid[]   | 
 extcondition   | text[]  | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 9.3)

   Table "pg_catalog.pg_extension"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 extname        | name    | not null
 extowner       | oid     | not null
 extnamespace   | oid     | not null
 extrelocatable | boolean | not null
 extversion     | text    | 
 extconfig      | oid[]   | 
 extcondition   | text[]  | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 9.2)

   Table "pg_catalog.pg_extension"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 extname        | name    | not null
 extowner       | oid     | not null
 extnamespace   | oid     | not null
 extrelocatable | boolean | not null
 extversion     | text    | 
 extconfig      | oid[]   | 
 extcondition   | text[]  | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

pg_extension (PostgreSQL 9.1)

   Table "pg_catalog.pg_extension"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 extname        | name    | not null
 extowner       | oid     | not null
 extnamespace   | oid     | not null
 extrelocatable | boolean | not null
 extversion     | text    | 
 extconfig      | oid[]   | 
 extcondition   | text[]  | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)

Documentation: pg_extension

Change history

Examples

postgres=# SELECT * FROM pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 13579 | plpgsql            |       10 |           11 | f              | 1.0        |           | 
 16455 | pg_stat_statements |       10 |         2200 | t              | 1.7        |           | 
(2 rows)

postgres=# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.7     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# \dx+ pg_stat_statements
     Objects in extension "pg_stat_statements"
                Object description                 
---------------------------------------------------
 function pg_stat_statements(boolean)
 function pg_stat_statements_reset(oid,oid,bigint)
 view pg_stat_statements
(3 rows)

Categories

Extension, System catalogue

See also

Extension, pg_available_extensions, pg_available_extension_versions, pg_extension_config_dump(), CREATE EXTENSION, ALTER EXTENSION, DROP EXTENSION