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 geeksforgeeks AUTHORIZATION Raju;

The following statement returns all schemas from the current database:

SELECT 
  * 
FROM 
    pg_catalog.pg_namespace
ORDER BY 
    nspname;

Output: