intro
Let's learn everything you need to know about the Postgres CREATE SCHEMA statement to add new schemas to your database.
Adding schemas to databases is a common task for PostgreSQL users and DBAs. This is done using the Postgres CREATE SCHEMA statement, which comes from the ANSI/ISO SQL standard and enables you to create new schemas effortlessly.
Time to master CREATE SCHEMA in PostgreSQL. Let’s dive in!
What Is a Schema in PostgreSQL?
In PostgreSQL, a database can contain one or more schemas. In this context, a schema is a logical namespace that organizes database objects such as tables, views, sequences, indexes, materialized views, as well as custom data types, functions, and operators.
Note: A Postgres schema does not correspond to a directory in the file system, but it is rather a logical concept within the database itself.
Schemas help manage access control and prevent name conflicts. That is because objects of the same type within a schema must have unique names. In detail, objects within a schema are accessed using the schema_name.object_name notation.
There are reasons why the team behind PostgreSQL decided to adopt schemas:
By default, PostgreSQL uses the public schema, but you can also create new custom schemas (as we are about to see!).
CREATE SCHEMA PostgreSQL Syntax and Usage
This is the most commonly used syntax for the Postgres CREATE SCHEMA statement:
1
CREATE SCHEMA [IF NOT EXISTS] schema_name [ AUTHORIZATION role_specification ];
Where:
The above CREATE SCHEMA statement adds a new schema named schema_name to the current database. The schema name must be unique within the database; otherwise, the statement will fail with this error:
1
ERROR: schema "schema_name" already exists
To prevent the above error, you can use IF NOT EXISTS. This clause guarantees that the schema is created only if it does not already exist. If already exists, nothing will happen.
Note that other syntax variations are available. For more details, refer to the official documentation.
Notes:
How to Create a Schema in PostgreSQL
In the following example, we will explore two ways to create a schema in PostgreSQL:
Suppose you have a company database and want to create a separate PostgreSQL schema for a new e-commerce application. The goal is to add an ecommerce schema to that company database.
Let’s see how to achieve that using both methods!
With the CREATE SCHEMA Statement
Connect to your company database using either psql. Then, run the following query to create the ecommerce schema in PostgreSQL:
1
CREATE SCHEMA postgres;
Now, if you list the schemas in the company database using psql, you will see:
Great! As shown in the image above, the ecommerce schema has been successfully added.
With a Visual Database Client
Creating a schema by manually launching a Postgres CREATE SCHEMA query is certainly possible. Still, when using the CLI, there is no feedback upon success. Also, you need to remember the exact syntax of the statement and its nuances.
A simpler alternative is using a visual PostgreSQL database client like DbVisualizer.
DbVisualizer is a feature-rich database client that lets you connect to a PostgreSQL server (and 50+ other databases) to manage your data visually. It supports both manual queries and UI-based operations, such as creating, altering, and dropping databases, tables, and schemas—all that without writing SQL commands.
In particular, you can use it to create a schema in PostgreSQL as follows:
Step 1: Download DbVisualizer for free and install it.
Step 2: Connect to your PostgreSQL server.
Step 3: Expand the "Databases" dropdown on the left, locate the "company" database, and expand the "Schemas" section:
Step 4: Right-click on "Schemas" and select the "Create Schema…" option:
Step 5: Fill out the form in the PostgreSQL schema creation modal and click "Execute" when done:
Behind the scenes, DbVisualizer executes the CREATE SCHEMA PostgreSQL statement—which you can see in real-time in the “SQL Preview” section.
Once executed, the new schema will appear in the "Schemas" dropdown:
Et voilà! You have created a PostgreSQL schema in just a few clicks—without writing a single SQL query.
Conclusion
In this article, you learned how to use the Postgres CREATE SCHEMA statement. You explored its syntax and saw it in action through a detailed example. While you can run the query directly from the command line using psql, managing databases is much easier with a powerful client like DbVisualizer, which offers full support for PostgreSQL and many other databases.
FAQ
What happens at a low level when you create a new schema in PostgreSQL?
At a low level, when creating a new schema, PostgreSQL updates the pg_namespace system catalogs, which store schema metadata. In particular, Postgres assigns a unique OID (Object Identifier) to the new schema and records its name, owner, and access privileges. If subcommands are included, objects like tables and functions are also registered in their respective system tables. Note that no physical files are created. That is because schemas act as logical namespaces within the database, organizing objects without affecting storage structure.
What is the difference between a schema and a database in PostgreSQL?
These are the main differences between a database and a schema in PostgreSQL:
What is the opposite of a create database schema PostgreSQL statement?
The opposite of the CREATE SCHEMA PostgreSQL statement is DROP SCHEMA, which can remove one or more schemas and their objects. The syntax is:
1
DROP SCHEMA [ IF EXISTS ] schema_name_1 [, ..., schema_name_n] [ CASCADE | RESTRICT ]
Where:
In particular, CASCADE means that the statement will drop the schema(s) along with all dependent objects. Instead, RESTRICT instructs the statement to fail if the schema(s) contain objects.
How to modify a schema in Postgres?
In PostgreSQL, you can modify a schema using the ALTER SCHEMA statement. This enables you to:
Note that ALTER SCHEMA does not let you modify objects within the schema. That can only be done by changing them individually.
Why use a visual database client to create a schema in Postgres?
Utilizing a visual database client like DbVisualizer to create a schema in Postgres offers several advantages. That is because it provides an intuitive UI, making schema creation and management much easier compared to using command-line tools. With DbVisualizer, you can visually see your queries, monitor execution, and access powerful features like drag-and-drop query creation and query optimization. Test all it has to offer!
