PostgreSQL: Best Practice – Create Extension in Schema

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a best practice of creating any extension in Schema of PostgreSQL.

Using Extension, we can add additional functionality in PostgreSQL.
If we do not provide any schema specification, it will create under search_path schema which PUBLIC most of the time.

PostgreSQL: The Schema Search Path and change the default PUBLIC Schema

Now, why we should create an extension in schema?

For example, I created a ‘dblink’ extension without schema specification and I am using dblink functions without giving any schema name because it formed in search_path schema.

But, If I create a ‘dblink’ extension in dblink schema, we have to access dblink features by specifying the schema name of that extension. This is also good for code and other management as a backup of the schema.

Check the below example:

Create a user-defined schema:

Create a dblink extension, under dblink schema:

PostgreSQL [Video]: Cross Database Queries using DbLink Extension