Order ALTER TABLE Allows you to personalize the tables in PostgreSQL. This command is used with an action, for example to add or modify columns.
What is the postgreSql command ALTER TABLE ?
Order ALTER TABLE is used in PostgreSql to modify existing tables. It can be used to add or delete a column, or to modify it according to your needs. In addition, PostgreSql ALTER TABLE is used to define or delete restrictions on a table in the database management system (SGBD). To obtain the desired result, you must specify the order with an action.
Dedicated servers
Performance and innovation
Take advantage of your own server, with dedicated hardware, cloud integration, minute invoicing and Intel® Xeon® or AMD processor.
Syntax of ALTER TABLE
For a basic understanding, it is useful to take a look at the basic syntax of ALTER TABLE. It presents itself as follows:
ALTER TABLE nom_de_la_table action;postgreSql
The proper command is therefore followed by the name of the table to which you wish to make changes. Then you specify the postgreSql command ALTER TABLE with the desired action.
Advice
To create a new table, use the Create Table command in PostgreSql.
PostgreSql use examples ALTER TABLE
In the following paragraphs, we will illustrate the functioning of ALTER TABLE by practical examples. We are going to create an example of a table called « customers », which initially contains three columns and three lines. It presents itself as follows:
| Id | Name | City |
|---|---|---|
| 1 | Berland | Paris |
| 2 | Wirth | Marseille |
| 3 | Madiot | Lille |
We can now customize it in different ways with ALTER TABLE In PostgreSql.
Add a column with PostgreSql ADD COLUMN
To add an additional column, use ALTER TABLE In combination with PostgreSql ADD COLUMN. This action has two parameters: the name of the column and the data type of the column. The syntax is as follows:
ALTER TABLE nom_de_la_table ADD COLUMN nom_de_la_colonne type de données;postgreSql
If we want to add a column to our “customers” table for addresses, we use postgreSql ADD COLUMN As follows:
ALTER TABLE Clients ADD COLUMN Adresse VARCHAR(255);postgreSql
The new extended table therefore presents itself as follows:
| Id | Name | City | Address |
|---|---|---|---|
| 1 | Berland | Paris | Null |
| 2 | Wirth | Marseille | Null |
| 3 | Madiot | Lille | Null |
Delete columns with DROP COLUMN
In the same way, we can delete a column of an existing table. For this, we use postgreSql ALTER TABLE In combination with action DROP COLUMN. The only parameter of this action is the name of the column. Here is the syntax:
ALTER TABLE nom_de_la_table DROP COLUMN nom_de_la_colonne;postgreSql
To delete the « city » column, we use this code:
ALTER TABLE Clients DROP COLUMN Ville;postgreSql
The table therefore only includes three columns:
| Id | Name | Address |
|---|---|---|
| 1 | Berlin | Null |
| 2 | Wirth | Null |
| 3 | Madiot | Null |
Rename columns with RENAME COLUMN
We can also rename an existing column, which can be a good alternative to the removal or addition of columns. The syntax of RENAME COLUMN In ALTER TABLE is as follows:
ALTER TABLE nom_de_la_table RENAME COLUMN nom_de_la_colonne TO nouveau_nom;postgreSql
Here we transform the « Name » column into « customer name »:
ALTER TABLE Clients RENAME COLUMN Nom TO Nom_du_client;postgreSql
In our table, only the name of the column changes, as we wanted:
| Id | Client name | Address |
|---|---|---|
| 1 | Berland | Null |
| 2 | Wirth | Null |
| 3 | Madiot | Null |
Other actions in PostgreSql for ALTER TABLE
Other useful actions with ALTER TABLE Include modification of the data type, the management of constraints and the addition of default values. Here are the main orders:
To modify the data type of a column:
ALTER TABLE nom_de_la_table ALTER COLUMN nom_de_la_colonne SET DATA TYPE type_de_données;postgreSql
To specify that a column must receive a value ::
ALTER TABLE nom_de_la_table ALTER COLUMN nom_de_la_colonne SET NOT NULL;postgreSql
To establish restrictions such as UNIQUE Or PRIMARY KEY For a column, use PostgreSql ALTER TABLE In combination with action ADD CONSTRAINT ::
ALTER TABLE nom_de_la_table ADD CONSTRAINT nom_de_la_contrainte UNIQUE (nom_de_la_colonne);postgreSql
