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.
This post covers all the essential aspects of Postgres schemas using suitable examples.
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: Creating a Postgres Schema
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.
How to Show Schemas in Postgres?
Execute the “\dn” command to show the already created schemas:
\dn;
The “\dn” command retrieves all available schemas.
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
In the following code, 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 “postgres_schema”.
Example 2: Changing Schema’s Owner
In the following code, ALTER SCHEMA command is used to change the schema’s owner from “postgres” to “sample_user” to “postgres_schema”:
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”.
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: Removing a Postgres Schema
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.
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.
