How to Install Extension in PostgreSQL: Simple Steps

To install an extension in PostgreSQL, use the CREATE EXTENSION extension_name; command inside your database. This command enables the extension's features for that database after it is installed on the server.

Syntax

The basic syntax to install an extension in PostgreSQL is:

• CREATE EXTENSION extension_name; - Installs and enables the extension in the current database.
• IF NOT EXISTS - Optional clause to avoid error if the extension is already installed.
• WITH SCHEMA schema_name - Optional clause to specify the schema where the extension objects will be created.

sql

CREATE EXTENSION [IF NOT EXISTS] extension_name [WITH SCHEMA schema_name];

Example

This example shows how to install the popular uuid-ossp extension, which provides functions to generate UUIDs.

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Output

CREATE EXTENSION

Common Pitfalls

Common mistakes when installing extensions include:

• Trying to create an extension that is not installed on the PostgreSQL server.
• Running CREATE EXTENSION outside a database context (it must be run inside a database).
• Not having sufficient privileges (you need to be a superuser or have the right permissions).
• Forgetting to quote extension names with dashes or special characters.

sql

/* Wrong: extension not installed on server */
CREATE EXTENSION non_existent_extension;

/* Right: check installed extensions first */
SELECT * FROM pg_available_extensions WHERE name = 'uuid-ossp';

/* Wrong: missing quotes for extension with dash */
CREATE EXTENSION uuid-ossp;

/* Right: use quotes */
CREATE EXTENSION "uuid-ossp";

Quick Reference

Summary tips for installing PostgreSQL extensions:

Tip	Description
Use CREATE EXTENSION	To enable an extension in your database.
Check availability	Use SELECT * FROM pg_available_extensions; to see available extensions.
Quote names with special chars	Use double quotes for extensions like "uuid-ossp".
Run inside database	Connect to the target database before running the command.
Require privileges	You need superuser or appropriate rights to install extensions.

Key Takeaways

Use CREATE EXTENSION extension_name; inside your database to install an extension.

Always check if the extension is available on the server before installing.

Quote extension names with special characters using double quotes.

You must have superuser privileges or proper permissions to install extensions.

Run the command connected to the database where you want the extension enabled.