PostgreSQL Schema

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

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

The same object name can be used in different schemas without conflict; for example, schema1 and myschema can both contain a table named mytable.

Advantages of using schemas:

• Allows multiple users to use one database without interfering with each other.

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

• Third-party applications' objects can be placed in separate schemas so they do not conflict with the names of other objects.

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

Syntax

We can useCREATE SCHEMA statement to create a schema, the syntax is as follows:

CREATE SCHEMA myschema (
...
);

The above statement will create a schema named myschema.

Schemas are commonly used to organize and isolate database objects, preventing object name conflicts.

To create a table, use the CREATE TABLE statement:

CREATE TABLE myschema.mytable (
    column1 datatype1,
    column2 datatype2,
    ...
);

The above statement will create a table named mytable in the myschema schema, and define a series of columns and their data types.

Please note that datatype1, datatype2, etc. above should be replaced with actual data types, such as integer, varchar(255), etc.

Examples

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

exampledb=# create schema myschema;
CREATE SCHEMA

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

Next, we create another table:

exampledb=# 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 whether the table has been created:

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

Drop Schema

Drop an empty schema (all objects in it have already been dropped):

DROP SCHEMA myschema;

Drop a schema along with all objects contained in it:

DROP SCHEMA myschema CASCADE;

Other Extensions