PostgreSQL Data Definition Language (DDL)

The term DDL stands for Data Definition Language. The DDL is used for creating and modifying the structure of the objects/entities in the database using some commands and statements. These objects can include aliases, tables, sequences, tables, etc. These statements include CREATE, ALTER, DROP, and TRUNCATE.

This article will discuss the data definition commands along with suitable examples.

PostgreSQL Data Definition Language (DDL)

In PostgreSQL, the DDL commands allow us to define a database. In this write-up, we will discuss the following DDL commands:

- Postgres CREATE Statement

- Postgres ALTER Statement

- Postgres TRUNCATE Statement

- Postgres DROP Statement

Let’s get started with the CREATE Statement.

CREATE Statement in Postgres

CREATE statement is used to create/form entities like databases, schema, tables, triggers, indexes, etc. The basic syntax for creating a schema is as follows:

CREATE SCHEMA Schema_Name;

Let’s consider that we want to create a table. For this scenario, the syntax will look like this:

CREATE TABLE <tab_name>
   (col_name_1 datatype,
   col_name_2 datatype,
   .
   .
   col_name_n datatype
   );

Example: Creating a New Postgres Table

Now if we want to make a table named “students_info”, we will have to specify the name of the table, its columns, and their data types. Following would be the simple query written for this case.

CREATE TABLE Students_Info
   (
   StudentID int,
   StudentName varchar(255),
   Address varchar(255),
   City varchar(255)
   );
SELECT * FROM Student_Info;

This would simply create a table named Students_Info with columns StudentId, StudentName, Address, and City:

ALTER Statement in Postgres

The ALTER statement is used to modify, add, or delete some already existing constraints or columns from the database table. We can add a column, drop a column, or can also change the data type of a column using the ALTER statement. The following code illustrates the syntax of how an ALTER statement is used to add a column in a Postgres tablet:

ALTER TABLE Tab_name ADD col_name datatype;

Example 1: Adding a New Column to a Postgres Table

Now if we want to add a column for the ages of the students, we will write the following query;

ALTER TABLE students_info ADD studentAge int;

This has added another column in the table named “studentAge” with initially all the values as null. This is because we have not inserted any value in that column:

Example 2: Rename a Column of a Postgres Table

Another clause named “RENAME” can be used with the ALTER statement to change the name of the table. The syntax is given below:

ALTER TABLE   table_name1 RENAME to   new_table_name1;

For example, if we want to rename our table “students_info” to “students”, we will write the following query:

ALTER TABLE students_info RENAME to Students;

It would give the following output.

Now if we run the following query, it would return an error that “ERROR: relation "students_info" does not exist”.

SELECT * FROM students_info

It means that this table has been renamed to “students”. For assurance, let’s run the query for the table “students”.

SELECT * from Students;

Following is the output table:

This means that for sure this table is renamed from "students_info" to "Students".

TRUNCATE Statement in Postgres

The TRUNCATE statement basically deletes all the data/rows from the database table but it does not delete the table (structure). The basic syntax for the query is

TRUNCATE table table_name;

Let's suppose we want to delete all the data from the table, maybe because the data is outdated and is of no use, we will simply use the TRUNCATE statement. Following will be the syntax if we want to truncate the above-considered table:

TRUNCATE table students_info;

This will simply delete all the data from the table as shown below.

Now if we want to get the table, we will have to run the select query:

We can clearly see that all the data from the table has been truncated.

DROP Statement in Postgres

As in the case of truncate, the whole data in the table was deleted but the existence of the table was still there. But if we want to delete the entire table what should we do? For this specific purpose, we use the DROP statement. The general syntax for the DROP statement is:

DROP table students_info;

The output for the given query is:

Now if we want to get the resulting table after this query, we will have to run the select query:

The error proves that the “students_info” table has been successfully dropped.

Conclusion

The DDL(Data Definition Language) is used for creating and modifying the object structures in the database using some commands and statements. These statements include; CREATE, ALTER, DROP, and TRUNCATE. We have covered them with their basic syntax and practical examples.

PostgreSQL Data Definition Language (DDL)