psql Commands for Managing PostgreSQL Extensions
- PostgreSQL extensions are add-on modules that provide additional functionality to the database server.
- They can offer new data types, functions, operators, or even foreign data wrappers for interacting with external systems.
psql
- psql is the command-line interface (CLI) tool for interacting with PostgreSQL databases.
- It allows you to execute SQL statements, manage databases and objects, and perform various administrative tasks.
Listing Installed Extensions
To see a list of extensions currently installed in your PostgreSQL database, use the following command within psql:
\dx
- The backslash (
\
) prefix indicates that this is a psql-specific command, not a standard SQL statement. dx
is a shorthand for the command\d extensions
.
Output Interpretation
The \dx
command will typically display information about each installed extension in a tabular format, including:
- name: The name of the extension.
- type: The type of the extension (usually
p
), indicating a procedural language extension. - owner: The database user who owns the extension.
- default_version: The default version of the extension that was installed.
- installed_version: The actual version of the extension that is currently installed.
- schema: The schema where the extension's objects are installed (often
public
).
Example Output
name | type | owner | default_version | installed_version | schema
-------------------+-------+-------+------------------+--------------------+---------
plpgsql | p | postgres | 11 | 11 | public
uuid-ossp | p | postgres | 1.1 | 1.1 | public
In this example:
plpgsql
anduuid-ossp
are the names of the installed extensions.- Both are procedural language extensions (
p
). - They are owned by the default user
postgres
. - Their default and installed versions are the same (
11
and1.1
, respectively). - Their objects are installed in the
public
schema.
Additional Considerations
- If you want to see more detailed information about a specific extension, you can use the
\d [extension_name]
command. - To see extensions that are available for installation but not yet installed, use the
pg_available_extensions
system view within SQL queries.
\dx
This code snippet will display a list of all extensions currently installed in your PostgreSQL database.
Getting Details of a Specific Extension:
\d plpgsql -- Replace 'plpgsql' with the actual extension name
This code retrieves detailed information about a particular extension, such as its functions, operators, and data types. Replace plpgsql
with the name of the extension you're interested in.
Checking Available Extensions (Using SQL Query):
SELECT name, version
FROM pg_available_extensions;
PostgreSQL provides a system catalog named pg_extension
that stores information about installed extensions. You can query this catalog to retrieve a list of extensions. Here's an example SQL query:
SELECT extname AS name, extversion AS version, pg_catalog.pg_namespace.nspname AS schema
FROM pg_catalog.pg_extension
JOIN pg_catalog.pg_namespace ON pg_catalog.pg_extension.extnamespace = pg_catalog.pg_namespace.oid;
This query retrieves the following information:
extname
: The name of the extension (same asname
in\dx
output).extversion
: The version of the extension (same asinstalled_version
in\dx
).
Using pgAdmin (if applicable):
If you're using a graphical administration tool like pgAdmin, you can visualize installed extensions:
- Open pgAdmin and connect to your PostgreSQL server.
- In the object tree, navigate to the server you're connected to.
- Expand the server node and locate the "Extensions" node.
- This node will list all installed extensions with details like name, version, and owner.
Choosing the Right Method:
- For a quick overview from the command line,
\dx
is the simplest approach. - If you need more flexibility and want to filter or manipulate the extension data using SQL, the
pg_extension
system catalog query is suitable. - If you prefer a visual interface and are already using pgAdmin, that option can be convenient.
postgresql psql postgresql-extensions