PostgreSQL – CREATE SCHEMA

PostgreSQL provides the CREATE SCHEMA statement to create a new schema in a database. By creating schemas, users can effectively separate data into logical groups, making it easier to manage and access information. Schemas also enhance security by controlling object visibility and permissions, allowing specific users to access or modify objects within a particular schema without impacting others

In this article, we will explain PostgreSQL CREATE SCHEMA, a powerful statement that allows us to create a new schema within a database. Understanding how to create a schema in PostgreSQL is essential for organizing our database objects and managing access effectively

PostgreSQL – CREATE SCHEMA

The PostgreSQL CREATE SCHEMA statement is a powerful command used to create a new schema within a database. A schema serves as a container for organizing database objects like tables, views, and functions, enabling users to maintain a structured and secure environment for managing data. This functionality is essential for enhancing database organization and access control.

Syntax

CREATE SCHEMA [IF NOT EXISTS] schema_name;

key terms

• Schema Name: Specify the name of the schema after the CREATE SCHEMA keywords. The schema name must be unique within the current database.
• Conditional Creation: 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.

Creating a Schema for a User

To create a schema owned by a specific user, use the following syntax:

CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION user_name;

PostgreSQL CREATE SCHEMA Examples

Now that we have known the basics of creating a schema in PostgreSQL, let’s jump into some examples to better understand the concept.

Example 1: Creating a Basic Schema

The following statement creates 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

PostgreSQL CREATE SCHEMA Example1

Explanation:

• The ‘CREATE SCHEMA IF NOT EXISTS marketing;' statement creates a new schema named ‘marketing' if it does not exist already.
• The subsequent query retrieves and displays all schemas in the current database, ordered by their names.
• The output shows that the ‘marketing' schema has been successfully created alongside the default ‘public' schema and any other existing schemas.

Example 2: Creating a Schema for a User

In this example, we will create a new role named Raju. To perform this, we first need to create the user using the following statement:

CREATE USER Raju WITH ENCRYPTED PASSWORD 'Postgres123';

Now, create a schema for the user ‘Raju':

CREATE SCHEMA AUTHORIZATION Raju;

You can also create a schema and assign ownership to ‘Raju':

CREATE SCHEMA IF NOT EXISTS geeksforgeeks AUTHORIZATION Raju;

Query to List All Schemas

The following statement returns all schemas from the current database:

SELECT 
  * 
FROM 
    pg_catalog.pg_namespace
ORDER BY 
    nspname;

Output

PostgreSQL CREATE SCHEMA Example2

Explanation:

• In this example, the user ‘Raju' is first created with an encrypted password.
• The ‘CREATE SCHEMA AUTHORIZATION Raju;' statement then creates a schema that is owned by ‘Raju'.
• The ‘CREATE SCHEMA IF NOT EXISTS geeksforgeeks AUTHORIZATION Raju;' statement creates another schema named ‘geeksforgeeks', also owned by ‘Raju', if it does not already exist.
• The final query lists all schemas in the current database, showing that both the ‘geeksforgeeks' and ‘marketing' schemas have been successfully created, along with their respective owners.

Important Points About PostgreSQL CREATE SCHEMA Statement

• The user who creates the schema automatically becomes the owner of the schema unless specified otherwise using the AUTHORIZATION clause.
• Schema names must be unique within the same database. Attempting to create a schema with a duplicate name will result in an error unless the IF NOT EXISTS clause is used.
• Schema names are case-insensitive unless enclosed in double quotes. For example, ‘CREATE SCHEMA Test' is equivalent to ‘CREATE SCHEMA test', but ‘CREATE SCHEMA "Test"' is treated as a distinct name.
• By default, the creator of the schema has all privileges on it. Privileges for other users must be explicitly granted.

Conclusion

In conclusion, mastering the CREATE SCHEMA syntax in PostgreSQL is crucial for effective PostgreSQL schema management, enabling us to organize our database objects efficiently. By utilizing options like AUTHORIZATION, we can control ownership and permissions within our schemas.

This knowledge empowers us to structure our database in a way that enhances performance and security, ensuring optimal use of resources. Implementing these practices will significantly contribute to better database organization and management in our projects.

FAQs

What is the use of schema in PostgreSQL?

A schema in PostgreSQL organizes database objects like tables and views into logical groups, enhancing manageability and structure. It also allows for control over object visibility and permissions.

What is public schema?

The public schema is the default schema in PostgreSQL where objects are created if no specific schema is specified. It is commonly used for shared database objects accessible to multiple users and applications.

What privileges are needed to create a schema in PostgreSQL?

To create a schema in PostgreSQL, a user needs the CREATE privilege on the database. The user must also have the USAGE privilege on the schema where they want to create the new schema, typically the public schema.

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL-schema