Delete Data in Table in PostgreSQL

Syntax of DELETE Statement in PostgreSQL

The syntax for DELETE Statement in PostgreSQL is as follows:

• It starts with the keyword DELETE, indicating that you want to remove data from a table. It helps to delete the data from the defined table.
• The FROM keyword follows, indicating the table from which you want to delete data.
• Replace table_name with the actual name of the table you're working with.

If you're aiming for precision in your data deletion, you can use a WHERE clause in your DELETE statement. This clause lets you specify conditions that must be met for rows to be removed, allowing you to precisely tailor which data gets deleted. After assembling the DELETE query in PostgreSQL, executing it initiates the removal of the designated rows. However, using the delete query in PostgreSQL is crucial, as the DELETE operation is irreversible and permanently erases data.

Examples

Let’s create a table employees to better understand the commands of the delete query in postgresql.

Creating the employees table

In this schema, the emp table has attributes such as emp_id (employee ID), f_name (first name), l_name (last name), and manager_id (for manager-employee relationship). The FOREIGN KEY constraint maintains data integrity by referencing the same table, and the ON DELETE CASCADE clause ensures that associated records are deleted when a manager is removed.

Now let’s add some employee's data into the table using the below command:

This insertion populates the emp table with diverse employee records, each tied to a specific manager through the manager_id attribute.

Output:

Now let’s just try some test cases on the above table.

Case 1: Now let’s suppose we want to delete the employee named Shyam.

Output:

Case 2: Now let’s suppose we want to delete the user named Ashutosh Sharma and employees under him.

Output:

After running these DELETEqueries in PostgreSQL, transformations within the database are set in motion. As these deletions are executed, the database undergoes changes aligned with the defined conditions, effectively showcasing the dynamics of the PostgreSQL DELETE statement in action.

Output:

Deleting a Single Row

Now let's delete a single row in the above created table.

In this example, we're using the DELETE query in PostgreSQL to remove a specific row from the emp table. The WHERE clause specifies the condition for deletion, in this case, the row with an emp_id of 4. You can replace the emp_id value with the one you want to delete.

Output:

Deleting Multiple Rows

Now let's delete a multiple row in the above created table.

In this example, we're using the DELETE query in PostgreSQL to remove multiple rows from the emp table. The WHERE clause specifies the condition for deletion, which in this case is all rows where the manager_id is 2. This will delete all employees who work under the manager with manager_id 2.

Output:

Deleting Using Joins

Now let’s delete using joins in the above created table.

In this example, we're using the DELETE query in PostgreSQL with a JOIN to remove specific employees and their corresponding managers from the emp table. The JOIN clause links the emp table with itself based on the manager_id and emp_id relationship. The WHERE clause specifies the condition for deletion, which in this case is all employees who work under the manager named John Doe.

Output: