PostgreSql Create Table explained simply

Order CREATE TABLE Allows you to create a new table in a postgreSql database. By using it, you immediately define columns and their specifications, such as the data type and associated constraints.

What is the postgreSql command CREATE TABLE ?

Order CREATE TABLE is used in PostgreSql to create a new table in an existing database. Each table and its columns must have a unique name in the database. The columns receive a name and a type of data that they must contain. You can also define constraints on some or all columns from their creation.

Syntax and operation of CREATE TABLE

PostgreSql's basic syntax CREATE TABLE is as follows:

CREATE TABLE nom_de_la_table (
    colonne1 type_de_données PRIMARY KEY,
    colonne2 type_de_données,
    colonne3 type_de_données
);

So you start by using the main command CREATE TABLE To ask PostgreSql to Create a new table. You then name it with a unique name. The names of the columns and the authorized data types are indicated in parentheses.

If you want to include constraints, syntax is not the same:

CREATE TABLE nom_de_la_table (
    colonne1 type_de_données PRIMARY KEY,
    colonne2 type_de_données NOT NULL,
    colonne3 type_de_données UNIQUE
);

PostGresql supports the following types of restrictions, in addition to PRIMARY KEY ::

• NOT NULL : The column in question cannot contain any values.
• UNIQUE : Ensure that all the values of a column or a combination of columns are unique.
• CHECK : Can be used to define the conditions that must be met when you insert or update data.
• FOREIGN KEY : to define links with a column within another table.
• DEFAULT : defines a default value for a column if no explicit value is specified during insertion.

Practical example of PostgreSql CREATE TABLE

The operation of CREATE TABLE In PostgreSql will surely be clearer with a practical example. For this, we will create a new table called « customer list ». This must contain four columns: « ID », « Name », « Pays » and « Address ». We define « id » as PRIMARY KEY And define that the columns « ID » and « name » should not remain empty. Here is what the corresponding code looks like:

CREATE TABLE Liste_des_clients (
    ID SERIAL PRIMARY KEY NOT NULL,
    Nom VARCHAR(50) NOT NULL,
    Pays VARCHAR(50),
    Adresse VARCHAR(255)
CREATE TABLE liste_des_clients (
);

The database will now create an empty table with this name and the columns you have defined, which you can then fill with values. The output of the full table will look like this:

See the tables created with \d

To make sure that the PostgreSql command CREATE TABLE Successful, you can use the command \dt. It allows you to list all the tables in a database. Here's how to use it:

You can also use the command \d nom_de_la_table To obtain a detailed description of a specific table. To display the specifications of our example table, use the following command:

testdb=# \d liste_des_clients