DML stands for Data Manipulation Language, which is basically used to do modifications to the database data. This modification includes adding, retrieving, and manipulating data. To do that, different commands/statements are used in PostgreSQL, such as INSERT, UPDATE, and DELETE.

Before moving on to the details of DML, let's find out what actually DML is.

PostgreSQL Data Manipulation Language (DML)

In PostgreSQL, the DML commands allow us to modify the database’s data. This article is based on the following DML commands:

- INSERT Command

- UPDATE Command

- DELETE Command

Let’s discuss them one by one.

INSERT Command in PostgreSQL

The insert command in PostgreSQL is used to add or insert rows/data into the table. The command used for inserting is INSERT INTO. Below is the basic syntax of insert statement:

INSERT INTO tab_name(col1, col2, …)
VALUES (val1, val2, …);

Now let’s suppose we want to make a “students_info” table and insert data in it. The following would be the query written for the particular example:

Create a Table First:

CREATE TABLE   Students_Info
   (
   StudentID int,
   StudentName varchar(255),
   Address varchar(255),
   City varchar(255)
   );

Insert Data Into it:

INSERT INTO students_info(studentid, studentname ,address,city)
VALUES ( 01,'John','13th Street. 47 W 13th St' , 'New York')
RETURNING *;

In the above syntax:

● Firstly, you have to write the name of the table(students_Info in this case) after the INSERT INTO statement, followed by a list of comma-separated column names.

● Secondly, add a comma-separated list of values, in the same order as you have specified the columns, with the VALUES statement.

This will add a row for all the data of student John provided in the values list.

For inserting multiple rows we will have to write the query as:

INSERT INTO students_info(studentid, studentname ,address,city)
VALUES ( 01,'John','13th Street. 47 W 13th St' , 'New York'),
( 02,'Alex','24th Street. 32 E 24th St' , 'San Diego'),
( 03,'Peter','6th Street. 23 W 6th St' , 'San Francisco')
RETURNING *;

The above code will create the following table in the database:

NOTE: A RETURNING clause is sometimes also used with the Insert command, but this is completely optional. “RETURNING *” is used to return the whole inserted row.

UPDATE Command in PostgreSQL

The UPDATE command in PostgreSQL modifies or updates the pre-existing data in a database. The command used for updating is UPDATE. To update the value of any pre-existing row, the following would be the required information:

1. The name of the table and the column that needs to be updated.
2. The updated value of the column.
3. And the condition where the update would take place.

Below is the basic syntax of the update statement:

UPDATE tab_name
SET col1 = updated_value,
col2 = updated_value,
WHERE cond;

Now considering the case if we have written the wrong city for the student John, in the same example we discussed above, we would want to change his city to Chicago. The following would be the query written for this case:

UPDATE students_info

SET city = 'Chicago'

WHERE studentid = 1

RETURNING *;

This will update the row of “City” from a pre-existing value to Chicago where the roll number of the student is 1.

Note that the WHERE clause is totally optional. If this is not present, then all the rows of the table will be updated.

DELETE Command in PostgreSQL

Till now we have learned to add and update values in the table but what if we want to delete data from a table? So here comes the use of the DELETE clause. We can delete row/rows from a table that are no longer of any use to us by this statement. The DELETE FROM keyword is used to delete the data. To delete a row we need to specify two things:

1. Specify the name of the table (after the keyword) from which that data needs to be deleted.
2. Specify the condition after the WHERE clause to show which rows from the table to delete.

DELETE FROM tab_name
WHERE cond;

In the same example discussed above, if the student Alex withdraws his admission from the school, we’ll want to remove his record from the students' table. In this case, the DELETE FROM query is used. The query structure for this particular example will be:

DELETE FROM students_info
WHERE studentid = 2
RETURNING *;

In the above example, the row in the students' table that has roll number 2 will be deleted. Remember that here the WHERE clause is completely optional and if it is not written, the statement will delete all the data/rows in the table.

Another important thing that is to be noticed here is that if we use the optional “RETURNING” clause with the DELETE statement it will always return the row/rows that is/are deleted. So following is the output for the above-mentioned query:

Conclusion

The Data Manipulation Language is very useful at times when we want to modify data in the database. These modifications may include the INSERT, the UPDATE, and the DELETE statements. INSERT statement inserts data into the table, UPDATE statement updates the old values in a table with the updated ones and DELETE statement deletes the data that is no longer of our use.