Create and use a diagram in PostgreSql

In PostgreSQL, a diagram makes it possible to group several tables to improve the readability and organization of the database to create it, the command is used CREATE SCHEMA. Order DROP SCHEMA Allows you to delete a created diagram at any time.

What is a postgresql diagram?

In PostgreSql, a diagram is a Logical grouping of several tables. It can also contain indexes, sequences, types of data, views and functions. Thus, it can be compared to a repertoire, but unlike the latter, a diagram cannot contain other nested diagrams. This method is used in particular for facilitate the management of large databases containing many tables. In the structure of the database management system (SGBD), a PostgreSQL diagram is between the level of the database and that of individual tables. The entire hierarchical structure is presented as follows:

1. Instance (often called « server » in PostgreSql, which can contain several databases)
2. Database
3. Plan
4. Table
5. Line

For Create a new diagram In PostgreSql, the command is used CREATE SCHEMA. Order DROP SCHEMA Allows you to delete the diagram. You will find below a more detailed explanation of these two commands.

CREATE SCHEMA In PostgreSql: Basic syntax

The syntax of CREATE SCHEMA In PostgreSql is as follows:

CREATE SCHEMA nom_du_schéma;

This command creates a new diagram in PostgreSql with a unique name within the database.

Assign a postgreSql diagram to new tables

If you now create a new table with the postgreSql command CREATE TABLEyou can add it to an already existing postgresql diagram. The corresponding syntax looks like this:

CREATE TABLE nom_du_schéma.nom_de_la_table (
...
);

To illustrate this process, we show you here how to create a diagram called « customers ». Here is the code:

CREATE SCHEMA Clients;

Then you create a new table called « List_Clients_France » that you insert in the PostgreSql « customers » diagram. To do this, use this code:

CREATE TABLE Clients.Liste_Clients_France (
    ID SERIAL PRIMARY KEY,
    Nom VARCHAR(50) NOT NULL,
    Pays VARCHAR(50),
    Adresse VARCHAR(255)
);

You get a new empty table in the Postgresql diagram called « customers ». You can now fill it with values using PostgreSql INSERT INTO.

Delete a diagram with DROP SCHEMA

You can also Remove a postgreSql diagram. If the diagram is already empty and is no longer necessary, simply use the command DROP SCHEMA. For our example above, it looks like this:

DROP SCHEMA Clients;

If you want to delete a diagram and all the objects it contains in Postgresql, here is the appropriate command:

DROP SCHEMA Clients CASCADE;

This command must therefore be used with caution so as not to lose precious data.