How to use CREATE SCHEMA in Postgres

In Database Management Systems (DBMSs), the visual representation of data stored inside databases is known as a schema. The data organization and presentation are the responsibility of the schema; however, the schema does not keep data itself. The developers practice looking for schemas before going for the development phase as they can get information about the data stored and the relationships between the tables.

Postgres databases do have schema support. The create schema statement in Postgres is practiced by creating a new schema with several functionalities. Apart from creating schema, the “create schema” command can also be used to specify the owners of a schema. This descriptive guide presents the usage as well as the working mechanism of creating the schema in Postgres.

Prerequisites

A Postgres database may contain multiple schemas inside it, but one schema can only entertain one database at a time. Thus, the create schema statement refers to the Postgres database. So you must have the following prerequisites before you dig into creating schema:

• Ensure that the Postgres database is installed, and the service is active and running.
• A Postgres database is present where you want to create the schema.

How does CREATE SCHEMA statement work

In the introductory part of this guide, you would have had an overview of the create schema statement. This section provides a working mechanism for this statement. The building blocks of create schema statement depend on the following syntax:

The description of the above syntax is provided below:

<name-of-schema>: You have to specify the schema name here. If the field is left blank, the user’s name is set to the schema name.

<AUTHORIZATION>: specifies the user that will own the new schema.

<schema-element>: This refers to the SQL clauses like CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE INDEX, CREATE TRIGGER.

How to use Postgres CREATE SCHEMA

In the previous section, you learned how the Postgres CREATE SCHEMA statement works. To do some practicality, we have provided some examples that exercise the use of Postgres to create a schema.

Create a new schema : The command provided below will create a new schema in the mydb database starting from the basics.

Note: We are logged in to a database named mydb.

You can verify the schema creation with the help of the command stated below. In our previous command, we created a linuxhint schema. As a result, it is present in the output.

Using CREATE SCHEMA to authorize a user : You can specify the owner of a schema as well. The Postgres command below will grant the ownership of “linux” schema to a user named “sam“.

Note: If you are getting an error while executing the above-mentioned command, then there are chances that the user does not exist. To create a database user, you may refer to the command provided below:

Use CREATE SCHEMA with IF NOT EXIST : The CREATE SCHEMA statement always looks for unique records, and if the schema already exists, then creating schema will return an error. As an example, suppose we wanted to create a schema called linux(which already exists). The create schema would return an error in such a case, as seen in the command’s output provided below.

The CREATE SCHEMA can be used with the “IF NOT EXISTS” option to avoid errors. When this option is specified, the create schema neither returns any error nor creates a duplicate. The command written below tries to create a schema named linux(that already exists), but the output issues a NOTICE instead of an error.

Bonus Tip

The CREATE SCHEMA functionality is exercised briefly in the above sections. Here we come up with a bonus tip related to schemas. A schema primarily depends on tables and views. So, we have compiled this section to guide you about creating a table and a view inside a schema.

Create a table inside a schema : Inside a Postgres schema, the tables (where the data is stored) and the views (an SQL query) can also be created. For understanding, we have created a table (named projects) and a view (named type) in the existing schema (linux) of a mydb database.

Conclusion

A database schema interprets what your data would look like inside a database. The schema represents how the data is organized in tables and how the data is stored on a disk. This post describes the implementation of the CREATE SCHEMA command in Postgres databases. One can follow this guide to create a schema for a database, dedicate a schema to a specific user/owner. Additionally, a bonus tip is provided that guides you to create a table and a view inside a schema.