PostgreSQL – CREATE SCHEMA
PostgreSQL has a CREATE SCHEMA statement that is used to create a new schema in a database.
Syntax: CREATE SCHEMA [IF NOT EXISTS] schema_name;
Let’s analyze the above syntax:
- First, specify the name of the schema after the CREATE SCHEMA keywords. The schema name must be unique within the current database.
- Second, optionally use IF NOT EXISTS to conditionally create the new schema only if it does not exist. Attempting to create a new schema that already exists without using the IF NOT EXISTS option will result in an error.
Note: To execute the CREATE SCHEMA statement, you must have the CREATE privilege in the current database.
To create a schema for a user use the following:
Syntax: CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION user_name;
Now that we have known the basics of creating a schema in PostgreSQL, let’s jump into some examples.
Example 1:
The following statement uses the CREATE SCHEMA statement to create a new schema named marketing
:
CREATE SCHEMA IF NOT EXISTS marketing;
The following statement returns all schemas from the current database:
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;
Output:
Example 2:
In this example, we will create a schema for a user (say, Raju). to do show let’s first create a user using the below statement:
CREATE USER Raju WITH ENCRYPTED PASSWORD 'Postgres123';
Now create a schema for the user Raju
as follows:
CREATE SCHEMA AUTHORIZATION Raju;
Third, create a new schema that will be owned by Raju:
CREATE SCHEMA IF NOT EXISTS w3wiki AUTHORIZATION Raju;
The following statement returns all schemas from the current database:
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;
Output:
Contact Us