Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-create-schema/ below:

PostgreSQL - CREATE SCHEMA - GeeksforGeeks

Last Updated : 15 Jul, 2025

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

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.

To create a schema owned by a specific 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 to better understand the concept.

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

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.

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;

The following statement returns all schemas from the current database:

SELECT 
  * 
FROM 
    pg_catalog.pg_namespace
ORDER BY 
    nspname;

Output

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.

• 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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4