PostgreSQL Schema

A PostgreSQL schema can be considered as a collection of tables.

A schema can contain views, indexes, data types, functions, and operators, among other things.

Identical object names can be used in different schemas without causing conflicts. For example, both schema1 and myschema can contain a table named mytable.

Advantages of using schemas:

• Allows multiple users to use a database without interfering with each other.
• Organizes database objects into logical groups for easier management.
• Objects from third-party applications can be placed in separate schemas, preventing name conflicts with other objects.

Syntax

We can create a schema using the CREATE SCHEMA statement, with the following syntax:

CREATE SCHEMA myschema.mytable (
...
);

Example

Next, we connect to tutorialprodb to create the schema myschema:

tutorialprodb=# create schema myschema;
CREATE SCHEMA

The output "CREATE SCHEMA" indicates that the schema was created successfully.

Next, we create a table:

tutorialprodb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The above command creates an empty table. We use the following SQL to check if the table was created:

tutorialprodb=# select * from myschema.company;
 id | name | age | address | salary 
----+------+-----+---------+--------
(0 rows)

Deleting a Schema

To delete an empty schema (all its objects have been deleted):

DROP SCHEMA myschema;

To delete a schema and all its contained objects:

DROP SCHEMA myschema CASCADE;