Schema - (PostgreSQL Schema)

In PostgreSQL, a schema is a logical container for database objects such as tables, indexes, functions, and views. A schema can be used to group related objects together and provide better organization and separation of concerns. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of schema in PostgreSQL.

Syntax

CREATE SCHEMA [schema_name] AUTHORIZATION [user_name];

• schema_name: The name of the schema to be created.
• user_name: The name of the user who will own the schema. This is the user who will be granted privileges on the schema.

Example

Let's create a new schema named sales:

CREATE SCHEMA sales AUTHORIZATION jdoe;

The output of this query would be:

CREATE SCHEMA

Explanation

In this example, we created a new schema named sales by using the CREATE SCHEMA statement. We also specified that jdoe is the owner of the schema.

Use

Schemas can be used to organize database objects into logical groups. For example, you could use a schema to group all of the tables and functions related to the sales department of your company.

Important Points

• Each database object belongs to a schema.
• The default schema in PostgreSQL is public.
• A schema can be explicitly specified when creating a new object by prefixing the object name with the schema name (e.g. sales.customers).
• Objects in one schema can access objects in another schema by specifying the schema name and object name (e.g. sales.customers and human_resources.employees).

Summary

In this tutorial, we discussed schema in PostgreSQL. We covered the syntax, example, output, explanation, use, and important points of schema in PostgreSQL. With this knowledge, you can now create logical groupings for database objects and keep your database organized and well-structured.