A Postgres schema is a namespace that holds the database objects like tables, functions, views, etc. Schemas assist us in organizing the database objects, controlling the database access, preventing unauthorized access, etc. Postgres automatically creates a schema named “public” whenever a new database is defined/created.

Quick Outlines

This post covers all the essential aspects of Postgres schemas using the following outlines.

• How to Create a Schema in Postgres
• How to Show Schemas in Postgres
• How to Alter a Schema in Postgres
• How to Drop a Schema in Postgres
• How to Modify the Schema Path in PostgreSQL

How to Create a Schema in Postgres

To create a new schema in Postgres, use the following syntax:

CREATE SCHEMA [IF NOT EXISTS] name_of_schema;

“IF NOT EXISTS” is an option that creates a schema only if the schema to be created doesn’t exist already.

Example 1: Creating a Postgres Schema Using psql

Open the psql, provide the login details, and execute the CREATE SCHEMA command to create a new schema, for example, “sample_schema”:

CREATE SCHEMA sample_schema;

The “CREATE SCHEMA” message in the output proves that the desired schema has been created successfully:

Example 2: Creating a Postgres Schema Using pgAdmin

To create the schema using pgAdmin, navigate to the "schemas" tab and right-click on it, hover over the "create", and click on the "schema..." option:

Upon doing so, a new window will pop up, type the desired schema name, and click on the "Save" button:

As soon as you click the "Save" button a new schema will be added to the schema list:

How to Show Schemas in Postgres

In SQL Shell, the “\dn” command is used/executed to show the already created schemas:

\dn;

The “\dn” meta-command retrieves all available schemas:

Open the pgAdmin, select the desired database, and expand the schemas section to see the available schemas using the GUI method:

How to Alter a Schema in Postgres

In Postgres, the ALTER SCHEMA command is used to modify an existing schema's definition. The stated command allows us to rename a schema and change the schema’s owner.

To rename a schema, use the below-provided syntax:

ALTER SCHEMA name_of_schema
RENAME TO new_name_of_schema;

To modify the schema’s owner, use the following syntax:

ALTER SCHEMA name_of_schema
OWNER TO new_owner;

Let’s put the ALTER SCHEMA command into practice.

Example 1: Changing Schema’s Name Using psql

In the following code, the ALTER SCHEMA command is used to rename the “sample_schema” to “postgres_schema”:

ALTER SCHEMA sample_schema
RENAME TO postgres_schema;

Use the “\dn” command to verify the schema’s name:

\dn;

The output shows that the “sample_schema” has been successfully renamed to “postgres_schema”.

Example 2: Changing Schema’s Owner Using psql

In the following code, the ALTER SCHEMA command is used to change the schema’s owner from “postgres_schema” to “sample_user”:

ALTER SCHEMA postgres_schema 
OWNER TO sample_user;

Use the “\dn” command to verify the schema’s owner:

\dn;

The schema owner has been successfully modified to “sample_user”.

Example 3: Alter Schema Using pgAdmin

To alter a schema using pgAdmin, navigate to the desired schema from the available schema list, right-click on it, and select the "Properties..." option:

Upon doing so, a new pop-up window for the selected schema will appear, alter the schema name or owner, and hit the "Save" button:

How to Drop a Schema in Postgres?

A particular schema can be dropped from a database using the DROP SCHEMA command, as shown in the following syntax:

DROP SCHEMA name_of_schema;

Example 1: Removing a Postgres Schema Using psql

In the following example, the “DROP SCHEMA” command is used to remove the “example” schema:

DROP SCHEMA example;

Use the “\dn” command to verify the schema’s removal:

\dn;

The selected schema has been removed successfully.

Example 2: Removing a Postgres Schema Using pgAdmin

Right-click on the schema to be removed from the list of available schemas and select either DELETE or DELETE (CASCADE) option to drop the unwanted schema:

Doing so will prompt a confirmation message, click on the "Yes" option to get rid of the selected schema:

How to Modify the Schema Path in PostgreSQL

In Postgres, “public” is the default schema; however, it can be changed using the “SET SEARCH_PATH” command:

SET SEARCH_PATH TO 'name_of_schema';

Example: Setting a Schema

The “SHOW SEARCH_PATH” command is used to see the current schema:

SHOW SEARCH_PATH;

To change the “public” schema to “postgres_schema”, use the following command:

SET SEARCH_PATH TO 'postgres_schema';

Let’s check the current schema via the following command:

SHOW SEARCH_PATH;

The current schema has been changed for the current session.

Read the following guide to learn how to change the default schema permanently.

Conclusion

Postgres offers various built-in commands, such as CREATE SCHEMA, ALTER SCHEMA, and DROP SCHEMA, to create, modify, and drop the Postgres schemas. Use the “\dn” command to show the available schemas. To alter/change the default schema, users must execute the SET SEARCH_PATH command followed by the schema name. This post presented a comprehensive guide on Postgres schemas using practical examples.