How to Create Extension in PostgreSQL: Syntax and Examples

In PostgreSQL, you create an extension using the CREATE EXTENSION command followed by the extension name. This command installs additional features or modules into your database, such as hstore or pgcrypto.

Syntax

The basic syntax to create an extension in PostgreSQL is:

• CREATE EXTENSION extension_name; - installs the extension with the given name.
• IF NOT EXISTS - optional clause to avoid error if the extension already exists.
• WITH SCHEMA schema_name - optional clause to specify the schema where the extension objects will be installed.

sql

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

Example

This example shows how to create the hstore extension, which allows storing sets of key/value pairs within a single PostgreSQL value.

sql

CREATE EXTENSION IF NOT EXISTS hstore;

Output

CREATE EXTENSION

Common Pitfalls

Common mistakes when creating extensions include:

• Trying to create an extension without sufficient privileges (you need to be a superuser or have the right permissions).
• Not having the extension installed on the PostgreSQL server (extensions must be installed on the server filesystem).
• Omitting IF NOT EXISTS and causing errors if the extension already exists.

sql

/* Wrong: causes error if extension exists */
CREATE EXTENSION hstore;

/* Right: avoids error if extension exists */
CREATE EXTENSION IF NOT EXISTS hstore;

Quick Reference

Clause	Description
CREATE EXTENSION extension_name;	Installs the named extension.
IF NOT EXISTS	Prevents error if extension already installed.
WITH SCHEMA schema_name	Installs extension objects into specified schema.
DROP EXTENSION extension_name;	Removes the extension from the database.

Key Takeaways

Use CREATE EXTENSION extension_name; to add new features to your PostgreSQL database.

Add IF NOT EXISTS to avoid errors if the extension is already installed.

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

Ensure the extension is installed on the PostgreSQL server before creating it in your database.

Use DROP EXTENSION to remove an extension if needed.