Trending October 2023 # How To Create And Drop Postgresql Schema # Suggested November 2023 # Top 14 Popular

You are reading the article How To Create And Drop Postgresql Schema updated in October 2023 on the website Cersearch.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Create And Drop Postgresql Schema

Introduction to PostgreSQL Schema

In PostgreSQL, a schema is a named collection of database objects which contain tables, views, sequences, indexes, data types, functions, operators, and other relations. These are symmetrical to the operating system level directory structure, except that the PostgreSQL schemas cannot be nested.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Working of PostgreSQL Schema

The database schema shows the part of the logical configuration or all of a relational database.

Access to the schemas can be controlled depending on the cases required.

The ownership of the schema is transferrable.

The database schema represents how the entities that make up the database relate to each other, including its views, tables, stored procedures, etc.

How to Create a PostgreSQL Schema?

To create a schema, you need to use the CREATE SCHEMA statement. You can specify the name of your choice for a schema. The CREATE SCHEMA statement will create a schema in the current database.

Note: To execute the CREATE SCHEMA statement, the user needs to have the CREATE a special right in the current database.

CREATE SCHEMA [ IF NOT EXISTS] schema_name;

Explanation: Define the schema name for the schema to be created after the CREATE SCHEMA clause. The name of the schema should be unique within the current database. IF NOT EXISTS is an option clause that adds a condition for creating the new schema only if it does not exist. If you try to create a new schema that already exists in the current database without using the IF NOT EXISTS clause will result in an error.

We can create a schema for a specific user as well:

Syntax #2

CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION user_name;

Explanation:

This is similar to Syntax 1 only difference because for creating a schema for the specific users, we need to specify the user_name after the AUTHORIZATION keyword, which is the same as the schema name.

You can create a schema and list of data objects in a single statement.

Syntax #3

CREATE SCHEMA schema_name CREATE TABLE table_name1 (...) CREATE TABLE table_name2 (...) CREATE VIEW view_name1 SELECT select_list FROM table_name1;

Note: Each sub-command in the above syntax does not end with a semicolon (;).

Examples to Implement PostgreSQL Schema

Let’s understand the CREATE SCHEMA statement with the help of the following examples:

Example #1

Create a New Schema. Create a new schema named EduCBASchema:

Syntax:

CREATE SCHEMA IF NOT EXISTS EduCBASchema;

We can fetch all schemas from the current database using the following statements.

Code:

SELECT  * FROM pg_catalog.pg_namespace ORDER BY nspname;

Output: The following result will be shown after executing the above statement:

Example #2

Syntax:

CREATE SCHEMA AUTHORIZATION EduCBA;

Create a schema and its objects in a single statement. The following example uses the CREATE SCHEMA statement to create a new schema named EduCBASCM. It also creates a table called ‘Transactions’ and a view named Transactions_list that belongs to the EduCBASCM schema:

Code:

CREATE SCHEMA EduCBASCM CREATE TABLE Transactions( transaction_id SERIAL NOT NULL, transaction_date DATE NOT NULL ) CREATE VIEW Transactions_list AS SELECT transaction_id, transaction_date FROM Transactions WHERE transaction_date <= CURRENT_DATE;

Output: As a result of the above statement, we will get the following statistics:

How to Drop PostgreSQL Schema?

The DROP SCHEMA is used to remove a schema from the current database and remove all of its objects.

Syntax:

Explanation:

Define the schema name after DROP SCHEMA, which we want to delete

Specify the IF EXISTS keywords which are optional to remove a schema only if the specified schema exists in the current database.

Use CASCADE to remove a schema, and all of its objects and all other objects are also deleted, which depends on those objects.

If you want to remove an empty schema, add the RESTRICT keyword.

Note: You need to be a superuser or the schema owner to execute the DROP SCHEMA statement.

We can use one line DROP SCHEMA statement for deleting multiple schemas as follows:

DROP SCHEMA [ IF EXISTS ] schema_name_1 [,schema_name_2,...] [CASCADE or RESTRICT]; Example:

We will use some of the CREATE SCHEMA section’s schemas to understand the examples for dropping a schema.

Remove an Empty Schema: The following statement is used to remove the EduCBASchema schema:

Code:

DROP SCHEMA IF EXISTS EduCBASchema;

Drop Multiple Schemas: The following statement is used to remove the multiple schemas Books and Notes using a single statement:

Code:

DROP SCHEMA IF EXISTS Books, Notes;

To remove a non-empty schema: The following statement will drop an EduCBASCM schema.

Code:

DROP SCHEMA EduCBASCM;

The result of the above statement is as follows:

If the deleted schema is non-empty and you want to delete the same and its objects, you should use the CASCADE option as follows:

Code:

DROP SCHEMA EduCBASCM CASCADE; Advantages of using PostgreSQL Schema

There are various reasons why anyone should use schemas:

PostgreSQL schema allows you to use a database without interfering with other databases.

It organizes database objects like views, indexes, sequences, data types, operators, functions, and other relations into logical groups to make these objects more manageable.

Conclusion Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Schema” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

You're reading How To Create And Drop Postgresql Schema

Update the detailed information about How To Create And Drop Postgresql Schema on the Cersearch.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!