Schema is basically a namespace that offers a variety of objects, including indexes, tables, views, functions, datatypes, operators, and sequences. For these objects, the statement “CREATE SCHEMA” is utilized for creating a new schema in the PostgreSQL database. Furthermore, users can modify objects in the existing schema.

This article demonstrates how to use the “CREATE SCHEMA” statement in different interfaces to create the schema in PostgreSQL. The content that illustrates this statement is as follows.

• How to Create Schema in PostgreSQL?
• Method 1: Create Schema in PostgreSQL via pgAdmin 4
• Method 2: Create Schema in PostgreSQL via SQL Shell (psql)
• Example: Create a Table in Schema

Let's start the journey with PostgreSQL.

How to Create Schema in PostgreSQL?

Follow one of the below-given syntaxes to create a schema in Postgres:

CREATE SCHEMA schema_name;

Or

CREATE SCHEMA [IF NOT EXISTS] schema_name;

Parameters

- CREATE SCHEMA: It is a statement utilized for creating a new schema into the database.
- schema_name: It defines the schema’s name in the database.
- [IF NOT EXIST]: It is an optional parameter that checks the occurrence of an existing schema. By using it, a notice will be displayed except for errors.

Method 1: Create Schema in PostgreSQL via pgAdmin 4

The pgAdmin 4 is the Graphical User Interface (GUI) that allows the users to perform various tasks like creating a database, schema, table, etc.

Follow the below instructions to learn how to create a schema using pgAdmin 4:

- A database “db_store”, is already created in the “Databases” section.

- Right-Click on the selected database, hover over the “Create” option, and then click on the “schema” as shown below:

Clicking on the “Schema” will open a new pop-up window in which the user can specify the schema’s name, such as “std_info”, as shown below:

Hit the “Save” button to store the schema name in the targeted database:

Finally, the schema “std_info” has been successfully created in the desired database.

Method 2: Create Schema in PostgreSQL via SQL Shell (psql)

Open the SQL SHELL, specify the necessary details and run the below command to create a schema named emp_data:

CREATE SCHEMA emp_data;

Executing the above command will create the “emp_data” schema into the “postgres” database:

The “CREATE SCHEMA” message shows that schema “emp_data” has been successfully created using SQL Shell. In psql, the users can execute the “\dn” command to get the list of available schemas:

\dn;

Finally, the “emp_data” can be verified in the first place in the list of available schemas.

Example: Create a Table in Existing Schema

Once a schema is created, the user can create functions, tables, and indexes in that schema. Let's carry out an example to create a table in the schema using pgadmin 4. For this purpose, select the “Query Tool” by clicking the right button on the “std_info” schema as shown below:

In Query Tool, the “CREATE TABLE” command is executed as follows:

CREATE TABLE std1(
name Text,
age Integer);

In this example, a table named “std1” having two columns “name” and “age” is created using the “CREATE TABLE” command:

Users can verify a message “CREATE TABLE” in the “Messages” window. Now, execute the “SELECT” command to get the table’s structure:

SELECT * FROM std1;

Finally, the user can confirm that a table “std1” with two columns, “name” and “age” has been created.

Conclusion

PostgreSQL offers the “CREATE SCHEMA” statement to create a schema using “SQL Shell” and “pgAdmin 4” interfaces. In Postgres, a schema is a namespace that offers a variety of objects, including indexes, tables, views, functions, datatypes, operators, and sequences. Once a schema is created, the user can create a table, function, index, etc. within that schema. This article demonstrates all possible methods to create a schema in PostgreSQL.