PostgreSQL Schema (SCHEMA)

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

A schema can contain views, indexes, data types, functions, and operators, etc.

The same object name can be used in different schemas without conflict, such as schema1 Both myschema and mytable can contain a table named mytable.

Advantages of using a schema:

• Allow multiple users to use a database without interfering with each other.

• Organize database objects into logical groups to make them easier to manage.

• Objects of third-party applications can be placed in a separate schema so that they do not conflict with the names of other objects.

A schema is similar to a directory at the operating system level, but a schema cannot be nested.

Syntax

We can use CREATE SCHEMA The statement to create a schema, the syntax format is as follows:

CREATE SCHEMA myschema.mytable (
...
);

Online Example

Next, we connect to w3We use codeboxdb to create the schema myschema:

w3codeboxdb=# create schema myschema;
CREATE SCHEMA

The output result "CREATE SCHEMA" indicates that the schema has been created successfully.

Next, let's create another table:

w3codeboxdb=# 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 has been created:

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

Delete schema

Delete an empty schema (all objects have been deleted):

DROP SCHEMA myschema;

Delete a schema and all the objects it contains:

DROP SCHEMA myschema CASCADE;